Forum Discussion
Using FILTER function and VSTACK function together
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 🙂