Dynamic Arrays, Dynamic Drop down list, Filters and multiple rows

You want data validation lists to be based on entries in the current row in the Non-Conformities worksheet. You need a mechanism to pull in the current row number.

One way.

Admin!A1: =IF(COUNT(SEARCH("Non-Conformities",CELL("Address"),NOW()^0)),CELL("row"),#N/A)
Admin!B1: =INDEX('Non-Conformities'!A:A,$A1)

Fill Admin!B1 right as far as needed.

Admin!B3: =UNIQUE(FILTER(Table11[Country],Table11[Security Region]=B$1))
Admin!C3: =UNIQUE(FILTER(Table11[City],Table11[Country]=C$1))
Admin!D3: =UNIQUE(FILTER(Table11[Address],Table11[City]=D$1))

As you make entries in the Non-Conformities worksheet, that will trigger updating the formula in Admin!A1, which will update the values in Admin!B1:D1, which will update the spilled formulas in B3#, C3# and D3#.

However, this isn't robust. Make an entry in, say, 'Non-Conformities'!A5, then decide you need to correct 'Non-Conformities'!E3, move to that latter cell, but the formulas in Admin!A1:D1 will still refer to row 5.

The only way to fix this/make this robust is with VBA, specifically, a Worksheet_SelectionChange macro for the Non-Conformities worksheet.

Please Login or Register  to view this content.

Great answer, thank you. This has been a great experience and I hope the forum the all the best

Not sure how to close thread