#REF error when referring to a closed workbook

[gers1978]

I have a workbook with a sheet that makes use of INDEX and MATCH to look up values in another workbook.

Problem is I always get #REF errors when I open it. I assume this is because the external workbook the formulas refer to is closed, cos when I open said workbook, it works fine.

I tried using VLOOKUP instead, same problem.

Is there any way at all round this?

I'm using Excel 2010.

Also posted here: http://www.ozgrid.com/forum/showthread.php?t=199563&p=769811#post769811

Many thanks.

 

[mole999]

what are your cell formulas, both type that fail

 

[gers1978]

what are your cell formulas, both type that fail

=INDEX('C:\Users\Me\<name>Desktop\Tracker.xlsx'!SummaryPMO[#All],MATCH($A4,'C:\Users\Me\<name>Desktop\Tracker.xlsx'!SummaryPMO[[#All],[Column1]:[Column1]],0),MATCH(B$3,'C:\Users<name>\Me\Desktop\Tracker.xlsx'!SummaryPMO[#Headers],0))

and

=VLOOKUP($A4,'C:\Users\Me\<name>Desktop\Tracker.xlsx'!SummaryPMO[#All],2,FALSE)

"SummaryPMO" is the name of a table in Tracker.xlsx</name></name></name></name>

 

[mole999]

some answers live here > Excel linked data formula turns to #REF upon opening workbook - Super User

I think INDIRECT is the way to resolve but I couldn't tell you the syntax quickly

 

[gers1978]

some answers live here > Excel linked data formula turns to #REF upon opening workbook - Super User

I think INDIRECT is the way to resolve but I couldn't tell you the syntax quickly

Yeah, I seen that link before, thanks, but those solutions either didn't work or are not suitable (I really don't want the external workbook open, and I don't want to paste values into my new workbook).

I thought INDIRECT also didn't work on closed workbooks?

 

[RoryA]

I think your problem is using Table referencing.

 

[gers1978]

I think your problem is using Table referencing.

Ahh ok, any way round that?

 

[RoryA]

Don't use it, or open the source.

 

[FDibbins]

I thought INDIRECT also didn't work on closed workbooks?

It doesn't, but you could try the Morefunc add-in
MOREFUNC | Ashish Mathur's Blog