Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Formulas: In a header or footer

  1. #1
    Join Date
    16th July 2003
    Location
    Calgary, AB
    Posts
    5
    Is it possible to index a header to a cell in a spreadsheet.

    I would like to create a template whereby every tab has the same header indexed to a cell on the tab that contrains the company information.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,463
    Have a look at my example here

    http://ozgrid.com/forum/viewthread.php?tid=2952
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    About me.

  3. #3
    Join Date
    26th June 2003
    Location
    Maryland, USA
    Posts
    368
    Great tutorial on Header/Footer setup, Roy.

    Dennis - if the cell info you want to put in your Header/Footer may be changing, you might consider putting the code in the WorkbookBeforePrint event so its updated before you print.

    ....Ralph

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    20th May 2003
    Location
    Mesa, Az
    Posts
    1,110
    Another solution to look at for Another Dennis!

    http://ozgrid.com/forum/viewthread.php?tid=4391

    HTH

  5. #5
    Join Date
    12th May 2006
    Posts
    9

    Re: Formulas: In a header or footer

    Dear all,

    I have a excel sheet2, which has header.

    I want to copy cell B5 of sheet1 in sheet2 header.

    What I have done so far is written a code in VB to put cell B5 value in the header.

    The code goes as follows in sheet2:

    VB:
    Private Sub Worksheet_Activate()ActiveSheet.PageSetup.LeftHeader = Format(Worksheets("sheet1").Range("B5").Value)End Sub 
    
    

    Now this works fine in showing value of B5, but it does not take the format.

    ie:

    sheet1 , B5 has date with format: Friday, 12, May, 2006

    whereas when i put the above code to show the same value in header of sheet2 it shows me:

    12/05/2006

    Anybody, any idea how can i keep the same format as cell B5 of sheet1??

    Any help will be appreciated.

    Thank you

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th May 2006
    Posts
    9

    Re: Formulas: In a header or footer

    Dear all,

    I have a excel sheet2, which has header.

    I want to copy cell B5 of sheet1 in sheet2 header.

    What I have done so far is written a code in VB to put cell B5 value in the header.

    The code goes as follows in sheet2:

    VB:
    Private Sub Worksheet_Activate() 
         
        ActiveSheet.PageSetup.LeftHeader = Format(Worksheets("sheet1").Range("B5").Value) 
         
    End Sub 
    
    

    Now this works fine in showing value of B5, but it does not take the format.

    ie:

    sheet1 , B5 has date with format: Friday, 12, May, 2006

    whereas when i put the above code to show the same value in header of sheet2 it shows me:

    12/05/2006

    Anybody, any idea how can i keep the same format as cell B5 of sheet1??

    Any help will be appreciated.

    Thank you

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,313

    Re: Formulas: In a header or footer

    Hi,

    Try,
    VB:
    ActiveSheet.PageSetup.LeftHeader = Worksheets("sheet1").Range("B5").Text 
    
    

    Cheers
    Andy


  8. #8
    Join Date
    12th May 2006
    Posts
    9

    Re: Formulas: In a header or footer

    Fab,thanks andy it does work.

    Although it looses the format everytime date is changed in sheet1 B5

    I tried putting some code in to get bold and font size, unfortunately it gives me error message. I guess I am not putting the correct vb code. I want to have font size: 20, Font: Arial, and Bold.

    VB:
     
    Private Sub Worksheet_Activate() 
         
        ActiveSheet.PageSetup.LeftHeader = Worksheets("Start Page&B").Range("C3").Text 
         
    End Sub 
    
    
    Does anyone know how can i get formats right?

    Thank you in advance

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    12th May 2006
    Posts
    9

    Re: Formulas: In a header or footer

    This code seems to be not working either. I thought following code will give me Font: arial, Size: 18 and In bold. But it is not working!!

    VB:
     
    ActiveSheet.PageSetup.LeftHeader = Worksheets("sheet1&B&""Arial""&18").Range("B5").Text 
    
    
    It gives me error. any suggestions?

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,313

    Re: Formulas: In a header or footer

    VB:
    Private Sub Worksheet_Activate() 
         
        Dim strHeader As String 
         
        With Worksheets("Start Page&B").Range("C3") ' change to whatever the correct sheetname should be
            strHeader = "&""" & .Font.Name & """" ' fontname
            strHeader = strHeader & " &" & Format$(.Font.Size, "00") ' fontsize
            If .Font.Bold Then strHeader = strHeader & " &B" ' fontbold
            strHeader = strHeader & " " & .Text ' text from C3
        End With 
         
        ActiveSheet.PageSetup.LeftHeader = strHeader 
         
    End Sub 
    
    
    Last edited by Andy Pope; May 12th, 2006 at 23:44.

    Cheers
    Andy


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Logo in header and footer
    By melsmiff in forum EXCEL HELP
    Replies: 2
    Last Post: February 7th, 2006, 01:49
  2. Worksheet header & footer
    By gbscuba in forum EXCEL HELP
    Replies: 2
    Last Post: April 4th, 2005, 12:01
  3. [Solved] Header / Footer
    By cb50chevy in forum Excel and/or Word Help
    Replies: 2
    Last Post: June 27th, 2003, 03:20
  4. Problem using the character & in Header/Footer
    By MzTee1 in forum EXCEL HELP
    Replies: 1
    Last Post: May 2nd, 2003, 08:11

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno