Stop external datasource locking to read-only

BertieDastard

New Member
Joined
Oct 24, 2014
Messages
2
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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

severynm

Board Regular
Joined
Jan 8, 2021
Messages
220
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,073
Messages
5,704,141
Members
421,328
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top