Dynamic Dynamic Drop Down

Mitchell_Cole1 · 09-27-2019 05:52 AM

I have an issue with my data validation. I am trying to make a dynamic drop down, I’ve successfully done this in other apps by reference a table “schedule” which contains the schedule of components for a job.

I’ve done this by setting the column1 type to enum, and data validation to schedule[valueoptions] the column1 will then display the “valueoptions” values as a enumerated drop down.

The problem I am having is, I want the column on the schedule sheet to change based on answers in the current form.

Given that there are 20,000 predefined possible answers to this question my goal was to narrow down the possible selection based on location and type to lists of 50 or less. That means the there are 600 possible “value options” columns that are determined by the first three questions on the table.

I had tried a If(And([Column 1]=x, [column2]=y, [Column3]=z, schedule[xyz], If([column1]=a, [column2]=b, [column3]=c, schedule[abd]… Which worked for 20 or so options. When I took it up to scale, it stoped working. I know my syntax was right I think that the expression was just too big.

I’ve currently got a virtual column now concatenating the name of the column on the schedule sheet, but I seem to be unable to set the value of the calculated column as the name of a column on my schedule sheet.

Just stuck at this point any help or ideas will be appreciated

Mitchell_Cole1

Update, After consulting the help desk I was advised to use the filter function and change how my data was formatted on the schedule sheet. Below is my current data validation statement.

If([MV, LVAC or DC?]=MV, Lists[MV Cables], IF([MV, LVAC or DC]= LVAC, Filter(Lists[AC Circuits], Contains(Lists[AC Circuits], [What block is the Termination in?])), Filter(Lists[DC Circuits], contains(Lists[DC Circuits], [What Sub array (Panel Board/Circuit Board) is the termination associated with?]))))

I feel like I am closer, but it is still not working. It says “Function ‘FILTER’ should have exactly two parameters, a table name and a filter condition”. From my understanding this is two parameters in each filter statement. The table to filter “Lists[AC/DC Circuits]” and a filter condition “contains(…)”. Any advice, Insight?

Steve

The first argument to the FILTER() function must be a table name (e.g., Lists), but you’ve provided column specifications (e.g., Lists[DC Circuits]). FILTER() will produce a list of rows, but you appear to want a list of column values. Try replacing FILTER() with SELECT().

Mitchell_Cole1

Thank you