Page Of Pages in a Cell
Some time ago I was asked whether it was possible to add a Page .. of .. pages in a cell on a worksheet, without use of VBA.
Interesting challenge of course.
But with some defined names this proved possible.
I will show just one situation, where the pages are all below each other on the worksheet.
These names need to be defined:
RowAfterpgbrk
=GET.DOCUMENT(64)
TotPageCount
=GET.DOCUMENT(50)
PageOfPages
=”Page “&IF(ISNA(MATCH(ROW(),RowAfterpgbrk,1)),1,MATCH(ROW(),RowAfterpgbrk,1)+1)&” of ” & totpagecount + 0*now()
ThisPage
=IF(ISNA(MATCH(ROW(),RowAfterpgbrk,1)),1,MATCH(ROW(),RowAfterpgbrk,1)+1)
So to get the Page 1 of 3 in a cell, simply use this formula:
=PageOfPages
and to get the current page:
=ThisPage
A warning is needed here:
Do not copy cells that contain any reference to these defined names in their formula to another worksheet, Excel versions prior to 2003 will crash. To copy a formula from such a cell, select the formula in the formula bar and then hit control-c. Now you can safely paste the formula on another sheet.
I have uploaded a demo file to my site
Final helpful hint: If you use defined names a lot, consider downloading the
Name manager (by Charles Williams, Matthew Henson and myself) from:
www.jkp-ads.com
or
www.oaltd.co.uk/mvp
or from:
www.decisionmodels.com/downloads.htm
Hi Jan Karel
very nice. I knew the number of pages trick but to use the same method to get the current page is something new
I’d guess the same warning as in your other post also applies to this one: Before Excel 2003 don’t copy cells which uses this feature?
Frank
Yes, same warning. Will add it to the text.
Im new to all this but have been looking all over the place for this solution. bit frustrating then that i cant understand it. What do you mean you define names… and what is Get.Document?… please treat me like a five-year-old and be really specfic. would be really happy if i could get an answer… like i said, have been looking all over for it… please!!
Define Names, As in Insert, Name, Define.
If you send me an email, I’ll get you a working example. Just do not copy the cells that contain the formulas to another worksheet or workbook. Use this address: jkpieterse@netscape.net
It doesn’t fucking work. Please, be more specific.
My Excel doesn’t recognize “=GET.DOCUMENT()” as a valid formula.
Fernando,
You have to use it in a defined name, not in a regular cell. And if you have a non english version of Excel, well, then you have to translate the formula as well. I think in Spanish the correct name is
=INDICAR.DOCUMENTO()
RowAfterpgbrk
=GET.DOCUMENT(64)
TotPageCount
=GET.DOCUMENT(50)
how to add function in excel
Fernando: Using bad language is not going to help you get closer to a solution.
Everyone: I edited the post to include a link to a sample file you can download from my site:
http://www.jkp-ads.com/downloads/pageofpages.zip
All very useful information. I would like to know the GET.DOCUMENT() code that returns the COLUMN after the page break (for example, J, M, whatever). Same thing as GET.DOCUMENT(64) only columns, not rows. Thanks!
Oops… GET.DOCUMENT(65) appears to do the trick. Although, how do I go to the next page? IT appears to return the column/row only for the first page in the worksheet. I’m using vba by the way… need to insert grouped pictures in the bottom right corner (and rotate the group if landscape).
If you’re using VBA anyhow, don’t use this ancient XL 4 macro commands, check out the HPagebreaks and VPagebreaks collections instead.
Is there a quick way to update make the ThisPage update? I am using it in every row in one column for more than 1200 rows.
Conotrl+alt+F9 should do it.
Hi, I tried your solution but I have no idea how to doit! i mean, i know nothing about computers and when i try in another document, it dont work:s
can you help me ?
thanks
tatiana
Loved this solution, thank you so much!!
I’ve used it to help me create a Table of Contents for a multiple-worksheet spreadsheet, probably not the cleanest way of creating a TOC, but it seems to work well enough for now
Is there any place where there is an overview of what all the parameters to GET.DOCUMENT(xx) mean?
I have added my own document properties, and I really do not want to do a trial and error of a zillion values before I find these!
Thanks.
See http://www.jkp-ads.com/Articles/ExcelNames08.htm
Tabish,
Among other places, you can get all the parameters for GET.DOCUMENT from the help file for XLM macro language at http://www.microsoft.com/downloads/details.aspx?FamilyID=C09BF7F7-D30E-4CE9-8930-5D03748CA5CD&displaylang=en
“Excel 2000 Help File: Running Excel 4.0 Macros”
Brad Yundt
This is a nice solution to a problem I ran into doing our year end physical inventory. Thanks…!!!
Very helpful but, how can I make the page numbers change if where I placed the formula is also set up a rows to be repeated at the top of each page?