Returning the cell reference below

  • Thread starter Thread starter rudekid
  • Start date Start date
R

rudekid

AHHHHHHHH!!!! This is really infuriating as I bet it's easy.

Have an address formula which returns a cell reference. Great. What
really need is the cell below that though, e.g. if it returns J2 I wan
J3.

Fiddled around with ROW, INDEX, nested ADDRESS statements...nothing.
If anyone could help they would help an increasingly balding man.

cheer
 
this might make things clearer:

=ADDRESS(2,MATCH("w. country",ImportData!$A$1:$M$1,0),4)

"w. country" is the header I'm trying to find in the arra
ImportData!$A$1:$M$1. If it returns, e.g. J2, how do I change th
syntax to make it actually return, J3?

The reason I need the cell reference is I need this to start a separat
VLOOKUP function.

thank
 
Hi
maybe this is to simple but why don't you use
=ADDRESS(3,MATCH("w. country",ImportData!$A$1:$M$1,0),4)
 
J'mexcuse parce que je suis un idiot!

What a moron: I'd forgotten the second part was the lookup and th
first was telling it where to locate the cell. Maybe I should take u
another occupation...?

Thank
 
ah, have a new problem here which I can get round but it will be painfu
for the processing time.

The ADDRESS formula has found the correct starting point (which I the
use in a VLOOKUP) but I need the row to increment each time so that
can reference the right cell each time, e.g.:

=ADDRESS(3,MATCH("w. country",ImportData!$A$1:$M$1,0),4)
=ADDRESS(4,MATCH("w. country",ImportData!$A$1:$M$1,0),4)
=ADDRESS(5,MATCH("w. country",ImportData!$A$1:$M$1,0),4)

I'm running some VBA code which I was hoping would be able to copy an
paste the formula down the spreadsheet depending on how many rows o
data there was. I'd therefore need a 1000 record spreadsheet to read:

=ADDRESS(1001,MATCH("w. country",ImportData!$A$1:$M$1,0),4)

Anyone know how to do this besides complicatedly splitting the functio
up using e.g. MID and then rebuilding it?

thank
 
Hi
i'm not so sure your approach is the right one but you may replace the
number 3-1001 with a row reference.
E.g. if your formula starts in row one use
=ADDRESS(ROW()+2,MATCH("w. country",ImportData!$A$1:$M$1,0),4)
and copy down
 
Back
Top