why wont INDEX work without $??

**NBVC** · 03-02-2009, 05:52 PM

Try then in first cell:

=IF(L3>0,INDEX(INDEX(J:J,IF(ROW()>100000,ROW()-100000,ROW())):J2,L3,1),"")

copied down.

colwyn · 03-02-2009, 07:26 PM

NBVC - thanks again but it doesn't work.
At row 100,001 and beyond it works fine but at rows 3 :100,000 it produces #REF! with the tag "Invalid Cell Reference Error"
Maybe it isn't possible to achieve ??
Big thanks
Colwyn..

**shg** · 03-02-2009, 07:43 PM

At row 100001 and below, you want the start cell of the range (which is J1 through row 100000) to start incrementing down (J2, J3, ...)?

**daddylonglegs** · 03-02-2009, 08:40 PM

Try this formula in M3 copied down

=IF(L3>0,INDEX(INDEX(J:J,MAX(1,ROW()-100000)):J2,L3,1),"")

You might still get a #REF! error because it depends on the values in column L. If the value in L is >= than the row number (or > 100000 past row 100000) you'll get a #REF! error