How to link mutliple sheets to huge data table

[danhendo888]

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?

 

[Fazza]

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]

=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.

 

[Fazza]

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.