Jun 06 2021 07:20 AM
I need help
Im using Microsoft Excel 365 on my MacBook , i have a task making a data list searchable while it automatically filters with the new =filter function. since I'm new to all of this i have no idea on ow to write the code , if you have any helpful information that would be great.
kind regards
DG
Jun 06 2021 07:38 AM
@DM_G0 Well, why don't you share with us what kind of data you are working with and what the outcome should be?
If it's just the FILTER function you want to learn more about, check the link below.
https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
Jun 06 2021 07:50 AM
@Riny_van_Eekelen
Thank you for responding to my question, as you can see I would like to input for example the first letter or number on cell B1 while upon doing this action I would like the data to collapse and open automatically while I input a value whether is a number or text
my problem with this is that i do not know the formula for this.
kind regards
Jun 06 2021 08:34 AM
@DM_G0 I'm afraid it's not clear. B1 doesn't seem to be part of your data table.
Jun 06 2021 08:41 AM
@Riny_van_Eekelen
Hopefully this would be more clear, it gives me a value error.
Jun 06 2021 08:47 AM
@DM_G0 Not really! Please upload the file.
Jun 06 2021 10:40 AM
If you'd like organize the bar which automatically filter the range as you type in this bar - afraid there are no formulas for that. To filter the range after you type the text in "search bar" cell and press Enter - maybe, with VBA programming.
FILTER() function doesn't filter range in place. It takes the range, apply to it filtering conditions and copy result into another place, starting from the cell where you enter formula.
Jun 06 2021 11:55 AM
Solution
I don't know of a way to filter as you type using standard excel functions. You can do it using textboxes for the input, but it does require some vba.
There are instructions here how to set it up.
https://chandoo.org/wp/filter-as-you-type-excel/
Also, I attached an example file you can look at (right click on the worksheet tab, select view code, and you will see the macro code). One thing I did differently is I did not use a linked cell, I just changed the macro code to pull the value directly from the textbox instead of getting it from the linked cell.
Jun 06 2021 12:05 PM
This is exactly what i wanted but your using an ActiveX component. i have excel 365 on my MacBook Microsoft via apple doesn't allow me to use activeX only only vba and macro.
kind regards
Jun 06 2021 12:39 PM
Jun 06 2021 05:47 PM
Jun 06 2021 11:55 AM
Solution
I don't know of a way to filter as you type using standard excel functions. You can do it using textboxes for the input, but it does require some vba.
There are instructions here how to set it up.
https://chandoo.org/wp/filter-as-you-type-excel/
Also, I attached an example file you can look at (right click on the worksheet tab, select view code, and you will see the macro code). One thing I did differently is I did not use a linked cell, I just changed the macro code to pull the value directly from the textbox instead of getting it from the linked cell.