Large FILTER SEARCH formula for dropdown lists in 1000+ Database points slows down UIform

Hi!

I have a database that has 5 "Zones" (individual tabs), where each one needs to filter using text or dropdown without interference from other zones (hence 5 tabs).
I suspect this is slowing the sheet down terribly since it takes a good 45 seconds for the User Form to reload sometimes.

Due to the need of keeping the worksheet in Excel Online, not desktop, I have no option to use VBA either.

Please see the Zone tabs in the attached workbook. Names have been created by a random name generator.

Hopefully, this is clear.

I don't follow. What's your question/issue? Is there a particular cell you're looking for a formula for?

I would start be changing your whole column formulae to a sensible limited range (A2:A1000).

You've got your database in an EXCEL table, so reference the table (as John says, this reduces the range.

=FILTER(Table1[[ID]:[Tasks]],ISNUMBER(SEARCH(A6,Table1[Zones])*(SEARCH(A8,Table1[Tasks])*(SEARCH(B2,Table1[BOX])*(SEARCH(A10,Table1[ID])*(SEARCH(A2,Table1[Time]*(SEARCH(A4,Table1[Client])))))))))

I don't know how much this will help, but it's probably a start.

Such a little tweak with such a tremendous amount of result! It is like night and day. I'm so glad to be reminded to use the table reference again.

Thanks, guys!

You're welcome, and thanks for the rep.