PDA

View Full Version : Solved: Formula to return totals of printed pages



Sir Babydum GBE
12-24-2007, 04:43 AM
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
12-24-2007, 04:57 AM
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
12-26-2007, 04:44 AM
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
12-27-2007, 07:07 AM
How's this - it uses defined names and Excel 4 macro functions.

Sir Babydum GBE
01-16-2008, 06:57 AM
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
11-11-2008, 06:36 AM
Hi Rory,
Just came across this post. Can you point me to a source of Excel 4 macro functions?
Regards
Malcolm

Bob Phillips
11-11-2008, 06:56 AM
Malcolm,

Do a search for macrofun.hlp

GTO
11-11-2008, 07:18 AM
Hello Malcom:hi: ,

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
11-11-2008, 07:25 AM
Malcolm,
I think you can still get it from here (http://support.microsoft.com/kb/128185).

Bob Phillips
11-11-2008, 07:35 AM
Hello Malcom:hi: ,

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.