Dynamic value based on Row for Header & Footer Print (Similar with Bank Statement), Help!

[radian89]

Hi All,

I want to ask is there any way or how to set up header and footer in excel, to dynamically refer to a value from its row? some kind like bank statement. please refer to the image below.
the reason why i choose to use Header & Footer, because the content row inside is dynamic, depends on how long detail text, it will auto expand, and fits it self when print.

From the image,
1st page header content : will be refer to a form (not yet developed) that inputted by user
2nd page header content : will refer to the 1st one, as well as 3rd, 4th, 5th page.

can I still use the header & footer for this feature? If it'll be more difficult, is there any way to do it?

Forum.JPG

thanks a lot for the help
Warm regards,

Adrian

[Kevin#]

The easiest way to do this is to avoid headers and footers. The footer is the problem - unlike the top where you can repeat rows, the same feature is not available at the foot of a page. Lining everything up would be a nightmare.

One approach (via VBA) would be to dynamically build the sheet as follows:
- use narrow margins
- build the header and footer into each page as in your example
- make each page the same size (in your example 8 rows of data plus header plus footer)
- the last page would usually contain some blank rows
- the "balance in web" could be included for all 8 rows (but, to avoid it being visible, use conditional formatting to display font with no colour if rest of row blank) - this would allow the balance brought forward and carried forward to be formula driven from the same relative cell each time
- put in a visual check to ensure that Balance Carried Forward at bottom of statement agrees to balance in source data! Better still, prevent it printing if the balances differed