Hi All,
I'm trying to set up a dependent validation list that pulls it's data from the Y values of a table (first column) the list will be rather large, and there will be a number of initial values (that this list is dependent on). I am hoping to avoid having to use a structure like that shown in Table 1 below, and use one like that shown in Table 2 where TRUE indicates which result relates to each particular input. I have the following non volatile* formula (as a named 'range') which works with Table 1 for the validation list:
Table 1Please Login or Register to view this content.
avenue vlresult av1 result1 av1 result3 av2 result2 av2 result3 av3 result1 av4 result1 av4 result4 av5 result3 av6 result2
Table 2
av1 av2 av3 av4 av5 av6 result1 TRUE TRUE TRUE result2 TRUE TRUE result3 TRUE TRUE TRUE result4 TRUE
I have tried to construct the formula myself but I've run into issues with MATCH only accepting a 1D array. Had thought to get around this by identifying the column first then conducting a standard INDEX MATCH on this 1D array, but alas I haven't had much luck with getting it to work...
Any and all suggestions will be greatly appreciated, however I hope to keep the formula non volatile.
I've attached a clean workbook with the above sample data and validation list: ExcelForum INDEX MATCH Validation.xlsx
Cheers,
DJ
* At least I don't think it's volatile!
Bookmarks