Excel links not working unless source workbook is open?

Question

I have a Master workbook, looks like this:

and I have individual statementts for all the employees which look like this:

I have all my dynamic named ranges working fine on the Master sheet, all looking good.

Now I notice that if I close the Master workbook, then the highlighted area where the data is supposed to be, all turns to #REF erros...

all the formulas to pull the data are like this when the workbook is open:

=INDEX('Staff Rewards Master.xls'!BenefitsData,MATCH(FirstName & " " & Surname,'Staff Rewards Master.xls'!Employees,0),MATCH(A:A,'Staff Rewards Master.xls'!BenefitNames,0))

and the formulas (naturally) become this when the Source or Master workbook is closed:

=INDEX('C:\vss\Staff Rewards Statements\Staff Rewards Master.xls'!BenefitsData,MATCH(FirstName & " " & Surname,'C:\vss\Staff Rewards Statements\Staff Rewards Master.xls'!Employees,0),MATCH(A10,'C:\vss\Staff Rewards Statements\Staff Rewards Master.xls'!BenefitNames,0))]

BTW, if I copy this to the RUN dialog (taken from the paths above), the file opens in Excel with no problem:

C:\vss\Staff Rewards Statements\Staff Rewards Master.xls

Here is the error message that I get if I calculate the Statement sheet after closing the source Master workbook:

Now, I should mention that if I remove the defined names and just put in cell references it works fine, but why can't my dynamic named ranges work?

for example, this has no problems:

=INDEX('O:\Dev\Staff Rewards Statements\[Staff Rewards Master.xls]Staff Benefits Master'!$B$7:$K$150,MATCH(FirstName & " " & Surname,'O:\Dev\Staff Rewards Statements\[Staff Rewards Master.xls]Staff Benefits Master'!$A$7:$A$150,0),MATCH(A10,'O:\Dev\Staff Rewards Statements\[Staff Rewards Master.xls]Staff Benefits Master'!$B$6:$T$6,0))

So what am I missing or doing wrong here?

Solution 1

You can't use names to reference ranges in closed workbooks.

I would guess (without seeing the book) that you could replace your named references with whole-column / whole-row references (as the match won't trigger on blanks). This would solve your need and wouldn't cause speed problems.

HTH

Solution 2

The INDEX function could be volatile or not depending on the version of excel you are using, and it could have direct or indirect dependents. (Source:http://www.decisionmodels.com/calcsecretsi.htm).

Anyways the consequences are either it recalculates the result or not. In case it recalculates the results in this case maybe it needs opened excel files.

ME I am using the same type of directories as yours with non volatile functions and I have no problem (office 2016). Even with updating the results, each time I open the file everything is updated.

Solution 3

A late reply, and answered using 2016 365, but ... External references DO work in external workbooks even then they are closed. The error you are getting is indicative of where the external named reference refers to a table, such as: "=Table1[Employee]". This does not work!

What will work is where you have turned off the Excel setting /options/formulas/'Use table names in formulas'. The external named range will need to be deleted and re-setup with this setting turned off. So the new named range will be something like "=worksheet1!$D$4:$D$100". You may wish to see how the range expands when you increase the size of the table.

You may then still get an error but it will change from #ref to #value. Errors are still caused if you try and use, say, OFFSET with an externally defined named range - as hinted above, I think this is connected to the volatile nature of any formulae.

Brian Barrett

Comments

[Our Man in Bananas]

how discombobulating, there was I struggling for days with dynamic named ranges using INDEX and OFFSET and now I find Excel can't take it!

[Cheeky Charlie]

Some reasoning (call it comfort, rather than assistance): With a range reference, Excel can access the 'raw data' of the file to interrogate the relevant range. With a name, it has to do more, it has to find the range the name refers to, then interrogate it. Can't do that additional logical step without opening the book. Same logic applies referencing a closed book with Offset formula. I am aware this is a simplification of the file structure, but it makes sense to me!

[Wai Ha Lee]

Do you have two accounts? I see that another user called Rachel Godsell submitted this edit to this post. If you do, you should probably see What are the rules governing multiple accounts (i.e. sockpuppets)? - having one account get reputation by editing posts of another is expressly forbidden.