Formulas referencing cells in a closed workbook

I am finding inconsistency whether two separate formulas are successful in referencing cells in a closed workbook. I would would like not to always open the normally closed referenced workbook and have both formula's work if the referenced workbook is closed or open.

The following formula works when the referenced workbook is either open or closed:

Please Login or Register  to view this content.

Where CamperListCampWorkbook is a defined name in the referencing workbook ; ='Camper Names V6-0.xlsm'!Camperlist'

and Camper Names 6-0.xlsm is the filename of the referenced workbook
and CamperList is a defined range defined in the referenced workbook.

The referenced workbook is set in an Edit Links entry.

I have many of these formula's on 20 worksheets in the referencing workbook and would prefer not to change them....and shouldn't since these references are working just fine on the open or closed workbook


The following formula works only when the referenced workbook is open:

Please Login or Register  to view this content.

I had originally written this formula using the INDEX function, but that didn't work with the referenced workbook closed. I have been previously told that OFFSET is more stable than INDEX, so I switched to OFFSET in this formula hoping that would solve my problem. It did not.

Remedially, I wrote a VBA macro that checks if the referenced workbook is open and if not, opens it. I would prefer to disable this macro.

Advice, please.

Interesting: The one that works doesn't have the full path in it. I thought the full path was gathered by Excel by connecting the file name with the information in the Edit Links entry. If I did what you are suggesting, do you think I will still be able to use edit links to adjust when I move the referenced workbook?

Thanks for your help.

I must be doing something wrong. With both files open:

As a test I entered into the referencing workbook:

Please Login or Register  to view this content.

from that I got the right answer (6)

So then I copied the fully qualified path into the formula as

Please Login or Register  to view this content.

(That's without pressing enter.) When I press enter it resolves to #Ref

and the formula changes to:

Please Login or Register  to view this content.

It seems to insert the unqualified file name and the defined name "Campers" back into the formula.


What's going on here?

Thanks again for you continued help

I did some more experimenting:

I still don't know why the formula changed, but I re-read your advice and see that I am to close the referenced workbook...before clicking enter on the formula. That worked!

That must have been what I did when I entered the first formulas that I cited in my first post. I did that long ago (5 years at least)...and likely did it unwittingly. Thanks. I think you solved my problem.