Sheet Name in Formula

[Galto]

Hi,

I saw a similar thread to this earlier, but my situation is not quite the same...

I keep track of my students' marks on separate worksheets... one per student... and each sheet is named with the students' first names. I also have a summary sheet that gives me an "at-a-glance" view of the class' marks.

Right now, I grab the results of three items per sheet with a simple reference formula.

e.g. on the summary sheet I might have a column of formulas that look like this:
=Joe!$C$14
=Suzy!$C$14
=Bud!$C$14

... with their full names in a corresponding column to the left. I would like to add about 4 or 5 more columns (maybe more) to the sheet and also include some more complex formulas that involve references to those same sheet names.

I have one student's row completed and the formulas work just fine. But I would like to have a way to duplicate that row for about 300 other students without needing to manually adjust each formula to change the sheet name. Even though it would be slightly quicker, I could use the "Replace" command on each row of the summary sheet to replace "Joe" with "Suzy", etc... but that's still going to take a while.

Can I build a sheetname reference into a formula that would allow me to "fill" the columns by using the fill handle - or copy-paste?

I recognize that when I set this up once, I can use it with other classes and students and I could just change individual sheet names.

Happy to clarify the question if this isn't quite clear.

Thanks in advance!

Todd

 

[GlennUK]

Do some experimenting with the INDIRECT function. I use this to retrieve data from other sheets by having the sheet name in a cell, and building the reference as a string, and pass that to INDIRECT like this:

=INDIRECT(sheetref&"!$C$14")

 

[wprager]

I think you'll need to use some VBA code. You could write a single procedure to populate the summary sheet, or add a user-defined function to return a sheet name, and then use the Indirect function to grab values from other worksheets.

Here is some possible code to get you started:

Sub PopulateSummarySheet()
    Dim sh As Worksheet
    Dim row_offset As Integer
    
    row_offset = 0
    For Each sh In Sheets
        If sh.Name <> "Summary" Then
            Range("Summary!A1").offset(row_offset).Value = sh.Name
            Range("Summary!A1").offset(row_offset, 1).Value = sh.Range("A1")
            row_offset = row_offset + 1
        End If
    Next sh
    
End Sub

 

[Galto]

Do some experimenting with the INDIRECT function. I use this to retrieve data from other sheets by having the sheet name in a cell, and building the reference as a string, and pass that to INDIRECT like this:

=INDIRECT(sheetref&"!$C$14")

GlennUK.... That solved it! The other thread I had found had mentioned the INDIRECT function but I wasn't clear on how it worked. The student names on my summary sheet were actually in a "Last, First" format and my sheet names referred ONLY to their first names, but a quick formula that involved at SEARCH, LEN, and RIGHT fixed that hiccup.


wprager I should have qualified my question by mentioning I waned to avoid using VBA code. I appreciate your response all the same!

Cheers all!

Todd

 

[GlennUK]

Great! Glad I could help.

 

[Galto]

Once again, the Mr. Excel forum members have saved my bacon!

What an amazing resource this is! Unbelievable!

Todd