Building formulas from elements in other cells.

rj

New Member
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

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Yogi Anand

MrExcel MVP
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

Legend
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

New Member
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!

Replies
10
Views
275
Replies
2
Views
285
Replies
1
Views
429
Replies
12
Views
305
Replies
18
Views
415

1,195,945
Messages
6,012,445
Members
441,701
Latest member
vnkendijs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back