Sum with Sheet names as a variable instead of manually provided text

[The_Wraith]

Since I've been hacking away at this syntax for over an hour... I figured it was time to give up and ask people smarter than me. I want to take the following standard SUM syntax:

=SUM('Sheet3'!C5:C100)

And, instead of manually specifying "Sheet3" in the formula I want to include a variable of that sheet name. I have the Sheet names in a separate column. Assume the following:

B3 = Sheet3
B4 = Sheet4
B5 = Sheet5
B6 = Sheet6

What is the proper syntax for something like:

=SUM([B3]!C5:C100)
=SUM([B4]!C5:C100)
=SUM([B5]!C5:C100)
=SUM([B6]!C5:C100)

?

I've tried such things as INDIRECT and numerous attempts to get the values in "B#" into the SUM formula without success. Please advise. Thanks in advance.

 

[Marcelo Branco]

I don't understand what you are trying to do with the formula above. It doesn't make sense to me.

Anyway, the right parenthesis (closing INDIRECT) is misplaced - it should be at the end of the string as in my post above.
=SUM(INDIRECT("'"&B3&"'!C5:C100"))

M.

 

[The_Wraith]

I don't understand what you are trying to do with the formula above. It doesn't make sense to me.

Anyway, the right parenthesis (closing INDIRECT) is misplaced - it should be at the end of the string as in my post above.
=SUM(INDIRECT("'"&B3&"'!C5:C100"))

M.

I'll try to explain without getting too much in the weeds. My "Sheet1" is a summary worksheet that pulls information from "Sheet2", "Sheet3", all the way to "Sheet18", etc. All of the detailed sheets (2-18) are simple "maintenance log" sheets that contain (3) basic Columns:

Date
Count
Notes

Various entries might include something like:

SHEET2:
2017.10.01 - 50 - (no notes)
2017.10.02 - 250 - (no notes)
2017.10.03 - (no count) - Started using 'xyz' process
2017.10.04 - (no count) - Cleaned the 'xyz' thing
2017.10.05 - 10 - (no notes)

On my SHEET1 I have a column dedicated to totaling the counts on each respective SHEET2-18, and this formula reads the DATE column of the last recorded COUNT:

=LOOKUP(2,1/('Sheet2'!C5:'Sheet2'!C999<>""),'Sheet2'!B$5:'Sheet2'!B$999)

My COUNT values all exist between C5 and C999 on each sheet. The lookup determines if the respective COUNT value in each sheet is < or > "", and if it is, includes the DATE (which exists between B5 - B999 of each sheet) value on my SHEET1 summary sheet. So, with my example 'table' above the entry on 2017.10.05 has the last entry with a count, so the "2017.10.05" will be presented through my LOOKUP formula on my summary sheet.

I was trying to find a shortcut of instead of manually specifying each Sheet# name, being able to reference the sheet name via text from another established field.

 

[Marcelo Branco]

I have to leave now. Maybe someone else can help.

M.