Data Connection Excel 2010 vs Excel 365, Having Trouble Connecting Between Workbooks

[marathonrunner]

Hi Everyone,

I use Microsoft 365 and have no trouble creating data connections between excel sheets. I have a model that gets updated by linking it to 4 other separate excel workbooks.

I tried to replicate this process in Excel 2010 as some people in my company use Excel 2010 but was not able to. Am I missing something? I can't seem to find a data connection from my excel model to other excel workbooks in Excel 2010.

I found a data connection between Excel and Text, Excel and Servers etc, but not between Excel workbook and Excel workbook in Excel 2010.

Alternatively is there a way around this problem? I would love to link my workbook to Google Sheets and I just need to get my Google Sheets updated with the most current data but I've not figured that out as well (also I don't know if that would work for my Excel 2010 colleagues).

Hope the explanation was clear, do ask me if I didn't explain anything correctly. My main objective is to have separate sources between my model and my data, I would like to have the data updated separately and then have it flow through to the model by right click and refresh data connections in the model. I am able to do it now in my Excel 365 between Excel and Excel but have not been able to replicate on Excel 2010.

[FDibbins]

How exactly are you "data connecting" to other workbooks?
Have you tried just using regular formulas instead?

[marathonrunner]

Hi FDibbins,

The way I have it set up is: I went to Data Connection -> Get Data -> From File -> From Workbook

I have 4 external files that I need to update, 2 are yearly, 2 are weekly. So my main concern is the weekly files.

Depending on the time of year I can have anywhere between 30k to 150k rows of data in each of those 2 files. So the great thing about using a data connection is that I just have to open up those two workbooks update them and then I just have to refresh my model. The Data connection puts everything into table format so it is nice.

i.e. week 1 I might have 30k rows of data, just update those two separate workbooks and click refresh in the model, week 2 I have 35k rows of data, do the same and refresh the model

I understand I could do this without the data connections but I'm trying to build a tool where the end users do not need to mess with the model workbook itself, all they need to do is update the external files. It's just something I prefer since it makes the model file look leaner (I can use the very hidden option to hide tabs) and more like a tool than an excel workbook. The model is basically a tool that should be able to refresh data with 1 click and provide the output the end users need while look lean and presentable. The external data sheets are just that, data sheets that needs to be copy and pasted into.

I'm not much of an expert in Excel, apologies, what do you mean by using regular formulas?

I was also thinking the data connection will make the file itself leaner so if I can avoid additional formulas I will try to avoid them.

[FDibbins]

A simple formula to reference a cell in another workbook, could be something as simple as ='[sample 1.xlsx]Rate'!$A$4

[marathonrunner]

I understand. So in my Model.xlsx I need to reference to External Data.xlsx for cells A1:CE200000?

Usually at year end we end up with around 1xx,xxx rows of data but since I don't know where the end point is I should just go down 200,000 rows?

So am I correct to conclude Excel 2010 is not able to set up a connection between 2 excel workbooks?

[FDibbins]

I dont know if it can set up that connection or not, but it seems it can (I dont have 2010)

A quick google search of "Data Connection Excel 2010" turned up some interesting links
https://www.google.com/search?q=data...hrome&ie=UTF-8

See if they help?