Accessing sheets based on cell contents

M

Mike

I want to create a formula with variable sheet names based on the contents of
a cell.

A1 = Sheet1
A2 = Sheet2
B1 = yes
B2 = No

=IF(B1='yes','A1'!X1)+IF(B2='yes','A2'!X1)
Where A1 and A2 resolve for the contents of A1 and A2 resulting in:
=IF(B1='yes','Sheet1'!X1)+IF(B2='yes','Sheet2'!X1)

Any help would be greatly appreciated.
Mike
 
P

PCLIVE

Try something like this:

=IF(B1="yes",INDIRECT("'"&$A1&"'!X1")+IF(B2="yes",INDIRECT("'"&$A2&"'!X1")))
 
M

Max

Use INDIRECT to resolve the variable sheetnames* in A1:A2 ..

Place this in say, C1:
=IF(AND(B1="yes",B2="yes"),SUM(INDIRECT("'"&A1&"'!X1"),INDIRECT("'"&A2&"'!X1")),"")

The expression will return "nothing", ie:"", as the value_if_false (you
didn't specify this in your post). Note that we have to use double quotes for
text:"yes" (not 'yes').

*Ensure there's 100% consistency between the actual sheetnames and the names
entered within A1:A2 (check for possible typos, extra white spaces, etc if
you get #REF!)
 
M

Max

Clarification:
*Ensure there's 100% consistency between the actual sheetnames
and the names entered within A1:A2 ..
Click to expand...

Except for case, that is (ie it's not case sensitive)
 
Top