Forum Discussion
Dilema in Dynamic Array Filter Function With Merged Cells.
Hi,
I am trying to update my personal sale information table using the new dynamic arrays formula where appropriate but just in my first attempt of trying to use the 'Filter Function', I hit a roadblock!
Vital Info:
My Table needs a searchable drop-down list in multiple merged cells to relate with the price but I can't even get past the start of the formula and I think it because my table is merged and the item/product price table is not merged!!!
Please any useful suggestion(s)?
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.
18 Replies
As a comment, better not to merge the cells at all to avoid such and many other potential issues. For the majority of cases Center Across Selection works fine as substitution of cells merging.
Not sure I can visualise your issue, but l suspect that your FILTER function breaks-down due the fact that you selected merged fields (the green cells in this example) to define the "Include" part of the function. When you do that, the range that gets inserted in the formula A2:B4. The formula returns a #VALUE! error. You have to manually change that range to A2:A4 and then it will work. Note that the filter result produces a -zero- in the second field. This indicates that there actually is a blank in B3, "under" the merged cell A3 that covers both.
Thanks for the help so far, but here's an attachment of the worksheet I'm trying building up so that you'll understand better or advise me better on what can be done.
The lens and frame columns are where I want to put the searchable data validation list and the cost is where I think the Xlookup will be to replace the Vlookup I used before. I just want to update and practice.
Please help!
Thanks.
Thanks for this, but your schedule appears to contain no data. It has some data validation in it, but not the source to populate the drop-down with (e.g. the named range "TBL_SPH"). Perhaps best that you upload an extract of your old sheet with working formulae and indicate what needs to change. Alternatively, enter some real data in you new sheet, including list(s) for the drop-downs and the costs to be found by XLOOKUP, indicating where to put the outcome.
