On 2002-04-04 06:12, codger wrote:
To create a name, use ctrl+F3 (define name) or select Insert from the menu, then name, then define.
In the dialog text entry enter your name 'Get.WorkBook.1', in the 'refers to:' entry at the bottom, enter (copy/paste) the formula I gave including the '=' char. It should be a formula; if it gets put in quotes, it's gone wrong.
You won't be able to dry run these 'name-formulas' in a worksheet. They belong to a hinterland of XL4 formulas that are information (as opposed to command or action based) and are OK in names but not in cells. It's this that stops you copying the cell contents.
As you didn't qualify my first remark, I presume that you aren't trying to sort these. The described solution will exhibit the 'page n of m' where m is the total and n is the tab number, left to right. If you need to select n with a criteria then you just (!) need to amend the formula to select n.
Now I see, OK I understand the GET.WORKBOOKS(1) thingy.
I don't see how:
4) The page number (order of tab, left to right) is now =MATCH(REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),""),Get.Sheets,0)
Would work on each sheet, the page number would be the same for each sheet (i.e. the last sheet that the formula is in)
changing to:
=MATCH(REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),""),Get.Sheets,0)
would stop this happening.
BUT! I will get there,
I'll explain:
My actual book currently has 61 pages in:
5 sheets of feeding data
7 sets of 8 sheets Monday-Sunday (currently).
Each of the Monday-Sunday has a summary sheet title TeamTotals&DayOfWeek.
Using your workings:
On 1 of the data sheets put:
=COLUMNS(Get.Sheets) in one of the cells, the result being 61
then put:
=(B4-(MATCH(REPLACE(CELL("filename",TeamTotalsMon!A1),1,FIND("]",CELL("filename",TeamTotalsMon!A1)),""),Get.Sheets,0)-1))/7
which is:
(No. sheets - (Sheet No of first summary - 1))/7
I have used 'TeamTotalsMon!A1' as the second arg in the CELL() to anchor it to that sheet (explained earlier)
the result being 8, thus far I have solved the Of how many sheets part and use =OfPages in those cells.
What I'm now looking is a way of the Page# being returned for each sheet. I'm playing with VLOOKUP to determin what to +/- as to which sheet number it actually is in relation to the number returned/7, if you follow.
Anyway I'm having a play with it now and should b able to work it out (head's fryed today).
Thanks for the help you've given, it's been extreely enjoyable (and eductional) playing with this and will continue to be.
By the way where can I get more info on XL4 thingies.
Many thanks,