This has to be the weirdest problem I have ever encountered.
I have a workbook with multiple worksheets.
The master (data source)worksheet is called Combined.
Two of the other worksheets (e.g. Macquarie Bank - Personal, NAB Credit Card) draw data from the Combined worksheet.
I have attached an Excel workbook for perusal.
On the NAB Credit Card worksheet the formula works perfectly and I can fill it up or down without errors!
This formula works correctly on all sheets EXCEPT Macquarie Bank – Personal:
=IF(OR(ISBLANK(VLOOKUP($B20,Combined!$B$20:$C$3019,1)),VLOOKUP($B20,Combined!$B$20:$C$3019,1)<=0,$B20<>VLOOKUP($B20,Combined!$B$20:$C$3019,1)),"",INDEX(Combined!$D$20:$D$3019,MATCH('NAB Credit Card'!$B20,Combined!$B$20:$B$3019,0)))
The nested error checking before the actual index/match is to prevent #NA and #Value errrors from being displayed and so preventing correct data transfer and resulting calculations on the NAB Credit Card worksheet.
HOWEVER, if I either copy and edit the formula, or type it is manually, to the Macquarie Bank - Personal worksheet it fails!
THIS is the formula being used on the worksheet Macquarie Bank – Personal and is NOT working correctly as it does on all other worksheets:
=IF(OR(ISBLANK(VLOOKUP($B20,Combined!$B$20:$C$3019,1)),VLOOKUP($B20,Combined!$B$20:$C$3019,1)<=0,$B20<>VLOOKUP($B20,Combined!$B$20:$C$3019,1)),"",INDEX(Combined!$D$20:$D$3019,MATCH('Macquarie Bank - Personal'!$B20,Combined!$B$20:$B$3019,0)))
Any ideas as to why this is happening? The ONLY difference in the formula is in the MATCH portion of the formual where the reference to the source changes.
Any ideas of how to fix it?
A SECOND issue, visible in the attached workbook, worksheet NAB Credit card occurs in cells L521 and L624 which is affecting the desired output in cell M624. All of the values are correct and cell L521 total matches cell L624 total som M624 should be outputting "OK", not "Error"! Any idea as to what is happening in this formula?
Thanks in advance for any assistance.
Bookmarks