The ampersand is a special character in Excel’s headers and footers. For instance, if you enter the date or the time, Excel puts an ampersand before the entry.
To show a literal ampersand in the header or footer, use two ampersands together.
This results in a header that looks like this:
Hi:
Would you tell me how to insert data links from another sheet’s cell in the header footer of an excel sheet. I want to concatenate the data with text in the header.
Lets say in sheet 1 cell A1 i have the word College and in sheet 2 cell B2 I have the word 2007
In Sheet 3 I want to input in the custom header area a formula that would combine both data pieces shown above, COLLEGE 2007.
Thanks
Bill
Hi Bill,
Not sure about doing this directly through the Header/Footer options in Excel, but if you use VBA, the following method will work (using your example above):
Sub ChangeHeader()
With ActiveSheet.PageSetup
.LeftHeader = “”
.CenterHeader = Range(“Sheet1!A1?) & ” ” & Range(“Sheet2!B2?)
.RightHeader = “”
.LeftFooter = “”
.CenterFooter = “”
.RightFooter = “”
End With
End Sub
Note that using this method will update the specified header at the time of running – it won’t auto-update if you change the values in the cells unless you re-run the code. I’ve left the references for the other headers & footers in place for your reference in case you want to add to other locations aside from center header.
(I use this quite a lot with reporting tools where the date range / report date / subject etc change based on the options chosen by user, where it’s integrated into the rest of the report code, and the finished report has appropriate footers etc for the selections made.)
HTH
MikeC
I am trying to take a Serial number from my header and have it automatically appear on the footer of the same page in a specified cell… trying being the key word here as i am having no success, i found this page online can you help me?
Great, this is just what I needed and I couldn’t find it anywhere on the Microsoft site. Thanks.
Thanks, that was the fix I needed – great blog!!!
Incredible that something so simple is not anywhere on the Microsoft site…thanks so much!
Couldn’t find this anywhere until Google sent me here. Thanks for the help!
Thank you! I’m in a new job & we couldn’t figure out why the ampersand wasn’t showing when we printed. Love that it is such an easy fix (once you know what to do)!