Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named formula crashes Excel 2002 instantly
I have this formula in xl 2002 SP3:
=INDEX($A$1:OFFSET(A$1,lastContigRow-2,),MATCH(SUBTOTAL(5,$B$1:INDIRECT("B"&lastContigR ow-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= |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named formula crashes Excel 2002 instantly
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" wrote: I have this formula in xl 2002 SP3: =INDEX($A$1:OFFSET(A$1,lastContigRow-2,),MATCH(SUBTOTAL(5,$B$1:INDIRECT("B"&lastContigR ow-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= |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named formula crashes Excel 2002 instantly
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. ============ In , Dallman Ross <dman@localhost. spake thusly: I have this formula in xl 2002 SP3: =INDEX($A$1:OFFSET(A$1,lastContigRow-2,),MATCH(SUBTOTAL(5,$B$1:INDIRECT("B"&lastContigR ow-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= |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing border colors instantly | Excel Discussion (Misc queries) | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
How to convert relative to absolute addressing instantly in Excel | Excel Discussion (Misc queries) | |||
Excel 2002 crashes | New Users to Excel |