Home

EXCEL DATA VALIDATION

cwaggoner
New Contributor

This is in regards to using "List" in Data Validation.

I am using a table and in a column of cells I call on list from this table. I am wanting to use another column to select from pre-defined set of 4 parameters, Based on what you choose in Column X, I want Column Y to pull a certain list. Is there way to have my list called in this manner? If not, what is the best method to pull data from the table using the method of selecting a parameter in Column X to view only options available in the list for Column Y.

 

Example:

Column X                                   Column Y

 (options)  I beam                        Brings up a selectable list of items in the table "beam"

                 C channel                    Brings up a selectable list of items in the table "chnl"

                 L angle                        Brings up a selectable list of items in the table "angle"

3 Replies

Hi, The easiest way of doing this would be to use INDIRECTfor data validation for Column Y. Simpler if you could remove the spaces from your options in Column (Ie. Could This be Beam,Angle,Channel)? If that works then just use defined names to name each of the ranges for the options in Y to match the option in X (So you'd have a named range Beam containing the Beam options, a named range Angle containing the angle options, etc). You can then set data validation in the Y to be of type list with the source as =INDIRECT(X2) (Assuming that the first data validation list is in X2). Simple sample attached.

 

If you want to keep this as I Beam, C Channel, L Angle then the formula for data validation in Y needs to be =INDIRECT(RIGHT(X2,LEN(X2)-2)) to remove the first 2 digits (Comes down to defined names not allowing spaces). 

 

 

 

 

Thank you JWR...that gives me a good example of my options on how to accomplish this task. Very helpful.

Your welcome. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies