Excel formula to lookup multiple criteria in another workbook that MUST remain closed.

[justmeok]

Hi everyone,
The above formula has proven to be more difficult than I expected. I need a formula to lookup date, then Store, then sales type and return the sales for that date for that site for that sales type. I have a few different formulas that do just that however they all require the source workbook to be open for the values to update. In this case we have multiple users but the source workbook must remain closed! An added complication is that both workbooks are stored on google drive so the google drive path is of course different for each user. If this is a complication that makes the above impossible we may be able to move the workbooks to a server although a google drive solution would be the preferred outcome. Example images of the workbooks attached.

So all you excel gurus out there is this even possible???


We are using Office 365 on Windows platform

 

[CesarF]

Maybe you can open the file as readonly instead...

 

[justmeok]

Thanks CesarF for your reply but I have tried that and due to the google drive path issue it still doesn't update the workbook. I am currently looking to see if OneDrive is an option