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.
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.
Here's the update on the worksheet, and like I said the lens type column is the primary point where I have an issue using the filter function, once that's resolved I know how to resolve the other parts of the table easily.
