Link not working when source workbook is open

Hi everyone,

First post here as I've usually been able to find the answer to any question I've had just by searching the site... so a thanks is in order.

My problem is that I have two separate workbooks. Workbook A is small database containing the details of various projects we are currently working on. It is maintained by a separate department and is only accessed in read only mode. Workbook B is a template that populates various fields by using an Index Match formula linked to workbook A once the Project Reference code is entered into the template.

Now this is where the issue arises. If I only open workbook B and enter the reference number, the link works without any issues whatsoever. However, if I have workbook A open, then open workbook B and enter the reference number, no data gets pulled through to workbook B.

I have checked the formula and there is no issues with that, especially as it works when wbA isn't open. I have checked to make sure the calculation options are set to automatic. I have also tried to update the links with both workbooks open, but that doesn't seem to help either.

I imagine it is a really easy solution, I have just never come across it in all my years working with excel.

Any help would be greatly appreciated.

I know, that is what has got me baffled. The formula is:

=INDEX('Programme Tracker.xlsx'!Project.Manager,MATCH($F$7,'Programme Tracker.xlsx'!UR,0))

It has worked for months without any issues, so I can only think that the owner has changed a setting or something....?

There are no error messages, it just returns a blank cell.

The names are just in a column of Project manager names and Unique references for each project.

There are several people that have read-only access to the file, but the there is only one person who maintains the data and has read-write access.

I would have guessed that a column had been added or deleted, but because you are using named ranges that shouldn't be the issue. Do you have access to the file yourself to be able to troubleshoot it? Verify the named ranges are what they're supposed to be?

The ranges are fine. That is the strange thing, if I don't have the file open, there are no issues whatsoever. The links pull through no problem. It only returns blank cells if the file is open. I know the easy fix is to not have source file open, but I need to know why it is doing this.