why wont INDEX work without $??

[colwyn]

s/s is 325501 rows deep and I run the following macro down column M.

=IF(L100001>0,INDEX(J$1:J100000,L100001,1),"")

Thing is, I don't want INDEX to look back more than 100,000 rows.
However, when from row 100,000 I delete the $ sign the macro produces #ref! in some cells and 0.00 in others. I cannot work this out and wonder if anyone has a solution?
Big thanks.
Colwyn.

[NBVC]

At row 1000, you would change the formula to, I think:

=IF(L100001>0,INDEX(J$100000:J100001,L100001,1),"")

and copy down

[colwyn]

Apologies NBVC, true to form I didn't explain myself correctly.

The macro starts at M3 with:

=IF(L3>0,INDEX(J$1:J2,L3,1),"")

At M100001 it is:

=IF(L100001>0,INDEX(J$1:J100000,L100001,1),"")

It's at this point that I need to restrict the formula to the past 100,000 rows.
However, when from row 100,000 I delete the $ sign the macro produces #ref! in some cells and 0.00 in others. I cannot work this out and wonder if you or anyone else has a solution?
Big thanks.
Colwyn.

[NBVC]

Revise the formula at M10001 to

=IF(L100001>0,INDEX(J$99999:J100000,L100001,1),"")

and copy down

[colwyn]

NBVC - I don't want to restrict the formula to row 100,000.
I want the formula to always take into account the previous 100,000 rows.
Big thanks.
Colwyn.

[NBVC]

Try then in first cell:

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

copied down.

[colwyn]

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]

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, ...)?

[NBVC]

Mine will give REF error for the same reason as daddylonglegs describes... It depends on what is in L3.

If your putting the initial formula in Row 1, then L3 must be at most 2... if it is 3 or more you will get that error, because the index range produced by the formula is only 2 rows tall and so it won't find a row 3 (if L3=3, the row number you want in your formula).

[colwyn]

NBVC and daddylonglegs - looks like I've hit a brick wall.
Once below row 100,000 the formula in column M will increasingly refer to rows below 100,000 and this seems to be presenting an insurmountable stumbling block.

Shg - yes, at row 100001 and below, I want the start cell of the range (which is J1 through row 100000) to start incrementing down (J2, J3, ...). Any ideas ??

Apologies to anyone having spent time wasted on this problem. It's only because I couldn't solve it that I posted it.
Big thanks.
Colwyn.

[NBVC]

Maybe posting a sample worksheet of what you have showing your expectations would help?