Use of Indirect() to link to outside Workbook cells

[jim may]

I always have problems with the ""'s in setting up the following link

In my
cell ref I have:
F4 C:\Users\Owner\Documents\ExcelChest\
F20 MySourceWB.xlsx
G20 Testing (My Sheetname)
H20 A1 (contains text)

This is my current attempt, which IS NOT working. Can someone assist in correcting?

=INDIRECT("'["&F4&F20&"]"&G20&"'!"&H20)

 

[Jonmo1]

Indirect doesn't work on closed workbooks.
If the workbook isn't closed, then the path is not required.

 

[Jonmo1]

There's an addin called Morefunc that has a similar indirect function that does work on closed workbooks.
It's linked in the Recommended Addins thread.

 

[jim may]

I've recently has a template file go bad with the intrusion of 12 sheets of #REF and 80 formulas per page (960 cells now with #REF ....)

I'm gonna have to recreate all links and was looking for a more fool-proof way to avoid this from occurring again. Currently my "MySourseWB.xlsx" file
is open which is why I'm likely getting the error, but I was hoping to do "whatever" to avoid the #REF from occurring again, after I recreate (estimated 2+ hours to do)
Thanks,

Jim

Jim

 

[Jonmo1]

Sorry to hear that.

was looking for a more fool-proof way to avoid this from occurring again

There's no such thing as fool-proof .

Can you use Find/Replace to replace the #REF with the correct workbook link?

 

[jim may]

This file is on a clients PC. I'm headed there tomorrow. I'll begin with a number of "TRYS" at doing something... Tks, Jim

 

[mikerickson]

.
There's no such thing as fool-proof .
...

I recall a quote "Nothing is fool proof, because fools are so ingenious."

 

[Jonmo1]

I recall a quote "Nothing is fool proof, because fools are so ingenious."

Or they'll just invent a more foolish fool.