Data Validated Cells' Lists reduce but only in one column

[batador]

http://www.excelforum.com/excel-form...selection.html
This post explains what I am wanting to do minus one facet, I have data validated cells spanning 3 columns as opposed to just a single column. I have been playing with the index formula and can't seem to write it correctly. How can I write this formula so that it will look at all three columns?

Please Login or Register  to view this content.

The range highlighted in red is the range that contains my data validated cells. I would also like for the ranges G10:G38 and J7:J40 to contain the data validation and the list contained therein to reduce with each selection of a cell. Any and all help is appreciated as my little pebble is fried.

[JBeaucaire]

My technique is:

1) Create a list of ALL the options (column A)

2) Create a numbering system next to that list that watches all the rows/columns/cells where the list will be used and enters a unique number in each cell ONLY when the watched range does not have the value in that row (column B)

3) Use an INDEX formula to then create a secondary list of the items from column A using the unique numbers in column B

4) Use the second list as the source of the DV for the watched range


This technique would work over any number of rows/columns to create a single diminishing list.

You can post a workbook if you wish and I will show you how I would set that up.

[batador]

Team Schedule.xlsx

Hope I was able to insert the file. You can see what I've got going on and apologies on the sloppiness from my previous attempts.

[JBeaucaire]

Column O - master list of all names
Column N - an indexing formula that is watching C10:G38 for values that match the column O list, if no match, it puts a unique number in this column
Column P - the List2 that is created from ONLY the values in column O where there is a number in column N, so this list shrinks itself

List2 is then used as the DV source in the cells in C10:G38.



From this you can expand as you wish. If you were to add cells in column J for instance to also use List2, you would adjust the formula in column N to also include those cells.

[batador]

Mr. Beaucaire that was exactly what I was looking for. Thank you very much. I have finally gotten back around to playing with this and it works great. Appreciate your help.