+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    08-01-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    21

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,836

    Re: Large FILTER SEARCH formula for dropdown lists in 1000+ Database points slows down UIf

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

  3. #3
    Registered User
    Join Date
    08-01-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Large FILTER SEARCH formula for dropdown lists in 1000+ Database points slows down UIf

    I completely forgot to ask a question...

    I'm asking if it is possible to do the filter formula on the 5 tabs in a different, more efficient way so it does not load the workbook as much, and as a result ( I suspect) reducing the UIform reload time after submitting?

    Specifically, A16 on each "Zone" Sheet
    Last edited by Sosborg; 08-20-2021 at 12:08 AM. Reason: Additional info

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    Re: Large FILTER SEARCH formula for dropdown lists in 1000+ Database points slows down UIf

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

  5. #5
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,836

    Re: Large FILTER SEARCH formula for dropdown lists in 1000+ Database points slows down UIf

    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.

  6. #6
    Registered User
    Join Date
    08-01-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Large FILTER SEARCH formula for dropdown lists in 1000+ Database points slows down UIf

    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!

  7. #7
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,836

    Re: Large FILTER SEARCH formula for dropdown lists in 1000+ Database points slows down UIf

    You're welcome, and thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Search (Filter) Dropdown list
    By mike2246 in forum Excel General
    Replies: 12
    Last Post: 07-31-2020, 11:57 PM
  2. Filter large database using Advanced Filter
    By thegrimmster in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-10-2011, 11:06 AM
  3. How to search/filter data in Excel dropdown having large number of values
    By sampr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-06-2007, 10:20 AM
  4. searching a large database with a long list of search terms
    By joe_d_builder@yahoo.com in forum Excel General
    Replies: 33
    Last Post: 01-10-2006, 02:30 AM
  5. searching a large database with a long list of search terms
    By joe_d_builder@yahoo.com in forum Excel General
    Replies: 34
    Last Post: 01-10-2006, 02:30 AM
  6. [SOLVED] searching a large database with a long list of search terms
    By joe_d_builder@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 01-10-2006, 02:30 AM
  7. [SOLVED] How to create identical dropdown lists in large blocks of cells
    By Paul in forum Excel General
    Replies: 2
    Last Post: 09-19-2005, 02:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1