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.

 

[JoeMo]

Try:
=SUM(INDIRECT(B3&"!C5:C100"))

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try
=SUM(INDIRECT("'"&B3&"'!C5:C100"))

M.

 

[Fluff]

Hi & welcome to the board.
Have a look at this thread & see if it helps
https://www.mrexcel.com/forum/excel-questions/54976-indirect-sum-sheet-names.html

 

[The_Wraith]

Try:
=SUM(INDIRECT(B3&"!C5:C100"))

This errored out with:
'Invalid Cell Reference error'

Try:
=SUM(INDIRECT("'"&B3&"'!C5:C100"))

This appears to have worked perfectly. Thanks! It's kind of hard to discern that syntax - is that a (double quote) (single quote) (double quote) &B3 (double quote) (single quote) !C5:C100 (double quote) ?

 

[JoeMo]

This errored out with:
'Invalid Cell Reference error'

What's in cell B3 when it errors?

 

[The_Wraith]

In terms of THIS manual Sheet formula:
=LOOKUP(2,1/('Sheet3'!C5:'Sheet3'!C999<>""),'Sheet3'!B$5:'Sheet3'!B$999)

How would I modify IT in the same manner you did with SUM?

 

[The_Wraith]

B3 contains the text 'Sheet3' (without single quotes, of course, just the straight text).

 

[Marcelo Branco]

This appears to have worked perfectly. Thanks! It's kind of hard to discern that syntax - is that a (double quote) (single quote) (double quote) &B3 (double quote) (single quote) !C5:C100 (double quote) ?

"'" --> an apostrophe (red) between the quotes - to handle sheet names that contain space(s)
& --> to concatenate
B3 --> cell that contains the sheet name
& --> to concatenate
"'!C5:C100" --> apostrophe; exclamation mark ! ; range

M.

 

[The_Wraith]

"'" --> an apostrophe (red) between the quotes - to handle sheet names that contain space(s)
& --> to concatenate
B3 --> cell that contains the sheet name
& --> to concatenate
"'!C5:C100" --> apostrophe; exclamation mark ! ; range

M.

So, what am I mistaking here?

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

MODIFIED:
=LOOKUP(2,1/(INDIRECT("'"&B3&"'")!C5:'Sheet3'!C999<>""),'Sheet3'!B$5:'Sheet3'!B$999)

NOTE: I only played with the syntax on the first "Sheet3". As, I assume the syntax would be the same for all other instances. Wouldn't that INDIRECT with with the quotes, concatenation, and quotes generate "Sheet3"?