Reference to cell address from value in other cell

[HorrLibre]

Hello,

I would like to address a cell by address from other cell (value). Example:

A1 have value “TEST”
A2 have value “OTHER”
A3 have value “A2”
A4 should have value “OTHER” (read from A2)
Changing A3 value to “A1” cell A4 should change to “TEST”

I would like to use it in =SUM() function as:

B1: =SUM(A1:A10) sums all values from row A from columns 1 to 10
but I would like to be able to change A1:A10 values not by editing function =SUM but by editing other cells:

B2: “A3”
B3: “A7”
B1: =SUM(VALUE_FROM_B2:VALUE_FROM_B3) so it would be =SUM(A3:A7) but it should be responsive to B2 and B3 changes.

Thanks!!!

[JohnSUN]

Excuse me, what do you mean as SUM of “TEST” (A1 and A3) and “OTHER” (A2) in formula =SUM(A1:A10)?

[HorrLibre]

It was second example, sorry for confusion. First wat about getting single value out of cell declared in other cell. Second one was about using =SUM on cells (with number values)

[JohnSUN]

(about first question) Try place to cell A4 formula

=INDIRECT(A3)

[HorrLibre]

Thank You very much, it did the job! I’ve looked at Spreadsheet manual and there was example: =SUM(INDIRECT(“a1:” & ADDRESS(1;3))). In conjuction with =INDIRECT and =CONCATENATE I’ve accomplished what I needed:

=SUM(INDIRECT(CONCATENATE(A1;":";A2)));

It is what i was looking for. Thanks.