Solved

Excel data validation with criteria

Posted on 2013-05-31
11
540 Views
Last Modified: 2013-06-05
I have an excel list like

Product         Subscriber
ACDSX           John Doe
GFERT           Mary Doe
REVBG          Pat Doe
GFERT           Taylor Doe
ACDSX           Dusty Rhodes
ACDSX           Doug Updegrave

I need to create a data validation that is based upon a product value that is entered into another cell (let's say, A1). For instance, if the cell value for A1 is "ACDSX" I need the data valdiation dropdown in B1 populated with the three subscribers John Doe, Dusty Rhodes and Doug Updegrave.

The subscriber list has several thousand entries. Excel 2010

Thx
0
Comment
Question by:fredjonzeTwo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 39211917
I don't know how to handle that directly in Data Validation unless you have a cross-reference table somewhere (i.e. a grid that shows, for example, that type ACDSX consists of name1,name2,name3).  What I see now is that it will require searching through the column to find the matches, then bring back all corresponding values.

I could build you a macro to run which will apply the data validation into any given cell if you'd like to go in that direction.
Meanwhile, let's see if any other experts have some ideas on other ways to handle this.
0
 

Author Comment

by:fredjonzeTwo
ID: 39211938
I can also write a macro...just hoping there is a way to do this dynamically.

Thx,
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39211940
If you have all of the codes and related items set up something like this:

ACDSX      GFERT      REVBG
abc      123      !@#
def      234      @#$
ghi      345      #$%

And each group named (e.g. all of column below ACDSX named as, for example, ACDSX) then you can use this data validation:

List:
=INDIRECT(A2)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39211992
Hi,

Please review the attached workbook.

BFN,

fp.

PS. For reference: [ http://www.cpearson.com/excel/NoBlanks.aspx ].
Q-28144487.xls
0
 

Author Comment

by:fredjonzeTwo
ID: 39212052
I reviewed the workbook and took the following steps

- Set up the formula for Selected
- Created the Selected named range
- Created the ValidationList named range (Validation List Column)
- Created the DataValidation named range and pointed it to the first cell of ValidationList
- Selected the ValidationList cell range, pasted in the array formula, and pressed ctrl+enter

The result is that I get a name in the first cell of the validation list column, but I get #NA for all the other cells. Is there a step I missed?

Real close!

Thx,
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39212059
See if this helps.

Note that column C has the dropdown list and the validation is as I indicated above: =INDIRECT(A2), etc.
DynamicDataValidationList.xlsx
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39212061
I reviewed the workbook and took the following steps

- Set up the formula for Selected
- Created the Selected named range
- Created the ValidationList named range (Validation List Column)
- Created the DataValidation named range and pointed it to the first cell of ValidationList
- Selected the ValidationList cell range, pasted in the array formula, and pressed ctrl+enter

The result is that I get a name in the first cell of the validation list column, but I get #NA for all the other cells. Is there a step I missed?

Real close!

Thx,

Presumably you saw that what I had provided worked in situ.

Can you attach your example?
0
 

Author Comment

by:fredjonzeTwo
ID: 39212073
I can't send my original as it is immense, but here is a mock up where I tried to re-create the example. In this, I get a #num error...and I followed the same steps.

Thx,

Jim
MockUp.xlsx
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 325 total points
ID: 39212120
Hi Jim,

Steps to resolve your issue:

a) Select cell [G2].
b) Copy the formula in that cell to the Windows Clipboard.
c) Clear cells [G2:G7].
d) Paste the formula in the Windows Clipboard back to [G2] & use [CTRL]+[SHIFT]+[ENTER].
e) Copy cell [G2] down all column [G] to row 7 (i.e. [G2] copied to [G3:G7]).

Also note: Cell [B2] should be using the Data Validation range from the (Hidden) Named Range "DataValidation".

You have not changed the worksheet within that (hidden) Named Range from my original worksheet name of [Q_28144487].

In my example file (above), the blank rows within the range [G2:G7] are not shown in the drop-down list in cell [B2].

In your workbook they are shown due to the fact that the (hidden) Named Range has not been changed to match the worksheet name [Sheet1].

You could, of course, name the worksheet [Q_28144487] & change the Data Validation range used in cell [B2].

BFN,

fp.
0
 

Author Comment

by:fredjonzeTwo
ID: 39222537
Thanks for the explanations and tutorial...I was able to get it working just fine! Most of my error was in the array...I thought I needed to paste the array formula into the range of cells instead of just G2.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39222639
You're are very welcome.

Good luck with the rest of your project.
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

688 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