Doug,

Your Indirect formula can include a cell reference that updates the string:

=INDIRECT("Sheet2!A" & ROW(A1))

will update, but you need to use a formula that returns TRUE or FALSE, along the lines of:

=INDIRECT("Sheet2!A" & ROW(A1))=A1


HTH,
Bernie
MS Excel MVP


"Doug Davey" wrote in message
...
I appreciate the help

INDIRECT does work great, but unfortunately it does not allow me to copy the
formula and have the reference change along with it, which is key to what I'm
trying to do. Thanks for the tip.

I've looked a little into the UDF, but I think I'll run into the same hiccup
as with INDIRECT. I'll keep looking into it.

Thanks again for the help

Doug Davey

"Pete_UK" wrote:

If you didn't have the equals sign within the quotes, you could use
INDIRECT.

Another way is to set up a User Defined Function (do a search for
EVAL, which is a commonly used name for the UDF), then you would be
able to do:

=EVAL(cell_ref)

where cell_ref contains an Excel-like formula stored as text.

Hope this helps.

Pete

On Jul 6, 2:26 pm, Doug Davey
wrote:
The "Sheet1!A1" is an output from a conditional formula (IF function). I
can't use the reference as the output without including the quotes - and I
have 32 lists of these references that don't reference.

Perhaps I can phrase my question a different way - is there a way to include
a cell reference on a different worksheet as an output from a conditional
formula?

Thanks for the help...



"Mike H" wrote:
remove the quotes and ensure the cell is formatted as general

"Doug Davey" wrote:

Is there a way to convert the text "=Sheet2!a1" into a format that will tell
Excel to use it as a reference to the worksheet?

This seems like a simple thing, but I can't figure it out...- Hide quoted text -

- Show quoted text -