Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Tabs

  1. #1
    Board Regular Ampleford's Avatar
    Join Date
    Mar 2002
    Location
    Scotland
    Posts
    369
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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......

  2. #2
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    "Have a good time......all the time"
    Ian Mac

  4. #4
    Board Regular Ampleford's Avatar
    Join Date
    Mar 2002
    Location
    Scotland
    Posts
    369
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...)

    )


  5. #5
    Board Regular Ampleford's Avatar
    Join Date
    Mar 2002
    Location
    Scotland
    Posts
    369
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    "Have a good time......all the time"
    Ian Mac

  7. #7
    Board Regular Ampleford's Avatar
    Join Date
    Mar 2002
    Location
    Scotland
    Posts
    369
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •