Why this match/Index formula works in Excel 2007 but not in Excel 2002??

[EvelynLu]

Hi all experts out there,

May I know why my below formula work for Excel 2007 but not Excel 2002?

=IFERROR(INDEX(PartNum!A1:C45000,MATCH(1,(PartNum!B:B=SR_From!H10)*(PartNum!C:C=SR_From!H11),0),1),"")

As for Excel 2007, there is nothing display when nothing match. Whereas when open the Excel file in version 2002,
it appears "#Name" error. May I know how can I rectify this issues?

Thank you so much for all advise.

 

[EvelynLu]

Also,

I notice the formula appear "0" by default.. is there anyway to display empty cell without any number/alphabet? How should I change the code?

Thank you very much.

 

[easy2understandexcel]

Hi Guys,

Thank you soooo much. After change the range name, it can work... but have a problem here.

As my PartNum tab actually link to another file which has 45000 lines but the system shows error that
"Excel cannot complete this task with available resources. Choose less data or close other applications"

End up it only manage to pull only 16500 lines and not the rest. Is there any walk around method for this to be able to pull 45000 lines and yet without having this error message?

Really appreciate you guys help... Any advise will be grateful.

Thank you for save my life...
Thank you

Reboot your computer and try again. Honestly.

 

[EvelynLu]

Hi hi..

Reboot already still same... can only link 16000plus line...

 

[MARK858]

You might be getting limited by Excel 2002's memory limit, which if that is the case I don't think there will be much you can do formula wise.
See the link below about memory usage..

Excel Memory Limits - Decision Models

 

[EvelynLu]

Oh Dear....

Then any workaround method that able to retrieve data from a worksheet that has more than 16000 lines? If separate to 2 worksheets, I really don't know how to write the above formula already....

How How??

Thank you...

 

[MARK858]

If it is the memory issue then there will probably be a VBA alternative but I am afraid I won't be writing it as I don't have a Mac these days (let alone with a 2002 version of Excel) to test on.

Hopefully there is someone who is in a position to write the code or even possibly there is one of the formula guru's who can think of something to help improve the process.

I hope someone can think of something for you, all the best.

 

[EvelynLu]

I see...

Still much appreciate your kind help. Btw, is there a way for the formula to display "" empty cell instead of "0" when the detail not enter yet? Any idea how to change the formula?

Thank you.

 

[MARK858]

You could add in another If statement but that won't help your calculation problems so if I was doing it in a later version then I would go into advance options, display options for this spreadsheet and uncheck the "display zero's in cells that have a zero value"

I think in older versions it was under the Tools menu, Options, View tab or else custom format the cells as 0;-0;;@

 

[EvelynLu]

Thank you soooo much. Just now suddenly everything working well. Very strange.

Thank you so much for the formula update. May I know how can I change to "Solved" for this thread?

Thank you once again to Mark858 and all experts out there.

 

[MARK858]

You don't mark threads "Solved" on this forum, you just say Thank you (which you have already done ).