Dynamic cell reference

**jamie.finney** · 08-13-2010, 07:52 AM

I have a dynamic fomula that tells me the row number of my final data entry on 'PAGE1'
(=MATCH(9.999999E+306,'PAGE1'!B:B))

from this i have the row numbers of several colums ie. the last 3 row numbers
=MATCH(9.999999E+306,'SIM1'!B:B)-2 = 169189
=MATCH(9.999999E+306,'SIM1'!B:B)-1 = 169190
=MATCH(9.999999E+306,'SIM1'!B:B) = 169191

is there a way i can use this number in a formula. I need to look for example on PAGE1 in cell B169191. Normally this formula would be ='PAGE1'!B169191 however it would need me to manually type in this value.

Is there a way i can set a formula to select cell 'B' then my dynamic number automatically?

In my example (see attachment) the formula in cell B7 would need to be ='PAGE1'!B169189 (with value 169189 taken from D7)

Every file i load will be of a different lenght.

any ideas?

**teylyn** · 08-13-2010, 08:14 AM

Yes, there is. Use the Match() part you've already established to figure out the row number and insert it into the INDEX() function.

=INDEX('PAGE1'!B:B,MATCH(9.999999E+306,'PAGE1'!B:B,1))

Personally, I prefer 99^99 as the VERY BIG NUMBER for Match. I've never come across a situation where it was too small, and it's easier to write in a formula:

=INDEX('PAGE1'!B:B,MATCH(99^99,'PAGE1'!B:B,1))

**jamie.finney** · 08-13-2010, 09:30 AM

Thanks teylyn for data on the bottom row this has worked a treat!

still having some problems making this formula look at the value of a cell one or two rows from the bottom though can't seem to make it work.

It seems as though this formula only works for numbers, is there an adaption that can be used for text?

I've attached another image showing the problem.

all my import files have the times in the format '36s' which is a text string so excel doesn't like it using this formula

NOTE: just noticed a small mistake on IMG_2.jpg the bottom right image should have '36s' and '60s' rather than '36' and '60'

SUCESS: this seems to work
INDEX(LOG_DATA!F:F,MAX(MATCH(99^99,LOG_DATA!F:F),MATCH(REPT("z",255),LOG_DATA!F:F)-3))

**teylyn** · 08-14-2010, 04:23 AM

Jamie, post a workbook, not a picture, please.

**DonkeyOte** · 08-14-2010, 04:31 AM

Originally Posted by jamie.finney

is there an adaption that can be used for text?

REPT("Z",255) will normally suffice for strings

For more info. on formulae for determining last values see: http://www.xldynamic.com/source/xld.LastValue.html