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

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

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

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 -

=INDIRECT(formula)

--
HTH

Bob

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

"Doug Davey" wrote in message
...
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...

Yes, try:-

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

Mike

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

Thanks - I'll read up on it and give it a shot....

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

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


"Doug Davey" <Doug wrote in message
...
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...

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 -

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!

"Bernie Deitrick" wrote:

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


"Doug Davey" <Doug wrote in message
...
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...

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 Davey" wrote in message
...
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!

"Bernie Deitrick" wrote:

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


"Doug Davey" <Doug wrote in message
...
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...

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 -