Page numbers

[Ian Mac]

All,

Is there a way of countins the number of sheets that have a certain name on a assigning a Page (Sheet) number to a particular cell on each.

i.e. I have 12 sheets (current) they are all called AgentA Mon, AgentB Mon, AgentC Mon etc. (the AgentA,b,c are real names of people). This is repeated for each day of the week.
What I'd like is for Excel to count the number of sheets and on AgentA put A1= "page 1" B1= "of 12".
the cells don't actually NEED the 'page' or 'of' in them.

I'd like this to be done viewing the sheets left to right in the workbook.

Also, if it's any help every day had the SAME people and ALWAY the same order.

Anyone??

[codger]

On 2002-04-04 01:55, Ian Mac wrote:
All,

Is there a way of countins the number of sheets that have a certain name on a assigning a Page (Sheet) number to a particular cell on each.

i.e. I have 12 sheets (current) they are all called AgentA Mon, AgentB Mon, AgentC Mon etc. (the AgentA,b,c are real names of people). This is repeated for each day of the week.
What I'd like is for Excel to count the number of sheets and on AgentA put A1= "page 1" B1= "of 12".
the cells don't actually NEED the 'page' or 'of' in them.

[..]

Bit fogged here: do you mean that if you have 5x3=15 tabs with all combinations of 'AgentA' thru 'C' and Mon thru FRi, putting 'Wed' in a cell would cause a formula to return 3 (for each of the 3 people)?

[Ian Mac]

On 2002-04-04 03:24, codger wrote:

Bit fogged here: do you mean that if you have 5x3=15 tabs with all combinations of 'AgentA' thru 'C' and Mon thru FRi, putting 'Wed' in a cell would cause a formula to return 3 (for each of the 3 people)?

I don't think a formula will do this. If we take Tues - Fri out of the equation and start with just Mon.

If I had Sheets Called:

{"A Mon";"F Mon";"G Mon";"Z Mon";"H Mon"}

AND a summary sheet called: Summary Mon

And they were laid out in the book LEFT to RIGHT where tabs are concerned:

{"Summary Mon";"A Mon";"F Mon";"G Mon";"Z Mon";"H Mon"}.

I'd like some code that would place:

{"Page",1,"of",6} on the Summary Sheet
{"Page",2,"of",6} on sheet "A Mon"
{"Page",3,"of",6} on sheet "F Mon" etc until
{"Page",6,"of",6} on sheet "G Mon"

BUT if I add a Sheet, say "D Mon" I'd like the Summary to read

{"Page",1,"of",7} automatically

Note: the sheets to be added will ALWAYS be between the Second and Last sheets so they will part of the formulas on the summary.

I hope this is a little clearer

Help would be greatfully received.

Many thanks

[codger]

OK, undeterred, I'm going to have a go at a formula based solution. I hope that,

[..]
I'd like some code that would place:

{"Page",1,"of",6} on the Summary Sheet
{"Page",2,"of",6} on sheet "A Mon"
{"Page",3,"of",6} on sheet "F Mon" etc until
{"Page",6,"of",6} on sheet "G Mon"

[..]

The last bit should be 'H Mon' - the order the tabs are in the workbook.

1) Create a name, say, Get.WorkBooks.1 that refers to =GET.WORKBOOK(1)
2) Create another name, say, Get.Sheets that refers to =REPLACE(Get.WorkBook.1,1,FIND("]",Get.WorkBook.1),"")
3) The total number of pages (tabs) is now =COLUMNS(Get.Sheets)
4) The page number (order of tab, left to right) is now =MATCH(REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),""),Get.Sheets,0)

Step (2) removed the [sheet-name] from the list returned by Get.WorkBook.1 in the same fashion as (4) does for the incident tab.

Caveats: CELL("filename") won't work on an unsaved workbook and, distressingly, you can't copy the formula about cell wise (you'll have to copy the formula from the formula bar). Sheet replication is OK though.

[Ian Mac]

[quote]
On 2002-04-04 04:40, codger wrote:
OK, undeterred, I'm going to have a go at a formula based solution. I hope that,
The last bit should be 'H Mon' - the order the tabs are in the workbook.
the Actual sheets are Persons Name_Day, so are in no order in terms of numerical or alpabetical.

1) Create a name, say, Get.WorkBooks.1 that refers to =GET.WORKBOOK(1)

Not sure how to achieve this, to test I placed =GET.WORKBOOK(1) in a cell and get an Invalid Function message. Is it an add-in function?

2) Create another name, say, Get.Sheets that refers to =REPLACE(Get.WorkBook.1,1,FIND("]",Get.WorkBook.1),"")

This will return the same as 1)

3) The total number of pages (tabs) is now =COLUMNS(Get.Sheets)

4) The page number (order of tab, left to right) is now =MATCH(REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),""),Get.Sheets,0)

Step (2) removed the [sheet-name] from the list returned by Get.WorkBook.1 in the same fashion as (4) does for the incident tab.

