Formula-based Excel page headers
- by Jake Krohn
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 numbers 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.
