Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Excel Form: Is there a better way (VB?) to return multiple results as a list for selection?

Posted on 2011-04-28
Medium Priority
169 Views
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.
=IF(ISERROR(INDEX(\$A\$1:\$C\$64,SMALL(IF(\$A\$1:\$A\$64=MARF!\$F\$16,ROW(\$A\$1:\$A\$64)),ROW(1:1)),3)),"",INDEX(\$A\$1:\$C\$64,SMALL(IF(\$A\$1:\$A\$64=MARF!\$F\$16,ROW(\$A\$1:\$A\$64)),ROW(1:1)),3))

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.

0
Question by:pamhiser
• 3
• 2

LVL 33

Accepted Solution

jppinto earned 2000 total points
ID: 35484831
What you want to do is to build "Cascading Validation Lists"...you can check my article here on EE to see how you can do it:

I've attached a sample file for you to see how it work.

jppinto
0

LVL 1

Author Comment

ID: 35485276
I like this option with the exception that every time we add a new test, I will have to update the form manually with the new test and variations.
I do have access to the data source and can import the updated lists of variations and tests if needed, does this open up more options?
0

LVL 33

Expert Comment

ID: 35485287
Are they on MS Access?
0

LVL 1

Author Comment

ID: 35485675
SQL
0

LVL 1

Author Comment

ID: 35494964
Are the cascading validation lists my only options at this point...?
0

LVL 24

Expert Comment

ID: 35877437
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