I'm using the "Rows to repeat at top" function in Excel's "Page Setup" dialog to ensure that a multi-row header block appears on every printed page of my worksheet. However, I'd like to be able to change certain bits of the header based on the content of the current page. I would simply like to display the value of one cell in the first row that is printed on the page.

If this is my header:

Section: xx

And the data looks like this (columns are Section and Name):

1 Foo
1 Bar
2 Baz

I want the "xx" in the header to be "1". If, further down on the next page, the value in the Section column is "3", I want that printed in the header of the next page.

I originally thought that using the "OFFSET" function might help, e.g.

="Section: "&OFFSET(A2, 1, 0)

But it only shows the offset from the original placement of the header, thus only working on page 1.

The end document is a PDF, so right now I'm able to go back in with the "TouchUp Text Tool" in Acrobat and add the values page by page. But it gets to be a tedious process with 70+ page reports. Anyone have any better ideas that don't require me mucking up the original Excel document with inserted headers every N lines?

This is Excel 2008 for Mac, if it makes a difference.


You cannot manage the number of page through "Rows to repeat at top". The only way to do what you are asking is to customize the page header in Printing > Page setup, as explained here: http://www.bettersolutions.com/excel/EZC149/QT524312221.htm

| improve this answer | |
  • Thanks. To be clear, though, I'm not looking to print the page number in my header, as I'm already doing that in a footer using the technique you mention. I'd like to be able to change the data contained in the "Rows to repeat at top" section based on the data on the current page. – Jake Krohn May 23 '10 at 14:24
  • If it was possible, the same cell should contain several values. Clearly this is not possible. – Toc May 23 '10 at 20:14

You might play with creating a name in the workbook that contains the Offset(A2,10) formula and then you could do this... create name FakeHeader="Section: "&OFFSET(Sheet1!A2,1,0)

Then if you use that name =FakeHeader where your pagbreaks occur you will get something close to what you are trying to do. Admittedly it means controlling the page structure quite a bit, but you can divide into sections this way as you wanted

| improve this answer | |

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.