Access a cell's Formula, rather than Value

[John Broderick]

Hello all:

I would like to modify this formula so that I use the formula of the cell
specified by x,
rather than the value stored in the cell.

=CHOOSE(x, Sheet1!A1, Sheet2!A1)


Thanks,
John

 

[Peo Sjoblom]

Not possible, you would need VBA for this. I can't see how by using CHOOSE
you would be able to do this even if it was possible to get the formula to
replace x since x should be an index number either 1 or 2 (in this case) if
you have 2 values? Or have I totally misunderstood?

 

[John Broderick]

Peo,

thanks for replying. Let me clarify:

x would be 1 or 2 based on the contents of another cell.
For example in Sheet3!A1 I have the formula:
=CHOOSE(A10, Sheet1!A1, Sheet2!A1)

If A10=1, I want the cell cotaining this formula to use the formula that
Sheet1!A1 has.
If A10=2, I want the cell cotaining this formula to use the formula that
Sheet2!A1 has.

Regards,
John

 

[Ragdyer]

It should work *exactly* like that, just as you have now!

What problem are you having?

 

[Peo Sjoblom]

Ahh! I see, unfortunately you would still need something that would
evaluate the cell to return, however you can hard code formulas in the
CHOOSE formula like

=CHOOSE(A10, SUM(Sheet1!D15), Sheet2!A1)

as an example so basically you could use the formula that is in Sheet1!A1
and so on. If not you would need a UDF written in VBA

 

[gumby]

Peo,

thanks for replying. Let me clarify:

x would be 1 or 2 based on the contents of another cell.
For example in Sheet3!A1 I have the formula:
=CHOOSE(A10, Sheet1!A1, Sheet2!A1)

If A10=1, I want the cell cotaining this formula to use the formula that
Sheet1!A1 has.
If A10=2, I want the cell cotaining this formula to use the formula that
Sheet2!A1 has.

Regards,
John






- Show quoted text -

Look at the INDEX & INDIRECT functions, you may have to chance that
way. The Indirect function uses the sheet names.

=INDIRECT(SheetName &"!e2:e57")



Dave