CODEWITHSUNDEEP
excelevalworksheet-function
b_dev
b_dev

Reputation: 2598

Representing an Excel cell address in a formula using the string values of other Excel cells?

Say I want the answer to this type of formula = SUM(startcell:endcell) but the startcell and endcell components change.

So instead of using C1 directly in a formula to represent the value in cell C1, I want to be able to use whatever the values are in and B1, B2, and B3 to construct something that can represent C1 in a formula?

Perhaps a better way to ask the question is, Does Excel have something like Python's eval() ?

alt text

Upvotes: 3

Views: 31897

Answers (4)

b_dev
b_dev

Reputation: 2598

alt text

Upvotes: -2

Phil Nixon
Phil Nixon

Reputation: 81

I think that more useful is the address formula which allows you to build a cell address string from the inputs you have specified. The notation is =ADDRESS(row_number, column_number,absolute_number_flag,bool_A1Style,sheet_text).

So for your example: - To return Sheet1!R1C3 you would use =ADDRESS(B3,B2,,FALSE,B1) - To return Sheet1!C1 you would use =ADDRESS(B3,B2,,TRUE,B1) or =ADDRESS(B3,B2,,,B1)

You can then use your INDIRECT function on this returned address.

Note if you wanted a dynamic range to be summed using the sum() function you could use two address calls to build the string for the whole range.

E.G. If you added the value of 5 in cells C3 and C4 of your example to specify row and column for your end cell you could have a formula of =ADDRESS(B4,B3,,,B2)&":"&ADDRESS(C4,C3) which would return 'Sheet1!'!$C$1:$E$5

I can't post images yet so hopefully this makes sense in text!

Upvotes: 2

Dr. belisarius
Dr. belisarius

Reputation: 61016

Explaining Excel formulas in text is tedious ... I think an image is better

alt text

HTH!

Upvotes: 8

Xtophe
Xtophe

Reputation: 2277

You can try

formula = SUM(INDIRECT(B1),INDIRECT(B2),INDIRECT(B3))

And make more tests to see if you can work on ranges

Upvotes: 0

Related Questions