?
Solved

Excel data validation with criteria

Posted on 2013-05-31
11
Medium Priority
?
546 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
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 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 1300 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

649 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