finding cell address

[Dokat]

Hi,

I am trying to write a formula for dynamic range where i know the the last cell address ($A$104) and need to find cell 52 rows before the last...Basically formula that will give me $A$52

Is this possible?

Thanks

 

[David Evans]

Have you looked at the OFFSET function?

 

[Dokat]

Tried this "OFFSET(ADDRESS(ROW(rng)+ROWS(rng)-1,COLUMN(rng)+COLUMNS(rng)-1),-52,0,1,1)" but it doesnt let me use address function.

Thanks

 

[Dokat]

I figured it out

="A"&OFFSET(INDIRECT(ADDRESS(ROW(rng)+ROWS(rng)-1,COLUMN(rng)+COLUMNS(rng)-1)),-52,0,1,1)

 

[David Evans]

I figured it out

="A"&OFFSET(INDIRECT(ADDRESS(ROW(rng)+ROWS(rng)-1,COLUMN(rng)+COLUMNS(rng)-1)),-52,0,1,1)

Well done! It's a good feeling to figure this stuff out!

 

[NARAYANK991]

Hi ,

The ADDRESS function can be replaced by the CELL function to get a shorter formula :

="A" & OFFSET(INDIRECT(CELL("address" , INDEX(rng , ROWS(rng)))) , -52 ,)

will return the same result as your formula ; the INDEX function using the ROWS function accesses the last cell of the range rng ; the CELL function returns the address without having to use the ROW parameter and the COLUMN parameter separately.

Narayan

 

[Dokat]

Brilliant! Thank you

 

[NARAYANK991]

Hi ,

Even shorter should be :

="A" & OFFSET(rng,ROWS(rng)-52-1,,1)

Narayan

 

[Dokat]

This is more efficient thank you.