Linking O365 to local excel files

[Steph77]

Hi all

I'm looking at building a dashboard in O365 using the snazzy dynamic array formulas that I've been reading about, but I'm running into a theoretical hitch with how to accomplish something.

I can schedule exports from our database to run automagically and save csvs with the data structure/content I want to a local network drive. I can then have other locally stored files update based on the information in these files. So far so good. But Excel 2016 doesnt hve the new formulas (and they won't be available until at least Excel 2022 from what I've been reading)

Sometimes users will be accessing these documents from off site, and so will not have access to the specified local locations, so as far as I understand it if I save the file to O365 for use from the cloud the links will break and the dashboard wont work.

I'd like this to run without user intervention (as in I don't want to have to log in every time the data changes and manually copy/paste data or anything like that). I'm also trying to avoid VBA that the users have to run themselves if possible due to network security settings (I don't even know how well VBA works with online O365 documents).

Is there any way to have a dynamic link between the O365 documents and the local documents that allows these links to stay active even when the user is viewing O365 from a remote location?

Tried google and a search here but struggling to find anything relevant.

Thanks in advance for any hints or tips

 

[mole999]

Just a thought, do your colleagues all have 365, will the work be available to all that need it via that contemplated route. Excel 2019 is out currently, whereas 365 is updated with new bits as and when ready.

 

[Steph77]

Yes everyone has access to O365 as part of our Microsoft licensing, and many already use that more than the local 2016 version for their work as they like the flexibility of being able to work without having to come on site. Are the new dynamic array formulas (FILTER, UNIQUE, SORT, etc) available in 2019? I had read they wouldn't be available until 2022.

 

[mole999]

From the online help file

Note: September 24, 2018: The FILTER function is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.
So I guess it isn't in 2019

 

[Steph77]

Yep that's what I'd seen too - I can get everyone to sign up for Office Insiders no problem, what I can't work out is how to link the online O365 file to the offline network files when the user isnt on the network

 

[mole999]

https://support.office.com/en-us/ar...fice-365-ac05a41e-0b49-4420-9ebc-190ee4e744f4