Accessing sheets based on cell contents

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 24 Aug 2006 22:27 GMT

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

Reply to this Message

PCLIVE - 24 Aug 2006 23:01 GMT

Try something like this:

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

>I want to create a formula with variable sheet names based on the contents
>of
[quoted text clipped - 11 lines]
> Any help would be greatly appreciated.
> Mike

Reply to this Message

Max - 25 Aug 2006 00:53 GMT

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!)

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I want to create a formula with variable sheet names based on the contents of
> a cell.
[quoted text clipped - 10 lines]
> Any help would be greatly appreciated.
> Mike

Reply to this Message

Max - 25 Aug 2006 08:31 GMT

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)

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message