Results 1 to 8 of 8

Thread: Sheet Tab Names

  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Sheet Tab Names

    Is there anyway to refer to sheet tab names in a cell? I know you can refer to them with VBA code with something like Sheets(2).Name but I don't know how to put that in a cell. Say I have a large number of sheets in a workbook and I want to summarize in the first tab. I want to refer to data in the different sheets by the tab names. I know you can put something like =Smith!C27 but I want to replace 'Smith!' with a formula. Is there any way to do that? Ideally, it would be nice to even use the row names in the first sheet with something like =Sheets(Row #).Name.C27. Then I could copy that down for the 70 rows and be done with it as long as the data I want is in the same place in every sheet. I hope that question makes sense.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    JP,

    If all the sheets {tabs} are laid out the same you can reference them as 1stSheetName:LastsheetName!a5 thus to sum the amounts in a5 on all the sheets, except the one the formula is on (prevents circular reference) =sum(1stSheetName:LastSheetName!A5).
    If your sheet Names are, in order, Summary, North, South, East, West it would be =sum(North:West!A5).
    Post back if this is not what you are looking for.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    That is close to being what I need. My scenario is that I have several classes (I'm a teacher) that have taken a test. I have all the students listed on the separate sheets with the class identified by the name on the sheet tab. I would like the Summary to just show the average for each class. I don't want to sum them. I want to have each average listed by the class on separate rows. For instance in A1 it might say Class 1 and in B1 it might show their average of 72. A2 might say 'Class 2' and B2 '83.' I am an elementary school music teacher and I teach 40 classes. They are identified by the name of their classroom teacher. I hope that makes it a little clearer.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    JP,

    If you set up the grades on each sheet as a dynamic range name you can just say =Average(Class1Avg) on the summary sheet.
    Let's say the grades are in column C then in the name manager you would setup the name Class1Avg with the formula:
    =OFFSET('Class 1'!$C$2,0,0,COUNTA('Class 1'!$C$2:$C$1001),1)
    This formula will allow for up to 1000 students without having to be changed or any action on your part other than entering the student names and grades on the Class 1 sheet. Of course you have to setup a different name on each sheet, e.g. Class2Avg etc.

    So the formula in A2:=Average(Class1Avg)
    B2:=Average(Class2Avg)

    Since I assume from your post immediately above that the sheet names are those of the teachers I don't know a way to make the formula copyable. Of course, you'll replace Class 1 with the teacher's name.
    Last edited by RetiredGeek; 2012-12-01 at 22:09.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I already have the averages figured out on the individual sheets so I'll just have to put the label on my Summary sheet and then just point to where the average is. I was hoping there was a way to refer to the sheets by their index like Sheet(2). I think you can do that with a macro and could probably use a For loop and maybe I'll fool with that but it is probably more work to do that than just do it the regular way. Thanks for your help.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    JP,

    You're right it will be more work! At least the 1st time...but this seems like a more than one time thing so over the years the time invested in creating a macro will definitely pay off...take the long view. If you can post a sample workbook I'd be glad to help with some VBA.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    New Lounger
    Join Date
    Dec 2012
    Posts
    6
    Thanks
    0
    Thanked 1 Time in 1 Post

    Try the INDIRECT() function

    Will the INDIRECT function do what you need? Indirect uses a text string to create a cell reference

    If you have the average data for Class1 in cell B4 on the sheet with the tabname of "Class1", then on you summary sheet you can show that average using the following

    A2 contains "Class1"
    B2 contains the formula INDIRECT(A2&"!B4")

    Does this give you what you are after?

  8. #8
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Yes, that will help. That is a fomula that a person can copy down. Thanks

Posting Permissions

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