Accessing sheets based on cell contents
 

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

Accessing sheets based on cell contents
 

Try something like this:

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

"Mike" wrote in message
...
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

Accessing sheets based on cell contents
 

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!)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mike" wrote:
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

Accessing sheets based on cell contents
 

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

Except for case, that is (ie it's not case sensitive)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---