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. Yes, same warning. Will add it to the text.

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

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

5. It doesn’t fucking work. Please, be more specific.
   My Excel doesn’t recognize “=GET.DOCUMENT()” as a valid formula.

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

7. RowAfterpgbrk
   =GET.DOCUMENT(64)

   TotPageCount
   =GET.DOCUMENT(50)

   how to add function in excel

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

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

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

11. If you’re using VBA anyhow, don’t use this ancient XL 4 macro commands, check out the HPagebreaks and VPagebreaks collections instead.

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

13. Conotrl+alt+F9 should do it.

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

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

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

17. See http://www.jkp-ads.com/Articles/ExcelNames08.htm

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

19. This is a nice solution to a problem I ran into doing our year end physical inventory. Thanks…!!!

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

21. Does it works in Excel 2007?

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

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

24. So, can I make a name which consist excel 4 macro functions in Excel 2007?

25. Yes you can.

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

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

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

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

30. No, it does not work in the repeating rows. Rows repeated are always on page 1 so will always show page 1 of xx

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

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

33. Hi Brad,

    Thanks for that. I’d be interested to see a similar function for total number of pages…

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

35. For Excel 2010, maybe you could use that new method to make working with page setup stuff faster:

    application.PrintCommunication = False

36. I tried to get.ducument() in excel 2007, but got error message: “this is not a valid function.” Any help?

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

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

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

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

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

42. Thanks, works like a charm!

43. Many thanx!

Posting code? Use <pre> tags for VBA and <code> tags for inline.