Excel Form: Is there a better way (VB?) to return multiple results as a list for selection?
Posted on 2011-04-28
I am creating a form in MS Excel to request testing of samples.
People will be selecting different tests for their samples.
Some of these tests have additional test variation choices that the customer must indicate so that we run the test the right way.
I need a way for only the test variations appropriate to the selected test to show up in a drop down list for the customer to pick from and my current solution is cumbersome.
I am using a cell Data Validation to reference a list of variations. This list is updating based on the customer's test selection and the use of INDEX/SMALL formulas. Ex.
But I have to have a column of INDEX/SMALL formulas created per test selection cell per sample. So Sample 1 may have Test1, test2, test 3 and require 3 columns. Sample 2 may have Test1, test2, test 3 and require 3 different columns. This would be 6 columns test variations to pick from for the 6 tests. They can have easily 30-40 samples and 5-7 tests each with room for more. Let me know if more information is needed.