Solved

Excel data validation with criteria

Posted on 2013-05-31
11
478 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
  • 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

758 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now