Returning the cell reference below

[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

 

[Andy B]

Hi

Have a look at OFFSET()

Andy

 

[Frank Kabel]

Hi
try
=ADDRESS(ROW()+1,COLUMN())

 

[rvExcelTip]

RANGE(CellAddress).OFFSET(1,0) should address the cell below th
returned on

 

[rudekid]

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

 

[Frank Kabel]

Hi
maybe this is to simple but why don't you use
=ADDRESS(3,MATCH("w. country",ImportData!$A$1:$M$1,0),4)

 

[rudekid]

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

 

[Frank Kabel]

Hi
no need for excuses. sometimes the simplest things are not that obvious

 

[rudekid]

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

 

[Frank Kabel]

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

 

[rudekid]

excellent, thank