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

Tom Hutchins wrote:

Use the INDIRECT function twice:

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

Hutch

"Todd S" wrote:

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


"Tom Hutchins" wrote:

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

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

Hope this helps,

Hutch

"Todd S" wrote:

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"),"")

--

Dave Peterson