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

Posted in Uncategorized

43 thoughts on “Page Of Pages in a Cell

  1. 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

  2. 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!!

  3. 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()

  4. 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!

  5. 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).

  6. 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.

  7. 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

  8. 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 :-)

  9. 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.

  10. 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?

  11. 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

  12. 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. :)

  13. 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

  14. 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.

  15. 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

  16. 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…

  17. 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

    Function PageNumber(c As Range) As Long
      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

  18. 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 volatile–but 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.

    Function PageCount(cel As Range) As Long
    ‘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

  19. 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

  20. hi
    How r u
    I read Ur post.
    v.good but i cannot understand how to apply this on my excel work book i am a new learner of excel
    so kindly guide me by video tutorial or by step by step excel hope u will guide me better
    Ur quick response will be highly appreciated.
    Thanks in advance.

  21. Hi Jan Karel,
    Thank you for the formula, it actually works pretty nicely, but I’m facing an other issue and I was wondering if you could help me find a way to solve it.

    My issue is that I want to use this PageOfPages on the first page and duplicate it on other pages using the LINES TO REPEAT FROM THE TOP in the page format, but when I do, the PageOfPages always show the result for the first page on all following pages. For exemple, if I have 6 pages, they will all show Page 1 of 6. I understand that excel is repaeting the content of the first rows, but isn’t there a way to have those rows repeating but change the PageOfPages according to the page it is repeated on?

    Thanks in advance.

  22. I am trying to label page of pages, but it is actually sheet of sheets. For example, I have 4 worksheets in each file, I want the first one to read Page (sheet) 1 of 4, Page 2 of 4, etc… any help?

  23. You may find this is a bit slow in processing, but

    Klik Insert, Name, Define: Add a relevant name (PageNo) and the refers to is =get.document(64)

    In a cell in the column you want enter the formula

    =If(ISNA(MATCH(ROW(),PageNo)),1,MATCH(ROW(),PageNo)+1)

    Assuming that you have your page breaks in the file, then it should give you the page number that particular cell would relate.

    Rgs,

    Heri Yahya

Leave a Reply

Your email address will not be published. Required fields are marked *