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
7
Medium Priority
?
169 Views
Last Modified: 2012-05-11
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
Comment
Question by:pamhiser
  • 3
  • 2
6 Comments
 
LVL 33

Accepted Solution

by:
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:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_4454-Cascading-Validation-Lists.html

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

jppinto
CascadingValidationLists-v2.xlsx
0
 
LVL 1

Author Comment

by:pamhiser
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

by:jppinto
ID: 35485287
Are they on MS Access?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:pamhiser
ID: 35485675
SQL
0
 
LVL 1

Author Comment

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

Expert Comment

by:Tracy
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question