Named formula crashes Excel 2002 instantly

[Dallman Ross]

I have this formula in xl 2002 SP3:

=INDEX($A$1:OFFSET(A$1,lastContigRow-2,),MATCH(SUBTOTAL(5,$B$1:INDIRECT("B"&lastContigRow-2)),$B$1:INDIRECT("B"&lastContigRow-2),0))

"lastContigRow" is a named range (cell) and works fine. Right now
it's Row 477.

The formula successfully shows the contents of a certain cell in
Row A that is not hidden.

When I give that formula a name and try to invoke that name, however,
Excel crashes instantly.

Can anyone offer any help? I would like to know why this crashes xl,
but more important yet is finding a way to replicate the function without
crashing xl.

=dman=

 

[Guest]

The problem appears to be INDIRECT: I get same result with "003.

Try:

=INDEX($A$1:OFFSET(A$1,LastContigRow-2,),MATCH(SUBTOTAL(5,OFFSET($B$1,0,0,LastContigRow-2,1)),OFFSET($B$1,0,0,LastContigRow-2,1)),0)


OR

=INDEX($A$1:OFFSET(A$1,LastContigRow-2,),MATCH(SUBTOTAL(5,rngb),rngb,0))

where rngb:

=OFFSET($B$1,0,0,LastContigRow-2,1)

HTH

 

[Dallman Ross]

Okay, I sweated out a solution here. This works and doesn't crash
Excel. Basically, I dumped the INDIRECT in favor of OFFSET and
made sure all refs are absolute, not relative. The rest is just
clean-up work.

=INDEX(OFFSET('Realized Gains'!$A$1,1,,lastContigRow-1),MATCH(SUBTOTAL(5,OFFSET('Realized Gains'!$A$1,1,9,lastContigRow-1)),OFFSET('Realized Gains'!$A$1,1,9,lastContigRow-1),0))

I named the formula and apply it to a chart as a dummy series name
in order to print the name of my filtered area inside my chart legend.

============