Dynamic Data Validation List Inside a Table

[ko1967]

My production version of Excel365 just got updated with dynamic arrays .. hallelujah.

Creating a dynamic array formula (using FILTER) and then referencing that cell# in data validation is awesome. But that relies on only a known number of occurrences.

Does anyone know how to do this inside a table.

Lets say you have a billing table with columns for Client and Project and then you enter your billing data for the client and project. Currently the Project data validation list all projects from the Projects table but I'd like to limit the Projects (via data validation) to just those corresponding to the client in the [@Client] field so that thousands of projects don't show up for every client. The code is simple (if I put it outside a table and know the [@Client] value, i.e. =SORT(FILTER(tblProject[Project],tblProject[Client]=[@Client])). But I can't put this formula in a cell outside the table so it can spill because it is dynamic based on which row (i.e. which client) the relevant for the filter and I can't put the code directly inside the data validation box as it won't provide the results and I even tried wrapping it in INDIRECT() and it won't pull dynamic list. Any ideas?

Thanks in advance.

 

[cyrilbrd]

Hi, could you post a small sample? Data validation can easily limit the number of choices given a Main Table that would list for each client the authorized corresponding projects.
example:

Book1
	A	B	C	D	E	F
1	Category	Item		Client_1	Client_2	Client_3
2	Client_1	Project_59		Project_13	Project_5	Project_26
3	Client_2	Project_27		Project_1	Project_27	Project_58
4	Client_3	Project_60		Project_59	Project_23	Project_56
5	Client_3	Project_26		Project_8	Project_47	Project_19
6				Project_32		Project_62
7				Project_65		Project_60
8				Project_38
9				Project_69
ko1967

Cells with Data Validation
Cell	Allow	Criteria
A2:A5	List	=myClient
B2:B5	List	=myProject

With Created in the Name manager
myClient =Table1[#Headers]
myProject =OFFSET(myProjectList,0,0,COUNTA(myProjectList),1)
myProjectList =INDEX(Table1,0,MATCH('ko1967'!XFD1,Table1[#Headers],0))

 

[ko1967]

Thanks cyrilbrd. Yes, you have the workaround necessary but it comes with a price. Every time you add a new client you have to add a new column and every time you add a new project you have to add that project to the column for that client. So I have to maintain a client list, a project list (tied to clients), and then another table to put these in rows and columns.

Microsoft was so close with dynamic arrays and filtering but like other things they failed to include them in all aspects (i.. allow the formulas inside data validation).

The workaround I found to be better for me was to filter all projects (regardless of client) on the Active/Inactive flag. This allows me to create a spilled array =FILTER(tblProject[Project],tblProject[Active]="Y") and then reference the first cell (e.g. =A1#) inside the data validation. It doesn't limit it to only the client in this row of the table but it does limit it to a small enough set of projects that are active that I can see a manageable list.