Building formulas from elements in other cells.

[rj]

We have a need to build formulas based on entries in other cells. So far, we have tried using the concatenante function to build the formula, however in the resulting cell, it will only display the formula rather than the value as if the formula was typed directly into the cell...

A simplified example:

Cell I22 has the value of "20"

In cell A1 the formula is:
=CONCATENATE("=","I","22")

Which correctly displays "=I22" as the value. Is there any way to make it display the value of 20 in cell A1 or is this just not possible in Excel? Again this is just a simplied example...the real formula is pulling elements from other tabs and cells to build the overall formula such as:

=CONCATENATE(L16,"+",M2,"+",HLOOKUP(CONCATENATE(N1,"_year0month0"),lookup1!E2:R8,L2))

Again, just the result is displayed rather than a value. If it is possible, thanks in Adavance for your help!

RJ

 

[Yogi Anand]

Welcome to the Board rj:

Try ...

=INDIRECT("I"&"22") ... to get 20

Regards!

Yogi
This message was edited by Yogi Anand on 2002-08-23 14:30

 

[lenze]

If I22 contains 20 then

="I"&INDIRECT(I22)will return I20
You can use this is a formula such as

=SUM(A4:"I"&INDIRECT(I22)) which is the same as A4+I20

See help for ways to use the INDIRECT function

 

[rj]

Thanks for the quick replies! I didn't know about this particular function... We spent the last couple hours looking for something that would do this and couldn't find the correct function. Great board!