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

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?



--
Regards,

Peo Sjoblom


"John Broderick" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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,

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


"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:u$qp%(E-Mail Removed)...
> 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?
>
>
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "John Broderick" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> >
> >
>
>

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

What problem are you having?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"John Broderick" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
> "Peo Sjoblom" <(E-Mail Removed)> wrote in message
> news:u$qp%(E-Mail Removed)...
>> 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?
>>
>>
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> "John Broderick" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>> >
>> >
>>
>>
>
>

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


--
Regards,

Peo Sjoblom



"John Broderick" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
> "Peo Sjoblom" <(E-Mail Removed)> wrote in message
> news:u$qp%(E-Mail Removed)...
>> 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?
>>
>>
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> "John Broderick" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>> >
>> >
>>
>>
>
>

On May 18, 2:46 pm, "John Broderick" <n...@none.com> wrote:
> 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
>
> "Peo Sjoblom" <terr...@mvps.org> wrote in message
>
> news:u$qp%(E-Mail Removed)...
>
>
>
> > 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?
>
> > --
> > Regards,
>
> > Peo Sjoblom
>
> > "John Broderick" <n...@none.com> wrote in message
> >news:(E-Mail Removed)...
> > > 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- Hide quoted text -
>
> - 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