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
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?
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().
Thank you
User | Count |
---|---|
43 | |
27 | |
23 | |
14 | |
12 |