Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-22-2022, 02:20 AM
le_robert le_robert is offline Using the Filter function to dynamic search large table Windows 10 Using the Filter function to dynamic search large table Office 2016
Novice
Using the Filter function to dynamic search large table
 
Join Date: Jul 2021
Location: France
Posts: 9
le_robert is on a distinguished road
Default Using the Filter function to dynamic search large table

Good morning,



I have a number of bilingual Excel glossaries created from an export from a third-party app, which has 14 columns and >300 rows. I want to be able to search this table so that it filters in real-time as I type a term in an ActiveX text box (one for English, one for French).

I've found a way to do this using VBA, but I was hoping to be able to do the same using the =filter() function so I can work with normal Excel files and not have to share macro-enabled ones around my organisation. All the articles/videos I've seen on this feature reproduce matching entries from a dataset into a new cell/table, but that's not practical for me considering the size of my tables. So I guess my question is: can this feature be used to do the same function as my macro? If not, is there another feature I'm not aware of that can do this?

I have attached a couple of screenshots showing a dummy dataset and how my macro solution works. I can also attach the workbook if that helps (presumably not a .xlsm for security reasons...)

Thanks very much in advance for your help. I'd be happy to clarify if anything isn't clear.

dummy dataset.jpg

working macro solution.jpg
Reply With Quote
  #2  
Old 06-22-2022, 05:16 AM
p45cal's Avatar
p45cal p45cal is online now Using the Filter function to dynamic search large table Windows 10 Using the Filter function to dynamic search large table Office 2019
Expert
 
Join Date: Apr 2014
Posts: 636
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

You won't be able to use =FILTER() within a table because by definition that cell with a formula in it has to be outside what it's filtering, that is, it has to be in another cell(s). Easy enough to do this while typing using an activeX text box and a linked cell. See attached where there's an activeX text box, linked to cell B10 whose value is used in a =FILTER function in cell D13. Of course, in this case it's just filtering the single column but it's very easy for that to apply to a whole table or a subset of the columns of a whole table.


Filtering the table itself is possible as you've said, with vba, but without vba there is the built-in functionality of the search box in the dropdowns of the headers of a table, this will visibly reduce the numbers of possibilities as you type (shown as checkboxes), but only for that column and within that dropdown's dialogue box. You'll only be able to see the full table result of that search-as-you-type when you finally OK that dialogue box.
2022-06-22_131304.png
Attached Files
File Type: xlsx msofficeforums49248.xlsx (14.9 KB, 7 views)
Reply With Quote
  #3  
Old 06-22-2022, 07:00 AM
le_robert le_robert is offline Using the Filter function to dynamic search large table Windows 10 Using the Filter function to dynamic search large table Office 2016
Novice
Using the Filter function to dynamic search large table
 
Join Date: Jul 2021
Location: France
Posts: 9
le_robert is on a distinguished road
Default

Thanks for the reply and info. It seems like VBA is the best option for my needs.

I suppose I could place my filter in a separate sheet and link it back to the original data. That would solve my problem of having two massive tables on the same sheet, but it feels a little over the top for what I want to do. I might try it anyway if I have time.

Thanks again.
Reply With Quote
  #4  
Old 06-27-2022, 01:06 PM
spillerbd spillerbd is offline Using the Filter function to dynamic search large table Windows 10 Using the Filter function to dynamic search large table Office 2013
Advanced Beginner
 
Join Date: Jan 2016
Posts: 60
spillerbd is on a distinguished road
Default

For tables that have a limited number of different values in a field, the use of Table Slicers is an option.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
LARGE function with 3 IF statements??? FloorManager Excel 3 08-23-2017 01:06 PM
Dynamic Dashboard w/ Search Criteria Exceluser Excel 2 04-27-2017 11:57 AM
Filter Search Button lonniepoet Excel Programming 53 12-23-2015 02:27 PM
Using the Filter function to dynamic search large table Data table search function omtinole Excel Programming 1 07-13-2012 10:03 PM
Using the Filter function to dynamic search large table Dynamic Filter 15 days lookout. stct Project 5 05-20-2011 03:30 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:49 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft