Cell reference in formulae from text in another cell

  • Thread starter Thread starter Gareth Bateamn
  • Start date Start date
G

Gareth Bateamn

Is there a way to use the written location/address of a
cell, e.g. 'A1' written in cell B1, in another formula
automatically without having to manually type it in?

I have used the '=ADDRESS' function to obtain the target
cell location from data I have. Say the target cell is
A1, how do I put this into another formula without having
to re-type it.

Here is a simplified example:

For example if I wanted to add the target cell to the
cells 'A2, A3, A4 and A5' what would I do?

At first I would attempt to take the value of the cell
through the '=CELL' function, meaning a formula like this:

=SUM(CELL("contents",B1):A5)

Note: In Cell B1 there are the characters 'A1'.

This formula doesn't work as the SUM function can not
process logical values or other functions etc. (as far as
I am aware) and returns the name error

Is there any function I can use to help or combination of
functions or if anyone knows a macro/code which can solve
the problem.

Thank you in advance for any help.

Gareth Bateman
e-mail - (e-mail address removed)
 
Gareth,

You want to use the INDIRECT function. E.g, if B1 contains 'A1', then

=INDIRECT(B1)

is the same as

=A1

Similarly,

=SUM(INDIRECT(B1&":A5")

is the same as

=SUM(A1:A5)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top