Page Number inside Cell?
 

That is, I'm not looking for the head/footer solution. Is there a way
to post page numbers in the worksheet instead of using the head/footer
command?


--
mae1778
------------------------------------------------------------------------
mae1778's Profile: http://www.excelforum.com/member.php...o&userid=25716
View this thread: http://www.excelforum.com/showthread...hreadid=395379

mae,

Putting page numbers into cells could be done with a macro. If there aren't
a lot of pages, you may find it acceptable to put them in manually with
View - Page break preview, where you can see the page breaks (dashed lines).
--
Earl Kiosterud
www.smokeylake.com

"mae1778" wrote in
message ...

That is, I'm not looking for the head/footer solution. Is there a way
to post page numbers in the worksheet instead of using the head/footer
command?


--
mae1778
------------------------------------------------------------------------
mae1778's Profile:
http://www.excelforum.com/member.php...o&userid=25716
View this thread: http://www.excelforum.com/showthread...hreadid=395379

Doing what you want to do is not going to be easy OR reliable and I don't think is really worth it - page numbers are calculated dynamically by the printer driver whenever the sheet is previewed or printed.

Indeed, the page numbers you actually get when PRINTING can even be different to what you see in the Preview (we have an HP Business Inkjet where the preview and Page Break Preview will state 'Page 1 of 1' but when printed spills out onto a Page 2).

Even if you did have a macro to work out the current pagination and put them in cells, if the printer driver changed these after it being sent to print, those cell 'page numbers' would not change to suit.

I should know, I've had at least half-a-dozen cracks at this working at different companies and as an answer to different briefs, and it has never come off, for both that reason and also that cells are best kept for the data itself rather than formatting items.

One think you might like to consider is creating a totally separate worksheet, entitled "Output", which could have a set area for data to show in - i.e. you have a 'header' in Row 1, a 'footer' (complete with page number, defined in a named range as PAGE_NUM) in a Row near the bottom of the page, and in between have a bunch of cells (let's assume 20 rows deep) which contain the following formula pasted down (to allow the relative reference of A1 to move with the cells):

=OFFSET(Sheet1!A1,PAGE_NUM*20,0)

What this would do is display the first 20 rows of data in your sheet when the PAGE_NUM cell contains the value '1'. You could then type '2' into the PAGE_NUM cell and the data from rows 21-40 would then appear instead.

You could even add a spinner control linked to the PAGE_NUM cell so that you could 'flick' through your 'pages' and print only the ones you wanted - then you'd have ultimate control.

(All this, of course, is assuming that your output sheet snugly fits onto 1 page itself.)

Regards,
BizMark

Biz,

I'm not surprised at your comments. Printing has always been an inexact
science. Minor size variations can put a row, line, whatever into the next
page. I've always hoped that as long as you're using the same print driver
when you do the page numbers, the pagination will be identical when it
actually prints. But your experience reveals that it ain't necessarily so.
Thanks.
--
Earl Kiosterud

"BizMark" wrote in message
...

Earl Kiosterud Wrote:
mae,

Putting page numbers into cells could be done with a macro. If there
aren't
a lot of pages, you may find it acceptable to put them in manually
with
View - Page break preview, where you can see the page breaks (dashed
lines).
--
Earl Kiosterud
www.smokeylake.com

"mae1778" wrote
in
message ...-

That is, I'm not looking for the head/footer solution. Is there a
way
to post page numbers in the worksheet instead of using the
head/footer
command?


--
mae1778

------------------------------------------------------------------------
mae1778's Profile:
http://www.excelforum.com/member.php...o&userid=25716
View this thread:
http://www.excelforum.com/showthread...hreadid=395379
-


Doing what you want to do is not going to be easy OR reliable and I
don't think is really worth it - page numbers are calculated
dynamically by the printer driver whenever the sheet is previewed or
printed.

Indeed, the page numbers you actually get when PRINTING can even be
different to what you see in the Preview (we have an HP Business Inkjet
where the preview and Page Break Preview will state 'Page 1 of 1' but
when printed spills out onto a Page 2).

Even if you did have a macro to work out the current pagination and put
them in cells, if the printer driver changed these after it being sent
to print, those cell 'page numbers' would not change to suit.

I should know, I've had at least half-a-dozen cracks at this working at
different companies and as an answer to different briefs, and it has
never come off, for both that reason and also that cells are best kept
for the data itself rather than formatting items.

One think you might like to consider is creating a totally separate
worksheet, entitled "Output", which could have a set area for data to
show in - i.e. you have a 'header' in Row 1, a 'footer' (complete with
page number, defined in a named range as PAGE_NUM) in a Row near the
bottom of the page, and in between have a bunch of cells (let's assume
20 rows deep) which contain the following formula pasted down (to allow
the relative reference of A1 to move with the cells):

=OFFSET(Sheet1!A1,PAGE_NUM*20,0)

What this would do is display the first 20 rows of data in your sheet
when the PAGE_NUM cell contains the value '1'. You could then type '2'
into the PAGE_NUM cell and the data from rows 21-40 would then appear
instead.

You could even add a spinner control linked to the PAGE_NUM cell so
that you could 'flick' through your 'pages' and print only the ones you
wanted - then you'd have ultimate control.

(All this, of course, is assuming that your output sheet snugly fits
onto 1 page itself.)

Regards,
BizMark


--
BizMark