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!
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!