Caveats: CELL("filename") won't work on an unsaved workbook and, distressingly, you can't copy the formula about cell wise (you'll have to copy the formula from the formula bar). Sheet replication is OK though.

Unsure of what you mean by this?? By cellwise do you mean on different sheets, if yes you could put the option 2nd arguement in as a reference e.g. =CELL("filename",A1) to anchor it to that sheet.

I already use such a formula in the workbook:

=(VLOOKUP($B$5,StaffRota,VLOOKUP(RIGHT(CELL("filename",G3),3),{"Mon",2;"Tue",4;"Wed",6;"Thu",8;"Fri",10;"Sat",12;"Sun",14},2,0),0))

to direct the VLOOKUP to a particular Column on a schedule, determined by the day of the week in the sheet name.

I'm not sure a formula will cut the mustard, but I have been wrong many times before.

[codger]

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.

[Ian Mac]

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,

[Ian Mac]

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.

I've cracked it!!!

I eventually worked out the Page# part. It turned out not to be difficult in the end. I simply set up a table of calculations for Mon, Tue etc. and minused the results from whichever sheet I was on via VLOOKUP().

I do have a couple of questions though:

If you need to select n with a criteria then you just (!) need to amend the formula to select n.

1) I don't understand this from your earlier post, It'll be me being slow but what is (!)?

2) The solution is top banana, however if I add sheets to the workbook, how do I get the =COLUMNS(Get.Sheets) formula to recalculate the number of sheets automatically. I've tried F9 (this isn't what I need anyway) and all the formula re-calc EXEPT that one.
At first I thought it was because it referenced the XL4 thingy BUT....

=(B4-(MATCH(REPLACE(CELL("filename",TeamTotalsMon!A1),1,FIND("]",CELL("filename",TeamTotalsMon!A1)),""),Get.Sheets,0)-1))/7

....this one works no probs???

The only way I managed to get it to re-calc was to enter then leave the cell.

Any reason for this??

[codger]

Ian,

The re-calculation 'problem' is a feature of Excel. Unless functions are marked to recalculate without their arguments changing (so called 'volatile' functions) F9 and shift F9 perform re-calcs only on cells that can be traced to a changed cell (so called 'dirty' cells). What is happening in your sheet is that Excel can't trace any cell dependents for your formula and, after an initial calculation when the book is opened, won't perform a re-calculation.

Fortunately you can tell Excel to do a calculation of cells even if they are 'clean'; by ctrl+alt+F9. As I often work with custom functions, I've got into the habit of doing that.

One other calc thing; I noticed that on my trial sheet (could I post this to the board somehow?), using =CELL("filename") to get the tabs isn't suffcient. This returns the tab of the active cell and a recalc will cell all the formulas to this tab-name. What should be done is =CELL("filename",A1) where 'A1' is any relative reference and hence 'anchors' the cell to the tab name and so makes these all different.

I see by your latest post you've already done this - that was a good spot.

Thus, if your end goal was to be able to print the whole workbook, then after collating by hand, each recipient would have numbered copies. Was this what was wanted?

To do this last part, to get {"Ant Mon","Cat Mon","Ant Tue","Cat Tue"} to separate to Ant's 1 or 2, 2 of 2 etc. you need to stop retrieving '3' for the second Ant (Tue); it is the third sheet but only Ant's second. This is a bit messy. If you're not trying to do this I won't burden the post with the formula, but if you are just shout.

[ This Message was edited by: codger on 2002-04-05 04:18 ]

[Ian Mac]

On 2002-04-05 04:16, codger wrote:
Ian,

The re-calculation 'problem' is a feature of Excel. Unless functions are marked to recalculate without their arguments changing (so called 'volatile' functions) F9 and shift F9 perform re-calcs only on cells that can be traced to a changed cell (so called 'dirty' cells). What is happening in your sheet is that Excel can't trace any cell dependents for your formula and, after an initial calculation when the book is opened, won't perform a re-calculation.

Fortunately you can tell Excel to do a calculation of cells even if they are 'clean'; by ctrl+alt+F9. As I often work with custom functions, I've got into the habit of doing that.

One other calc thing; I noticed that on my trial sheet (could I post this to the board somehow?), using =CELL("filename") to get the tabs isn't suffcient. This returns the tab of the active cell and a recalc will cell all the formulas to this tab-name. What should be done is =CELL("filename",A1) where 'A1' is any relative reference and hence 'anchors' the cell to the tab name and so makes these all different.

I see by your latest post you've already done this - that was a good spot.

Thus, if your end goal was to be able to print the whole workbook, then after collating by hand, each recipient would have numbered copies. Was this what was wanted?

To do this last part, to get {"Ant Mon","Cat Mon","Ant Tue","Cat Tue"} to separate to Ant's 1 or 2, 2 of 2 etc. you need to stop retrieving '3' for the second Ant (Tue); it is the third sheet but only Ant's second. This is a bit messy. If you're not trying to do this I won't burden the post with the formula, but if you are just shout.

[ This Message was edited by: codger on 2002-04-05 04:18 ]

How then can I use Ctrl+Alt+F9 in VBA.

with the:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub

would suffice.

the e.g. {"Ant Mon","Cat Mon","Ant Tue","Cat Tue"} you gave.
I needed Ant to be page 1 on both occations

Re: that Page# part EUREKA! I did get it and as I've said, the solution was easier than first thought, simple maths one # minus another. (and they say we don't remember things from school )

Thanks again, hope you know the VBA bit, but not to worry if you don't you've saved a lot of future headaches.