Forum Discussion
Dilema in Dynamic Array Filter Function With Merged Cells.
ituryu Have a look at the attached (updated) workbook. It has a searchable dropdown for Lens Type. Type DUO and click the arrow and only lenses with DUO in the name are listed. Type BLUE and all the blue lenses come up. Is this something you can use?
See here for more.
Hi,
A searchable drop-down list will return a list unique in a way that it displays items or products or names containing a character that you typed. For instance, the lens type has Duo as a type of bifocal and I want to see only the Duo lenses and not the entire list if I type Duo in the lens type column, it will only list out the bifocals having Duo only and nothing else! In my early version of this worksheet of mine, I did it but using multiple steps and long formulas. Now with the filter function from the videos I've seen so far, no multiple columns of search, frequency or final list.
I think my merged cell here is in the way and I can't get rid of it cause its the only way I know to keep the lens Rx columns all in one worksheet.
So please if there's anyone else who understands how to go about this, HELP!!!
Thank you.
ituryu Have a look at the attached (updated) workbook. It has a searchable dropdown for Lens Type. Type DUO and click the arrow and only lenses with DUO in the name are listed. Type BLUE and all the blue lenses come up. Is this something you can use?
See here for more.
ituryu Received the same link from someone else on another thread. Hadn't seen this one yet. Thanks for sharing!
I'd like to say thanks for the great help so far with my excel dilemma, but I've finally resolved it with the help of this video I found online and so far seems to be working exactly as I want it to.
Here's the link: https://www.youtube.com/watch?v=waqzwMCYD9I&t=4s
Cheers.
ituryu Had a brain wave an came up with a possible solution. You'd need a helper column and you need to work in sequence. Once you have entered your choice in a drop-down you can't go back. The attached file demonstrates the principle in a simplified example, but I trust yu are able to implement it in your own schedule, provided you find it acceptable.
ituryu I realise that the solution found on the web only works when you link a drop down to one cell at the time. Not very useful in your case. I you "only" have 20 or so cells to have the dropdown in you could consider to create a filter for every cell. One for D3, one for D5, one for D7 etc. I have done that in the attached workbook for these first three cells. I guess you can do the rest yourself. Haven't yet figured out a different solution, though. Will get back if/when I do.
We have a slight problem!!!
The formula seems to work for the first cell in the lens type column, but does not work for the next cell!!!
I thought this was over, I need to look into it and try hard to understand this dilemma.
Thanks.
ituryu Very good! Learned something myself as well.
Thank you very much, you understood what I asked for and I followed your example and its working fine and its exactly what I can use presently.
Cheers.