#REF! when closing External Link

[BrotherDude]

Hello All,

I have an array formula that is only working when the linked spreadsheet is open. I am referencing a table, is that the problem?

The formula when both sheets are open:

{=IF(ROWS(B$6:B6)>$G$2,"",INDEX('[Serial Number Tracking.xlsm]Serial Credit Sheet'!A$2:A$76049,SMALL(IF('Serial Number Tracking.xlsm'!Table1[INV'#]=$C$2,ROW('Serial Number Tracking.xlsm'!Table1[INV'#])-ROW('[Serial Number Tracking.xlsm]Serial Credit Sheet'!$B$2)+1),ROWS(B$6:B6))))}

The formula when Source sheet is closed:

{=IF(ROWS(B$6:B6)>$G$2,"",INDEX('\\Server01\finance\Sales\LOOK UP SPREADSHEETS\[Serial Number Tracking.xlsm]Serial Credit Sheet'!A$2:A$76049,SMALL(IF('\\Server01\finance\Sales\LOOK UP SPREADSHEETS\Serial Number Tracking.xlsm'!Table1[INV'#]=$C$2,ROW('\\Server01\finance\Sales\LOOK UP SPREADSHEETS\Serial Number Tracking.xlsm'!Table1[INV'#])-ROW('\\Server01\finance\Sales\LOOK UP SPREADSHEETS\[Serial Number Tracking.xlsm]Serial Credit Sheet'!$B$2)+1),ROWS(B$6:B6))))}


also G2 from the formula references a countif from the source sheet, but when I enter a number the other formulas still don't work so I don't think that's the issue. That formula is below.

=COUNTIF('\\Server01\finance\Sales\LOOK UP SPREADSHEETS\Serial Number Tracking.xlsm'!Table1[INV'#],C2)

I've tried updated the links but still no luck, any suggestion would be great!
Thank you.

 

[BrotherDude]

The Table was the problem. Removed it and defined the range by column and all is well