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?
Does it works in Excel 2007?
Hi,
I’m curious here. Why would you use this method, or even VBA, to do that? Isn’t it much simple, robust and reliable to use the page setup?
But I do understand the “because we can” answer…
PAT
I don’t think I’ve used it, but I’d say if you’re not printing. If you just want to display something on the screen but still give users a sense of location, it might be useful. Also, if you want to put it somewhere on the printed page other than the header or footer. And, of course, because you can.
So, can I make a name which consist excel 4 macro functions in Excel 2007?
Yes you can.
Dear JKP
I do as you suggest.
I set a row repeat.
But =PageOfPages remains “Page 1 of xx”
Where I do wrong?
Where to edit?
Thank you
DWP
So although I too am like a 5 year old with these things I eventually figured it out! Many thanks – HOWEVER did the question of how to get the page number to work if its present in a cell in a rows which is repeated at the top of each page? On printing the cell contains page 1 of 14 on the top of each page.
Thanks.
Stuart and Ron never got a response to their question regarding formulas placed in repeating rows…. is there a way to update the formulas for each page within the designated print area? I played a bit with this and depending upon how ‘deep’ the print area was, the number of pages did change by not the ‘page’ (from 1 of 3 to 1 of 10), however – this appeared the same on each of the 10 pages.
Also – I noticed in your sample excel file that there were some additional ‘defined’ names that seemed to pertain to the print area that were left out of your initial response. Are these additional names crucial to the updating?
Some clarification would be immensely helpful.
Regards – Lenny33
I’ve never found a way to make it work with rows that repeat. See here http://www.dailydoseofexcel.com/archives/2010/01/26/vba-page-of-pages-in-a-cell/
As for the defined names, the only ones you really need are RowAfterPagebrk, TotPageCount, and PageOfPages. If you want to use any of the other names, you can add them.
No, it does not work in the repeating rows. Rows repeated are always on page 1 so will always show page 1 of xx
I scoured the net looking for VB solutions to these problems. I left your page open for days before trying it because I thought someone must have surely posted a simple VB or UDF that does this. Resorting to the abandoned macro functions didn’t seem right. However, this is the only thing that got the job done. Seriously thank you for keeping this page available.
I will continue trying to reach a VB solution for =ThisPage but until then…
Former Excel MVP Ture Magnusson posted a UDF to return the page number at: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_10213800.html#2094940
You use it with a worksheet formula like:
=PageNumber(A100) ‘returns page number of cell A100
Dim pb, h As Long, v As Long
For Each pb In c.Parent.HPageBreaks
If pb.Location.Row <= c.Row Then h = h + 1 Else Exit For
Next pb
For Each pb In c.Parent.VPageBreaks
If pb.Location.Column <= c.Column Then v = v + 1 Else Exit For
Next pb
Select Case c.Parent.PageSetup.Order
Case xlDownThenOver
PageNumber = ((c.Parent.HPageBreaks.Count + 1) * v) + h + 1
Case xlOverThenDown
PageNumber = ((c.Parent.VPageBreaks.Count + 1) * h) + v + 1
End Select
End Function
Brad
Hi Brad,
Thanks for that. I’d be interested to see a similar function for total number of pages…
In trying to modify Ture’s code to return the total number of pages for Jan Karel, I observed that the function below does not update automatically. The simple answer is to make it volatilebut there is a noticeable delay while it recalculates. This delay would be intolerable if you were editing the workbook. For this reason, I suggest manually triggering the recalculation, such as by changing the value in the cell being passed as a parameter.
‘Returns the number of pages for the worksheet containing range cel
‘Note: function is not volatile, so it needs to be refreshed manually. _
It is not very fast, so it would be unwise to make function volatile.
Dim pb, h As Long, v As Long
Dim rg As Range, rgPrintArea As Range
With cel.Parent
If .PageSetup.PrintArea = “” Then
Set rgPrintArea = .UsedRange
Else
Set rgPrintArea = .Range(.PageSetup.PrintArea)
End If
Set rg = rgPrintArea.Cells(rgPrintArea.Rows.Count, rgPrintArea.Columns.Count)
For Each pb In .HPageBreaks
If pb.Location.Row <= rg.Row Then h = h + 1 Else Exit For
Next pb
For Each pb In .VPageBreaks
If pb.Location.Column <= rg.Column Then v = v + 1 Else Exit For
Next pb
Select Case .PageSetup.Order
Case xlDownThenOver
PageCount = ((.HPageBreaks.Count + 1) * v) + h + 1
Case xlOverThenDown
PageCount = ((.VPageBreaks.Count + 1) * h) + v + 1
End Select
End With
End Function
Brad
For Excel 2010, maybe you could use that new method to make working with page setup stuff faster:
application.PrintCommunication = False
I tried to get.ducument() in excel 2007, but got error message: “this is not a valid function.” Any help?
Hi Jan Karel ,
Many thanks for this amazing stuff. Just one note – the apostrophes were not actually apostrophes , and only on replacing them with actual apostrophes , the formula worked.
Tony : Please do the following :
Click on Name Manager ; click on New ; for the range name , either copy + paste or type in RowAfterpgbrk ; click in the Refers To box , and delete whatever is displayed there ; copy + paste or type in =GET.DOCUMENT(64)
Repeat this for the remaining three range names : TotPageCount , PageOfPages and ThisPage.
Within your worksheet define your print range ; go to each page break and wherever you want , on each page , type in the formula =PageOfPages ; you should see something like Page 1 of 17 ; Page 2 of 17 and so on , depending on whether you have put in the formula on the first page or the second or …. The total number of pages ( 17 in my example ) will again depend on the size of your defined print range.
Narayan