Using a result as a cell reference

[John]

Hi All

I have used MATCH and ADDRESS to find the cell reference of a value to
look up

I now want to use the product of that formula as a cell reference in
another formula (so I can say something like SUM(A1roduct)

can anyone point me in the direction of the correct syntax as I cant
seem to get it working

Kind Regards

John

 

[Debra Dalgleish]

You can use the INDIRECT function. For example, if the address is in
cell D6:
=SUM(INDIRECT("A1:"&D6))

 

[Paul]

Hi All

I have used MATCH and ADDRESS to find the cell reference of a value to
look up

I now want to use the product of that formula as a cell reference in
another formula (so I can say something like SUM(A1roduct)

can anyone point me in the direction of the correct syntax as I cant
seem to get it working

Kind Regards

John

May I offer advice that you are quite free to ignore if you wish?
I used to try this sort of thing - calculating an actual cell address as
text and then using it in a formula with the INDIRECT function. However, I
later discovered that that was an involved and overly-complicated approach,
which often led to problems. There are usually much easier and better ways
of accomplishing the same result.

You haven't described your original goal (i.e. how you are using MATCH), so
I will give a simple example to illustrate what I mean, hoping that it will
relate to your situation. Suppose you had numbers in column A and letters in
column B, and that you wanted to sum the numbers from row 1 down as far as
the row with the letter "x" in column B. Rather than finding the actual cell
reference to go in a formula like your SUM(A1roduct), you could simply use
=SUM(OFFSET($A$1,0,0,MATCH("a",B1:B100),1))

The big advantage of avoiding INDIRECT is that references will adjust as
necessary, whereas text is fixed.