Create a formula by joining text and the value from a formula

[Jesper Audi]

I would like to create a formula by joining together a text string and the
value within a defined cell.

For example, if the value of 10 is in the cell a5
and I have typed the value 5 in the cell h5

rather than typing =a5
I might like to create a formula rather like this ="a"&h5 or
=value("a"&"H5") to return the value in the cell a5 ie 10

I can see a lot of uses in formula building eg when copying formulas across
columns, I might like to increase the cell address row number by 1 or a
pretdetermined number each time

 

[Gary''s Student]

=INDIRECT("A" & H5)

so if H5 contains 10, this formula will give the same results as:
=A10

Read about INDIRECT() in Excel Help. It's a really neat function.

 

[Jesper Audi]

Hi Gary

Thanks for superfast response. Strangely I was trying = indirect earlier
today, but couldn't see a way for it to accomplish what I wanted.

To explain a bit more, it isn't the value 10 that I necesssarily want to get
to, it is the formula a5.

So if in earlier example, I have Values in a5, a6, a7 etc
I want to be able to have the values 5, 6, 7 in columns H I J, row 5

I want to be able to copy my joined together formula

eg = "a"&"h5"
into, say cell H6, so that it reads a5

and that when I copy it into cell I6, and J6, I want it to read =a6 and =a7.

For example I might want the cell in a5 to multiply with the cell in a105
and the cell in a6 to multiply with a106 etc, and ideally I would like to be
copying acoss columns with the cell reference increasing by 1 for every
column.

Of course I might not want the cell reference to increase only by 1, so if I
wanted it to increase by 3 each time, eg =a5 or = a8 or = a11
then I could just put in the numbers 5, 8 and 11 into cells H5, I5 and J5


In this case, I couldn't get the = Indirect function to help me. When
increasing my cell reference only by 1 each time, I can copy my formulae down
and then transpose into my row, but this workaround doesn't always help and
is a bit longwinded.



Many thanks for your time.

Jesper

 

[Jesper Audi]

My apologies; I see this does in fact work

Thanks again.

Jesper