Search code examples
google-sheets

Adding row splits data validation Google sheets

In the Test spreadsheet here I created a budget table that has dependent dropdown lists. When you select a category in the Main table, column F, in the Subcategory column (G) another dropdown list appears, based on the Category selection. It works fine and I am happy with it except for when I add a row in the main table, data validation in column G (Subcategory) splits and range reference is not good anymore.

Is there a way to stop Google sheets to split my data validation rule when I add a row? Interestingly, adding a row does not split data validation rule in column F (category), but just in column F (Subcategory).

Thanks in advance!

Solution

  • Added one possible approach to your test sheet:

    I placed the backend dependant-dropdown setup in the same tab instead of a separate one(Preparation tab) & now data validation rules can withstand the insertion/deletion of rows within that range

    =map(F3:F38;lambda(Σ;torow(ifna(filter(Ranges!E2:N;Ranges!E1:N1=Σ));1)))

    enter image description here