Tabs

[Ampleford]

I would like to refer to a series of tabs in a workbook by grouping them into periods - eg Tabs 1,2,3,4 would be week numbers so Period 1 would relate to the sum of those. Rather than create 13 periodic worksheets, can I refer to a tab (or series of tabs) by using formulae / references on 1 worksheet. eg entering "1" in the Period reference box would sum these tabs, where entering "2" would sum tabs 5-8 etc. I find it impossible to work out the syntax. To refer to cells, or to refer to areas is easy, but to refer to tabs seems beyond me.

I know I'm not explaing this very well but I've been working on this for ages and my brain is fried......

[s-o-s]

Ampleford,

There is a great work around
Create a Tab Start1 (this can be blank)
Create a Tab Stop1 (Also Blank)
On your Tab Period1 enter in B3 the formula:

=SUM(Start1:Stop1!B3)

Copy this to all the cells that you want summing on your template. Then drag and drop the Tabs for Period 1 between the Start and Stop.

WORD of WARNING...Do not put PERIOD1 between Start1 & Stop1 you will create serious Circular References!!!

Just repeat above with Start2, 3...until you have them all

Ps This is great if you have Departmental budgets with say 20 Depts. in one division then your boss decides that Dept 666 is now going to Move from Sales to Marketing...The Change takes 2 seconds just drag the tab 666 between the new dummy place holders and away you go.

[Ian Mac]

On 2002-05-01 02:25, Ampleford wrote:
I would like to refer to a series of tabs in a workbook by grouping them into periods - eg Tabs 1,2,3,4 would be week numbers so Period 1 would relate to the sum of those. Rather than create 13 periodic worksheets, can I refer to a tab (or series of tabs) by using formulae / references on 1 worksheet. eg entering "1" in the Period reference box would sum these tabs, where entering "2" would sum tabs 5-8 etc. I find it impossible to work out the syntax. To refer to cells, or to refer to areas is easy, but to refer to tabs seems beyond me.

I know I'm not explaing this very well but I've been working on this for ages and my brain is fried......

I'm unsure of where you'd like the results to be.
Would they be on a summary sheet at the front/back of the book?
Are all of the 'Periods' going to be on the Front/Back sheet?

Or, is the result of the first 4 going to be on Sheet1 of the 1,2,3 & 4, then sheet5 hold the sum of 5,6,7 & 8?

I can provide an answer for both.

[Ampleford]

I could have another 13 tabs with sums of the relevenat tabs on them, but this is more about references to tabs than about the actual spreadsheet.

To refer to a tab from a spread I need to use the syntax:-
'Tab1'!A1

To refer to a number of tabs I need to use:-
'Tab1:Tab4'!A1

I want to be able to drop a number (in this case a period which relates to a set number of weeks) on a cell which will influence the number(s) in the tab references.

eg Period 1 = Weeks 1-4, Period 2 = Weeks 5-8 etc etc

If I enter Period 7 I want it to drop data from tabs 25-28 which would be referred to by:-

'Tab25:Tab28'!A1

So that if I change it to Period 8 the reference changes to:-

'Tab29:Tab32'!A1

I know I'm not making this clear but the workbook is 16MB and slow to open / close (because of a lot of conditional formatting) so I don't really want to add any more tabs than I need.

(Also I love when there's a problem I don't think I can solve - then whoosh - it all becomes clear - usually with the help of you guys...)

)

[Ampleford]

ps - Can I also just say to S-O-S that the reply he sent, although not what I'm loking for here, is a brilliant workaround for grouping spreads.


Cheers

[Ian Mac]

On 2002-05-01 04:19, Ampleford wrote:
I could have another 13 tabs with sums of the relevenat tabs on them, but this is more about references to tabs than about the actual spreadsheet.

To refer to a tab from a spread I need to use the syntax:-
'Tab1'!A1

To refer to a number of tabs I need to use:-
'Tab1:Tab4'!A1

I want to be able to drop a number (in this case a period which relates to a set number of weeks) on a cell which will influence the number(s) in the tab references.

eg Period 1 = Weeks 1-4, Period 2 = Weeks 5-8 etc etc

If I enter Period 7 I want it to drop data from tabs 25-28 which would be referred to by:-

'Tab25:Tab28'!A1

So that if I change it to Period 8 the reference changes to:-

'Tab29:Tab32'!A1

I know I'm not making this clear but the workbook is 16MB and slow to open / close (because of a lot of conditional formatting) so I don't really want to add any more tabs than I need.

(Also I love when there's a problem I don't think I can solve - then whoosh - it all becomes clear - usually with the help of you guys...)

)

Rather than explainig complicated thing, I've set up a Workbook with 2 possible answers. If you want I'll mail it to you

[Ampleford]

Cheers - My EMail is trevor.ampleford@somerfield.co.uk