Can you use a formula in headers?

[chillyok]

I have an workbook with about 20 different sheets. Each sheet has a header that includes a statement date, that will not change as the actual date changes - March statements will always be March. I copy these worksheets monthly and have to manually edit all the headers for the new date.

I thought I could put the statement date "March 2011" in a hidden cell on one worksheet, then use a formula in each of the headers to pull whatever is in that cell, but I only get the actual text of the formula (='Book 1!$!$!'). Any suggestions?

 

[shg]

The formula is not correct, but it sounds like the header rows are formatted as Text. Format them in the date format you prefer.

 

[chillyok]

Yes, I realized as soon as I clicked enter, the formula was not correct. It would be a cell reference such as $A$1, in the first sheet.

But I'm actually typing "March 2011". If I type "March 2011" in cell A1, then use a formula in my header =A1, it prints out as "=A1", not March 2011. It's like it doesn't recognize it as a formula in the header. I tried using an actual date, and still got the same results.

 

[shg]

The cells are formatted as Text, so you see the formula rather than its results. Change the format.

 

[chillyok]

I'm entering the formula in the header (in page setup), not in a cell in the worksheet. I'm not sure there a way to set a format in the headers/footers in page setup.

 

[shg]

Ohhh -- THAT header Sorry!

You can't enter a formula in the page header, as you've learned. You can enter the current date and time. You'd have to use VBA to copy a cell value.

 

[shg]

For example,

Sub x()
    ActiveSheet.PageSetup.LeftHeader = Range("A1").Text
End Sub

 

[chillyok]

Yea!!! This worked for me. Had to do a little research, because I've never used VBA before, but I got it. Thank you so much!

 

[shg]

You're welcome. Sorry for the misunderstanding.