How to link mutliple sheets to huge data table

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
126
Office Version
2016
Platform
Windows
In workbook, I have about 20 sheets that all have formulas (vlookups, sumifs, match etc) linked to one sheet (10,000+ rows)

But everytime I make a change in any sheet (e.g. number format, or dragging down formulas), it takes a few seconds to calculate.

Is there a better way to have the sheets 'linked' to the data file?
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,358
Depending on how they are done, formulas linked to the sheet can have orders of magnitude difference in performance. With large datasets differences can be seconds versus hours (to get the same results).

Depending on what is being done, alternative to formulas are queries and pivot tables. Database type approaches that do not use formulas. Sometimes these can do in seconds what takes efficient formulas very long times (hours).

And then there is VBA too.
 

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
126
Office Version
2016
Platform
Windows
=INDEX(Sheet2!$L$3:$L$4000,SMALL(IF(FREQUENCY(IF(Sheet2!$K$3:$K$4000=Sheet2!$K10411,Sheet2!$L$3:$L$4000),Sheet2!$L$3:$L$4000),ROW(Sheet2!$L$3:$L$4000)-ROW(Sheet2!$L$3)+1),1)

When I use the range from row 3 to 4000, this formula does not work.
But if I reduce the row range from 3 to 400, it does work.

Any idea why this is?

My sheet has 10,000 rows.
 
Last edited:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,358
Not knowing anything about it, I'm unable to comment, sorry.

If you want a specific/helpful :) answer, suggest you start a new thread and explain the setup, give some sample input data, matching results, etc, etc. And the Excel version. IMO the more work you put into asking the question the better the answer/s you'll get - so it is worth the effort. With a good explanation of the setup, there is a good chance someone will offer a really good solution.

FWIW, I'm guessing though that this is the sort of thing I'd try for a non-formula approach.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,060
Messages
5,472,213
Members
406,809
Latest member
haf19

This Week's Hot Topics

Top