Solved: Formula to return totals of printed pages

[Sir Babydum GBE]

Hi

On the attached sheet I'm trying to figure out a formula that will return the sum of the cells below, but only the cells that will appear on a given printed page.

Ideally, I'd like to be able to click 'print', and the entire document will be printed (with rows 1 - 3 appearing on each sheet with appropriate results).

Rows in the main table can be inserted - and so I can't have fixed print page references.

But I'd be happy to use a filtering system and print out a page at a time if I could have a formula that would return the page number.

Any suggestions please?

Cheers

Sir BD

[rory]

If you mean you want to use the 'Rows to repeat at top' option in Page Setup to have a total for each page when printed, then that won't work - you will just get the same information on each page. You would need to have a separate print routine that printed each page individually and calculated the relevant information, probably putting it in the header or footer as appropriate. (if Access is an option, it would be a piece of cake in a report)

[Sir Babydum GBE]

If you mean you want to use the 'Rows to repeat at top' option in Page Setup to have a total for each page when printed, then that won't work - you will just get the same information on each page. You would need to have a separate print routine that printed each page individually and calculated the relevant information, probably putting it in the header or footer as appropriate. (if Access is an option, it would be a piece of cake in a report)

OK thanks Rory

What about a UDF to return a page number in each cell in, say, column A (1 - 300)? Is there such a thing? Cos then I can use Subtotal and print one filtered page at a time.

Thanks

[rory]

How's this - it uses defined names and Excel 4 macro functions.

[Sir Babydum GBE]

Rory,

My apologies for not getting back sooner. This is great, it'll do just fine.

Thanks for your time on this

Sir BD

[mdmackillop]

Hi Rory,
Just came across this post. Can you point me to a source of Excel 4 macro functions?
Regards
Malcolm

[Bob Phillips]

Malcolm,

Do a search for macrofun.hlp

[GTO]

Hello Malcom ,

Should be quit by now, but saw your request and in case you don't already have, thought you might like this (help file (.chm)) that I found somewhere in Microsoft's KB or MSDN or ???

Anyways, hope useful,

Mark

[rory]

Malcolm,
I think you can still get it from here.

[Bob Phillips]

Hello Malcom ,

Should be quit by now, but saw your request and in case you don't already have, thought you might like this (help file (.chm)) that I found somewhere in Microsoft's KB or MSDN or ???

Anyways, hope useful,

Mark

I haven't seen that version before, but it is good, better layout than the version that I have.