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:
=”Page “&IF(ISNA(MATCH(ROW(),RowAfterpgbrk,1)),1,MATCH(ROW(),RowAfterpgbrk,1)+1)&” of ” & totpagecount + 0*now()
So to get the Page 1 of 3 in a cell, simply use this formula:
and to get the current page:
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: