Excel Advice neeeded

for general chat about stuff

Moderator: moderators

Excel Advice neeeded

Postby tones » May 29th, 2017, 3:11 pm

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.
"Munster could join the French League, or an expanded English / British league."
User avatar
tones
Brian O'Driscoll
 
Posts: 6126
Joined: July 21st, 2006, 5:35 pm
Location: In a glass case of Emotion

Re: Excel Advice neeeded

Postby Oldschool » May 29th, 2017, 6:12 pm

tones wrote: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...
Mirror, Mirror on the Wall who's the greatest player of them all? It is Drico your majesty.
User avatar
Oldschool
Devin Toner
 
Posts: 9912
Joined: March 27th, 2008, 2:10 pm

Re: Excel Advice neeeded

Postby ronk » May 29th, 2017, 9:06 pm

Connect to the tables instead of just referencing. Try using .xlsx format.
User avatar
ronk
Devin Toner
 
Posts: 8519
Joined: April 9th, 2009, 12:42 am

Re: Excel Advice neeeded

Postby Logorrhea » May 30th, 2017, 12:36 am

ronk wrote: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.
User avatar
Logorrhea
Malcolm O'Kelly
 
Posts: 3848
Joined: October 2nd, 2007, 1:20 pm
Location: D24

Re: Excel Advice neeeded

Postby tones » May 30th, 2017, 7:30 am

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.
"Munster could join the French League, or an expanded English / British league."
User avatar
tones
Brian O'Driscoll
 
Posts: 6126
Joined: July 21st, 2006, 5:35 pm
Location: In a glass case of Emotion

Re: Excel Advice neeeded

Postby tones » May 30th, 2017, 9:57 am

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
"Munster could join the French League, or an expanded English / British league."
User avatar
tones
Brian O'Driscoll
 
Posts: 6126
Joined: July 21st, 2006, 5:35 pm
Location: In a glass case of Emotion

Re: Excel Advice neeeded

Postby tones » July 4th, 2017, 3:13 pm

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.
"Munster could join the French League, or an expanded English / British league."
User avatar
tones
Brian O'Driscoll
 
Posts: 6126
Joined: July 21st, 2006, 5:35 pm
Location: In a glass case of Emotion


Return to General Chat

Who is online

Users browsing this forum: No registered users and 1 guest