Generating a cell reference within a formula

[Todd S]

How do you generate a reference to a cell in another worksheet based on the
contents of a cell in the current worksheet.

Here is what I have been trying. Logically it works. But when true, it
returns the text that I am concatenating rather then the value of the cell
that I am referencing.

Cell A6 = January, 2008

=IF(CONCATENATE("'"&A6&"'!B2")<>0,CONCATENATE("'"&A6&"'!B2"),"")

 

[Tom Hutchins]

Use the INDIRECT function to make Excel treat the concatenated text as an
address:

=IF(CONCATENATE("'"&A6&"'!B2")<>0,INDIRECT(CONCATENATE("'"&A6&"'!B2")),"")

Hope this helps,

Hutch

 

[Todd S]

Thanks Tom, that worked well! However, now when the result is false, it
returns "0.00" rather than "". Suggestions?

 

[Tom Hutchins]

Use the INDIRECT function twice:

=IF(INDIRECT(CONCATENATE("'"&A6&"'!B2"))<>0,INDIRECT(CONCATENATE("'"&A6&"'!B2")),"")

Hutch

 

[Todd S]

Thanks Tom. Works perfectly!

Use the INDIRECT function twice:

=IF(INDIRECT(CONCATENATE("'"&A6&"'!B2"))<>0,INDIRECT(CONCATENATE("'"&A6&"'!B2")),"")

Hutch

 

[Dave Peterson]

The =concatenate() function serves the same purpose as the & operator. But you
don't need both:

=IF(INDIRECT("'"&A6&"'!B2")<>0,INDIRECT("'"&A6&"'!B2"),"")
or
=IF(INDIRECT(CONCATENATE("'",A6,"'!B2"))<>0,
INDIRECT(CONCATENATE("'",A6,"'!B2")),"")

(two lines to avoid line wrap in the post.)