Converting a text form of "=Sheet2!A1" into a reference formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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...
 
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...
 
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
 
=INDIRECT(formula)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Yes, try:-

=IF(A1=1,Sheet2!A1,Sheet3!A1)

Mike

Doug Davey said:
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...
Click to expand...
 
Doug,

Give cell A1 of Sheet2 a name, for example, Name1

Then on the CF, use

Formula is

with the formula =Name1=CellAddress

like

=Name1=B3

Where B3 is the cell with the CF'ing.

HTH,
Bernie
MS Excel MVP
 
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
 
Are you meaning me to try using an IS function, and is "CF" short for
conditional formatting? This sounds like it may help, but I'm not exactly
sure where you're going with this. Could you break it down a little more for
me?

The one thing I need to be able to after I get Excel to recognize the text
as a formula is to be able to copy it and have the reference change with the
copy. Will what you are recommending allow me to do that?

Thanks for the assistance - I do appreciate it!
 
Doug,

It depends on how you want the formula to change.

Say that you want to compare cell A1 of Sheet1 to cell A1 of Sheet2, and so on.

If you name A1:A100 of Sheet2 TestName, and use the "formula is" CF option on cells A1:A100 of
Sheet1, with the formula

=INDEX(TestName,ROW(A1))=A1

Then you can Conditionally Format all 100 cells at once.

But, as I said, a lot depends on your workbook structure and what you are trying to achieve.

HTH,
Bernie
MS Excel MVP
 
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
 
Back
Top