Google Sheets Data Validation Warnings When Using Relative Paths with Filter View
[Update 1] This appears to only happen when using a filter view
[Update 2] Link to sample sheet: https://docs.google.com/spreadsheets/d/15eIm2ldEvgcoImTxB6Ik7c8AEJ3bhPzttpRUszN_Jvc/edit?usp=sharing
I have a sheet where I need to use data validation to create dropdown lists that change by row. The dropdown list options are maintained using cells on the same row (i.e. row x's options are also maintained on row x). I'm using "Data Validation -> List from a range" with the following formula for the cell on row 3:
=Options!$B3:$E3
As you notice, I'm keeping the row path relative so that the dropdown list changes from row to row. The formula for the cell on row 4 is therefore:
=Options!$B4:$E4
This works correctly in terms of what appears in the dropdown lists. The problem is that Google Sheets complains about the inputs I select saying the input data is invalid (even if I select it from the dropdown list). There are no circular dependencies between the validated cells and the ranges containing the dropdown list items.
The only way the warnings go away is if I use absolute paths (i.e. $B$2:$E$2). However, this makes it difficult to copy/paste the cell and data validation to other rows.
Any idea how I can fix this?
The data is supposed to be text, and I've formatted both the ranges of items and the column where I'm using data validation as plain text.
if you want to use filter view with sorting capabilities...
right now you are doing dropdowns from F:H range which is dynamically based on E column. what you need to do is to make it static like J:M range and then create a dropdown from K:M. this way if you sort or filter the A column the data validation won't throw any errors:
- Remove first element from a $_POST array
- Trouble reading numbers from file into array
- Dynamically populate a subarray inside of an array declaration
- Only print array row data if id is found in a whitelist array
- Increment variable name in a loop
- Transpose an associative 2d array
- how to split each word in a string in a separate list
- Array is always empty even when it is expected to have elements
- Build a multi-line address string with conditional components
- Access only the first row of a 2d array
- Doubled elements in result array while scraping HTML content
- Loop over an array of unknown depth
- How to access keys and values of a multidimensional array
- How to union ranges in google spreadsheets
- Validate an array as "empty" if it has no elements or if all elements have no length
- Generate an array of href strings from all <a> tags in an HTML document using Simple HTML DOM Parser
- Extracting JSON response and putting them inside a customize JSON
- Best way to filter an objects's properties for data
- How to loop over json-encoded data?
- Fill array with numbers adding up to a nominated total without exceeding item maximum
- Generate an associative 2d array with incremented column values using a flat array as first level keys
- Put Values in Multidimensional Array keys
- What is the proper way to initialize a fixed length array?
- Need help understanding pointer to a type of n elements in C (int (*p)[10])?
- Create Pivot table and add additional columns from another dataframe
- Algorithm to reverse an array/string only in terms of rotate operations
- Group data from lines of a log file by one column and create subarrays from another column
- When did iterating on the span of an array become faster than iterating on the array itself
- Use values of a flat array as the keys in every row of a 2d array
- Function to clear any value by assigning to it a default constructed value of same type