Tabs

Ampleford

Active Member
Joined
Mar 26, 2002
Messages
380
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......
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Upvote 0
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.
 
Upvote 0
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...)

:eek:)
 
Upvote 0
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

:)
 
Upvote 0
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...)

:eek:)

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

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top