Page Number inside Cell?

[mae1778]

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?

[Earl Kiosterud]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[BizMark]

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" [email protected] wrote
> in
> message news:[email protected]
>
> 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

[Earl Kiosterud]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" [email protected] wrote
>> in
>> message news:[email protected]
>>
>> 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