Stop external datasource locking to read-only

[BertieDastard]

No doubt this will have been covered previously but I couldn't find the answer in a search so apologies in advance!

Scenario:

I need to do a lookup from Workbook B, in to Workbook A

When I tried this locally it worked well just referencing the target workbook in the formula, however when I tried to do it with a URL (these are both OneDrive files) the data coming in was incorrect (date fields coming in with the wrong dates.

Instead, I tried pulling in the lookup table from the external source as a data connection, then of course I can reference an internal worksheet for my lookup, having pulled the data in from the other workbook
The issue comes when I try to update the source data, it is locked as read-only while the target workbook is open. This creates an issue as there are multiple users involved so its not viable to have the target workbook closed down every time the source data needs to be updated.

Example

Workbook B - Employee Contract End Dates - Updated by multiple line managers across business
Workbook A - HR Record - Updated by HR team

My issue would mean that the HR team would need to come out of the HR record xlsx before a line manager could update the contract end dates

(this is just an example to paint the picture, not the actual requirement, in case anyone was going to ask why HR records are being held on Excel lol)


Questions are:
Can I have the data source unlocked so it can be edited on the fly when the connection is "live"
or even better, is there a way to look up external Workbooks held on OneDrive without making a data connection but returning accurate data?

Thanks!

 

[severynm]

Can I have the data source unlocked so it can be edited on the fly when the connection is "live"

Nope - at least not directly. If this is the approach you wanted to take to have both ends of the connection open at the same time, what you could to is add a third "intermediate" file. File 1 writes to a temporary file 2 that no one ever opens, file 3 reads from file 2. Therefore, in essence file 2 becomes your "source" allowing you to have file 1 and file 3 open at the same time.

or even better, is there a way to look up external Workbooks held on OneDrive without making a data connection but returning accurate data?

Assuming you mean just using references in formulas, to my knowledge the data will not update unless the workbook is opened (although im not 100% confident). If you can get the data connection working - that would be my recommendation.