SOLVED

EXCEL LIMITATIONS

Occasional Contributor

Can one workbook handle 52 Tabs with 2400 "Retrieval Formulas" (In Other Words Pulling Information from 300 excel files". That's a combination of 124,800 formulas in one work book.

 

6 Replies

@DaCrab_101 

Worksheet and workbook specifications and limits

Excel specifications and limits

Please see this link above for more information.

 

 

Hope I was able to help you with this info.

 

NikolinoDE

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

 

@NikolinoDE 

 

I could not determine the answer to my question viewing that file.

 

@DaCrab_101 

It is not possible to answer your answer with yes or no, as there are limitations in sub and general areas.

Example: does your formula also include links? If yes, how many?

How big is the workbook? ... in MB.

Which version of Excel are you using?

Are the files on the hard drive, OneDrive, Sharepoint?

Concurrent requests via workbook? ... more than six?

 

That is why I have sent you this link, where you can look up for yourself where there might be a limitation in your work map, if at all.

 

Here is a link with further information about possible limitations.

Data Model specification and limits

 

Hope I was able to help you with this info.

 

Thank you for your understanding and patience

 

NikolinoDE

I know I don't know anything (Socrates)

@NikolinoDE 

Thanks, that makes more sense. I'm good with excel, wrote a ten year program to run the business, we are in our 6th year. 

There is a folder called "TECHS TEMPLATES". In that folder are 300 excel files. Each file represents 10 years, 2026-2035. The old program ends in 2025. 

There is a MASTER outside of that folder. The TEMPLATES are in a "Cloud" but they are downloaded to a Computer Folder where the MASTER is housed. I would like to have 52 TABS in the MASTER, one TAB for each week of a year. I was going to create 10 MASTERS, one for each year of the ten year cycle I'm creating. When the MASTER is opened I would like it to pull information from each of the 300 excel files. There are 124,800 links from those 300 files to the MASTER. I'm running Office Professional 2016.

What do you think?

best response confirmed by DaCrab_101 (Occasional Contributor)
Solution

@DaCrab_101 

Number of formulas is limited only by resources (memory, etc). 124800 formulas which get information from 300 files could have a lot of time if not hangs. Perhaps Power Query or like will be more optimal solution.

Thank you!
Time to learn something new.
Tim