1

I have a spreadsheet with several worksheets. On each printed page of each worksheet I would like to have references to some fields that i define somewhere in the document.

More specifically, i would like to have a version number, date of approval, in the headers. And I would like to be able to specify those values in cells in the first worksheet.

I want do do it smarter because it's a chore, and error prone, to update it manually.

I'm trying to create something like the quick parts-functionality found in Word. But I can't figure out how to do it.

I have tried to reference named cells, but since the header and footer-field isn't cells, it doesn't seem possible. I've web searched for "create custom header and footer elements", and similar stuff, but found only references to how the the 'out of the box-elemntes" are used. Also, it seems that there is no Quick Parts concept in excel, as there is in Word (which is quite a shame, cause that stuff truly makes me feel like a superuser..

I haven't looked in to creating a macro that solves my problem since i'm not that much of a super-user, but would be willing to dip my toes in the that pool if that is the way to go.

So, any suggestions?

0

Google tells me that this has to be done with VBA:

http://excel.tips.net/T002522_Putting_Cell_Contents_in_Footers.html

0

With some help from Scott Harris answer I created a button on the first sheet called "Update Headers" and then used picked the cells that i wanted to create text from the named cells, and then looped through all of the worksheets and updated them as well.

Sub Update_header()    
    Dim WS As Worksheet
    Dim HeaderText As String
    HeaderText = Sheet1.Range("F5").Text & " " & Sheet1.Range("C2").Text & Chr(10) & _
                                       Sheet1.Range("E2").Text & " " & Sheet1.Range("F2").Text & Chr(10) & _
                                       Sheet1.Range("B4").Text & " " & Sheet1.Range("C4").Text
    For Each WS In Worksheets
       WS.PageSetup.RightHeader = HeaderText
    Next

End Sub

When i first started out i tried to update all of the sheets in the workbook when i left the worksheet where i hade updated the text i wanted in the header. But the performance was so poor, so i decided to go for a button and then initiate the update manually instead.

-2

From 'Page Layout' in the ribbon, select 'Print Titles', then select the 'Header/Footer' tab.

This gives the custom features that you want.

Screenshot of Header/Footer dialogue box

You can insert fields from the Document Properties here if you wish.

2
  • How is that different from using the regular header/footers? I can't make the changes propagate to all the worksheets. Also, I having difficulties inserting fields from the Document Properties, any hints on how to go about doing it?
    – Erik
    Oct 22 '14 at 14:14
  • This does not answer the OP's question
    – Daniel K
    Nov 13 '18 at 10:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.