Populating a Data Validation List based on...

Basically, I want to populate a Data Validation based on values in another cell on another worksheet--but, I want to populate it with the values stored in the cell right next to the cell.


http://i32.photobucket.com/albums/d3...n/untitled.jpg

would be an example.


What I want my Data Validation List to do is to look at column B and wherever it sees a certain color--for example, red, I want it to put the corresponding value in column A in the list.

Any ideas? I'm fairly new to excel, but not that new. I was thinking of using a VLOOKUP function but had no idea how to go about it in a list.

Data validation lists shouldn't have to work that hard. Best to solve your problem on the worksheet itself, then have the validation list used the solved column of values

Here's an example of how I would do it. A helper column creates an indexable "key", the that key is parsed into columns for the validation lists, to use. You can change anything you want in columns A and B and the lists will reassert themselves.

Named ranges are used for rows 2-20 in the blue section, each column named for the color. For instance, highlight H2:H20 and you will see the name "Red" appear in the Name Box (to the left of the formula bar).

Anyway, once it's all working, you could hide all those helper columns, or even have them all a different (maybe hidden) worksheet.

OK, i'll try again.


Basically, I want my Data Validation List on a different worksheet(let's call it Sheet2) to look at Column B in Sheet1(The one shown in the picture) and wherever it sees "Red" to populate the list with the corresponding Value in Column A.

This isn't what i'm using this for, it's just a simplified example.


For example, from the picture, my list should contain the values 1,4,7,9 and 18.

Thanks for the response, JBeaucaire. Perfect

That's what I've shown you how to do. See in column H? Those are the values collected there.

There's no need to simplify, and doing so frequently results in unusable answers. The workbook I've posted offers a completely usable solution. You need only set your validation list on Sheet1 to =Red to get the values in column H since that is what I 'named' those cells.

You can expand this from there.

This technique could also be used for DependentDropLists that gets a list based on what is chosen in a previous cell. For instance, on my sample sheet you could have a cell that must have a color chosen FIRST, then the cell next to it would THEN populate with the correct values associated with the first choice.

Take a look at this updated version, notice the values column E are missing, make a choice there BEFORE you check the list in column F.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]