Solved

Excel data validation with criteria

Posted on 2013-05-31
11
494 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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 Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

910 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

26 Experts available now in Live!

Get 1:1 Help Now