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
Frank Kabel:
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
22 December 2004, 7:57 amjkpieterse:
Yes, same warning. Will add it to the text.
22 December 2004, 8:15 amNina:
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!!
10 February 2005, 8:54 amjkpieterse:
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
10 February 2005, 12:41 pmFernando:
It doesn’t fucking work. Please, be more specific.
17 October 2005, 8:31 amMy Excel doesn’t recognize “=GET.DOCUMENT()” as a valid formula.
Juan Pablo González:
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()
17 October 2005, 8:56 amzhaoyan:
RowAfterpgbrk
=GET.DOCUMENT(64)
TotPageCount
=GET.DOCUMENT(50)
how to add function in excel
12 November 2005, 11:39 pmjkpieterse:
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
24 November 2005, 4:25 amkchatch:
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!
16 December 2005, 3:18 pmkchatch:
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).
16 December 2005, 3:38 pmjkpieterse:
If you’re using VBA anyhow, don’t use this ancient XL 4 macro commands, check out the HPagebreaks and VPagebreaks collections instead.
18 December 2005, 11:54 amAdam:
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.
6 December 2007, 8:33 pmjkpieterse:
Conotrl+alt+F9 should do it.
7 December 2007, 12:19 pmtatiana:
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
31 January 2008, 2:55 amKim:
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
1 April 2008, 3:15 amTabish:
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.
4 April 2008, 5:26 amjkpieterse:
See http://www.jkp-ads.com/Articles/ExcelNames08.htm
6 April 2008, 5:25 amBrad Yundt:
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
6 April 2008, 4:55 pm