0

The aim: For my work, I would like to create a google spreadsheet where we can document our errors in processing the order transactions. For this I need two drop-down menus; the first shows the superordinate error category, the second then the exact error types that are assigned to the category. I want these dependent drop-down menus to repeat in each row and function in each row independently of the rest.

My procedure: I have created two spreadsheets. On the first I have created the actual table for the errors, on the second I have written the error categories and under these the error types, which I have defined as a common area and named with the name of the associated error category. I created the first drop-down menu via "Data validation" and selected the error categories on the second spreadsheet so that they are displayed when you click on the first drop-down menu. To create the second, dependent drop-down menu, I first inserted a formula into the second spreadsheet.

=IF('Source of improvement'!D2=A1;INDIRECT("Order_processing"),IF('Source of improvement'!D2=B1;INDIRECT("Packing"),IF('Source of improvement'! D2=C1,INDIRECT("Customer_service_requests"),IF('Source of improvement'!D2=D1,INDIREKT("Inventory_and_system"),IF('Source of improvement'!D2=E1,INDIREKT("Shipping"),INDIREKT("Pick_up"))))))

With this formula, when I select a defect category on my second spreadsheet, all the information from its defined defect area is always displayed, i.e. the concrete defects.
Then I created the second drop-down menu on the first spreadsheet and specified that it should always select its elements from the range in which the errors are specified on the second spreadsheet, depending on the selection of the error category in the first drop-down menu.

The problem: This works great for just one row, but how can I apply this formula to the whole column without having to recreate it for each row and adjust the specifications? Unfortunately, I haven't found any useful information on the Internet yet and would be very grateful for help!

Improve this question
1

1 Answer 1

Reset to default
0

Drop-down lists can be made dynamic by setting their options to refer to a range that is filled with a spreadsheet formula, but it is usually easier to define the list programmatically with something like the dependentDropDownLists_ script.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.