Is this going to work: 68.000 linked cells in a model?

[dms11]

Hi all,
I have an excel model consisting of 68.000 cells with data. Basically it is an admin dashboard and central data collection (parent model). These cells are linked with 6 different excel models that are providing the data to the parent model and are in a cloud. The idea is that employees will enter/update information into the individual files and these should be directly updated in the parent model.

The setup is there but I have not yet inserted the date as it will come by and by over the next 6 months.
Question: is this going to work or am I going to run into major memory or other related problems?

Best

 

[Rijnsent]

Hi Best,

I've seen models with more linked cells, but personally I tend to go for an VBA-import routine in the central model. So I think your solution will work fine, but keep an eye on performance and switch to VBA if the performance is crappy. I've been trying to find some articles on which solution is better under what circumstances, the only clue I could find was this page: https://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx , which states: "Avoid inter-workbook links when it is possible: they can be slow, easily broken, and not always easy to find and fix.".

Hope that helps a bit,

Koen

 

[dms11]

Thanks a lot. That does help!

 

[FDibbins]

if you have 68 000 links, are they ALL always updating, or is most of the data historic data that wont change? If this is the case, have you considered valuing the older data, to do away with those links?

 

[Rijnsent]

That is a good point Ford makes. What I do sometimes: leave the formula in the top row and every now and then copy it down, recalculate the sheet and copy-paste values for all but that top row. In that way you maintain the formula, but do get speed as you don't need constant recalculations. You could even write a macro that does that for you.

Happy Excelling ,

Koen

 

[FDibbins]

Koen, yes, I have done that a few times too, and always leave the formula in the 1st row - works great with daily-per-shift data over 10's of years

 

[Smitty]

Welcome to the Board!

It sounds like a better job for a database, especially with the multi-user element. Have you thought about Access?

 

[dms11]

Thats a very interesting thought, I havent figured out myself. The problem is that the master file is feeded out of 6 different files. Updates need to be done on a regular basis for all values (daily, sometimes more often than once a day). How would you go about the Macro to represent this?

 

[Rijnsent]

Hi Best,

how it would work:

Linked cells:
-press recalculate/F9 to pull in all values and everything should be updated
-in case of pasted values: copy formula down, recalculate sheet (pulling in the info) and copy-paste values for all but the top row
-every time you do that, the data is refreshed
-instead of doing this manually, you could write a macro to do those steps for you: macros/VBA can basically automate everything you can do manually in Excel (/MS Office)

Macro:
-run a macro when you want to update the data
-the macro will go to each of the 6 workbooks, open it and copy-paste the data into your master workbook

I have to agree with Smitty that your task sounds like MS Access would be a better solution, but that has a steeper learning curve (vs Excel/VBA). MS Access is a database, it would hold all data, all users would use the same database and their data would be "filtered for them" upon logging in (so they only see their own part, but the admin can see all data).

Koen

 

[FDibbins]

I get that there are a number of files that feed into the master file, but, as I asked, would ALL cells need to update (because of changes), or would only the latest data in the other files need to be pulled in (in addition to what had already been pulled in)?