Forum Discussion
Using FILTER function and VSTACK function together
We haven't got to VSTACK yet, never mind REDUCE, so I guess there is further to travel!
Combined#
= TOCOL(productLists, 1)
"Product list"
= FILTER(combined#, ISNUMBER(SEARCH(selectedCompany, combined#)))
PeterBartholomew1 omg, it is working so well. thank you for that 3D range stuff, which is covering all tabs between first:last! mentioned, whoa 🙂
(once we had a short chat discussion but do not remember when and what about, only that it was Excel)
as a "gift" in return added two twists to your solution:
--in column B added a monitoring formula to highlight if a product name contains >1 brand name
--replaced your data validation dropdown in G1 to a fancy(!?) quick 2+1 click selection method, hope you will like it
Thanks for the suggestions! The first:last! strategy is good in that it ensures added sheets are included in the 3D range. I hadn't come across the INDIRECT/CELL strategy, nor the double-click to force a recalc (F9 also works but perhaps lacks the elegance).
I worked through your formula to see what it was doing and came up with an alternative test to check that the user has clicked a company from the list.
= LET( ref, INDIRECT(CELL("address")), selected, IF( ISREF(ref distinctCompanies), ref, "doubleclick any cell then single click on a company" ), selected )- PeterBartholomew1
funny is that i was trying that space intersection method when created my twists but was not working however one of my favourites of "old" Excel tricks. maybe i have improperly set up my named ranges. so promoting your alternative to master then, thanks 🙂