Here is a link to a test spreadsheet: https://docs.google.com/spreadsheets/d/10pofQ3TJM1FvBEPdvDq73-dm7hafhNwAbqVkfekofS8/edit#gid=0
The dropdown menus in column C are dependent on the selection in Column B.
To achieve this I have two more tabs in my sheet. The first one called "Dataset" lists the different possible options for each selection.
The "Options" tab transposes the data into rows dependent on the selected model in the production sheet's column B.
The formula for transposing the data is: =TRANSPOSE(INDEX(dataset!$A$2:$F$8,,MATCH(INDIRECT("production!$B" & ROW()),dataset!$A$1:$F$1,0)))
Back in the production tab, Column C grabs the data range in the options sheet with "data validation, dropdown (from a range)". The range in data validation should always match the current row.
So the problem: in my data validation, row 8 has the explicit range =options!A8:8, but if I move row 8 to row 1 it breaks because it is still referencing row 8. The option I had in mind was to use row() formula in my range but this isn't possible.
Is it possible to create a cell which contains the ROW() function, then reference that cell in the data validation range perhaps?
