Use a cell to tell function the sheet name?

[vischo]

Is it possible to use a cell to tell a function the sheet name?

Such as ex. SUMIF Function (Sheet1!A1:A10,"John",Sheet2!B1:B10)

Instead use something like this....

X1 = "Sheet1"
X2 = "Sheet2"

SUMIF("X1"!A1:A10,"John","X2"!B1:B10)


Anyone have any ideas

 

[Bernie Deitrick]

vischo,

If X1 and X2 are cell references:

=SUMIF(INDIRECT(X1 & "!A1:A10"),"John",INDIRECT(X2 & "!B1:B10"))

HTH,
Bernie
MS Excel MVP

 

[Harlan Grove]

If X1 and X2 are cell references:

=SUMIF(INDIRECT(X1 & "!A1:A10"),"John",INDIRECT(X2 & "!B1:B10"))

....

Safer to use

=SUMIF(INDIRECT("'"&X1&"'!A1:A10"),"John",INDIRECT("'"&X2&"'!B1:B10"))

which guards against the possibility that the worksheet names in X1 and X2
might have embedded spaces. If these need to handle copy and paste
operations like relative references, then

=SUMIF(INDIRECT("'"&$X$1&"'!"&MID(CELL("Address",($A$1,A1:A10)),6,64)),
,"John",INDIRECT("'"&X2&"'!"&MID(CELL("Address",($A$1,B1:B10)),6,64))