Excel Advice neeeded

[tones]

Hi,
Just wondering if someone has come across this before in excel. I am having an issue where I am linking data from numerous files (sourced from cloud based system) into one large Master file sent to a client. The link is basic i.e. CELL A1 in a specific tab of the Master File is looking directly to CELL A1 in the feeder file.
The issue comes when there is more data on the feeder files and the Master file does not update (despite me dragging the formula down) unless I open the feeder file.
I have tried to go into Data - Edit Links and update Values but I still need to open all the feeder files. The Master file is stored on a shared Folder location (as opposed to a Shared Drive). I think this may be causing the problem but not 100% sure.
I am saving these files as xls files - wonder if I save them as csv files.

Also, I was initially going to try Index Match but I am not using references to search for data merely trying to link to avoid copying & pasting.

[Oldschool]

Hi,
Just wondering if someone has come across this before in excel. I am having an issue where I am linking data from numerous files (sourced from cloud based system) into one large Master file sent to a client. The link is basic i.e. CELL A1 in a specific tab of the Master File is looking directly to CELL A1 in the feeder file.
The issue comes when there is more data on the feeder files and the Master file does not update (despite me dragging the formula down) unless I open the feeder file.
I have tried to go into Data - Edit Links and update Values but I still need to open all the feeder files. The Master file is stored on a shared Folder location (as opposed to a Shared Drive). I think this may be causing the problem but not 100% sure.
I am saving these files as xls files - wonder if I save them as csv files.

Also, I was initially going to try Index Match but I am not using references to search for data merely trying to link to avoid copying & pasting.

Bit rusty at this sort of stuff - I'm assuming you are using the import external data feature.
If not that might be your problem. There could be security/permissions issues too.
IFyou don't get a better solution like maybe some setting that needs to be ticked/unticked or your shared folder needing to be set as some kind of default location then could you write a little macro that opens the files you need and does a "refresh" or "refresh all" and then closes the files - sledge hammer I know but...

[ronk]

Connect to the tables instead of just referencing. Try using .xlsx format.

[Logorrhea]

Connect to the tables instead of just referencing. Try using .xlsx format.

Yeah. If you just link to data in other sheets, those sheets have to be open for the links to work. I cant advise on how to fix (I assume as ronk suggested its a connection you need), but it is a limitation I've experienced myself.

[tones]

I have seen in a previous role that when linking to other sheets - those sheets do not need to be open. We had been opening them for years.
Have tried (briefly) saving the feeder files in txt format and importing this was - which has worked.
Will have a look at the xlsx option, and the protection point.
Would love the macro option but I'm terrible at them and in a small company so there is not a lot of help with writing macros available.

[tones]

Also, because the Master file is on a shared folder/location as opposed to a shared drive - would this have an impact?

The shared drive was set up for storage of files for historical reference purposes

[tones]

By way of an update - and to say thanks,

The txt files worked and imported the data in a matter of seconds. Also, I created summary sheets (for e-mail reporting) and that was instantaneous.

Many thanks for your help, funnily, I had to save the files down as xls (as the receipient runs Macros off them) but this has had no adverse affect.