Need to Create a dependent list using data validation row headings instead of columns

[almostanexpert]

Hello All,

I have scoured the google for an answer and I can't seem to find out. I have read many posts to create data validation lists and then dependent lists but those always take into account that the information is in nice columns. How about when your source data is arranged by rows. Quick example:

Col AA Col C
P1000 CST
P1000 PHX
P1000 SAT
P2000 SAT
C5000 ADD
C5000 NYC
XS400 SAT
XS400 PHX
XS400 COS
J3500 ADD
There are around 500 additional records

What I would like to do is after picking P1000 I will see in a dependent list CST, PHX, SAT and be able to choose one. I did come across one article that used Index Match and Offset but for the life of me I cannot find it again. I turn to your great minds for assistance.

Any guidance is greatly appreciated.

Thank you.

 

[Zack Barresse]

This is an odd structure. It's quite possible, but I wouldn't say optimal. For this solution we're assuming a few things here. You already have a list of unique values for your primary list. In the named ranges below, I copied your list above to columns A and B of Sheet1. Lastly, this assumes your dependent list will be sorted by the primary dependents. It will return incorrect results if it is not. I'm not sure what "Col AA" and "Col C" are, if those are headers, or where that data resides, you weren't very clear with that. You can adjust accordingly though.

You'll need to create 4 named ranges.

"PrimaryChoice"
Refers to the cell in which you choose from your primary list of [unique] items, i.e. P1000, P2000, C5000, etc. This part is a bit tricky, so pay attention. It's a relative referenced named range. This means it's completely dependent on what cell is currently selected versus what you type for the cell reference. For my example, I have the primary list of unique values in a drop down in cell D1. I then select cell E1, and hit CTRL + F3 (or on the ribbon, click Formulas, Name Manager). Click "New...", enter "PrimaryChoice" as the Name, and the Refers to as:
=Sheet1!D1

The important takeaway here is that you've selected E1, with a relative reference of D1. This means that named range will always move and be one cell to the left of the active cell. My assumption was you wanted to dependent data validation to be to the right of the primary item chosen. As an example, if you wanted the dependent cell to be under the primary choice, you would have selected cell D2 when creating the above named range. Make sense?

"SecondaryFirst"
Refers to:
=INDEX(Sheet1!$B:$B,MATCH(PrimaryChoice,Sheet1!$A:$A,0))
Again, column A contains your primary list (P1000, etc.), and column B contains dependent values (CST, PHX, etc.).

"SecondaryLast"
Refers to:
=INDEX(Sheet1!$B:$B,MATCH(PrimaryChoice,Sheet1!$A:$A,1))

"SecondaryList"
Refers to:
=SecondaryFirst:SecondaryLast
This is an intersect range which should be your dependent list.

Then whenever you want to have a cell show a dependent list, just use the list data validation named range of =SecondaryList.

HTH

 

[almostanexpert]

Hello Zack, first I would like to thank you for time. I understand that the structure is not ideal but it's the one I have to work with. My source file is in a network drive and I will be referencing, it gets updated by various users. So I am unable to restructure it to comply with the standard setup for data validation and dependent lists. Yes those are the columns where the data is located. I am going to adapt your work to my situation. Than you again

 

[almostanexpert]

Hello Zack your solution worked just the way you explained. However when I tried adapt it to my source information I would get an error message. I will try tomorrow an post an updare.

 

[almostanexpert]

Hello Zack and All,

I have the previously mentioned solution working to a degree. The only thing that I am having trouble is with the data being listed for my dependent list. When I choose a primary with no multiple secondary, I still get all of the options available for other Primary's, so it's not refreshing the secondary list based on the Primary. If anyone else has tried this solution I would greatly appreciate any input.

Thank you.

 

[Zack Barresse]

I can't replicate your findings. Can you post specifications to what you have in your spreadsheet? Or post your spreadsheet to the cloud and provide a link?

 

[almostanexpert]

No worries Zack I got it to work. It turns out that my data was being read as text so I went ahead and forced it to be a number and now it is working. I greatly appreciate our solution. I am now going to add the Offset function to it can auto add any edits that are done to my source document.

Thank you for your time and solution.

 

[Zack Barresse]

Ah. Very glad you got it working! Thanks for posting what you did.