Is there a way to create a dynamic list for use with data validation? For
example: if I have a list of 10 players. In A1, the list will display all
players in the list. However, B1 will show only 9 players from the list
(original 10 minus the one selected in A1).

Thank you.

See this:

http://contextures.com/xlDataVal03.html

--
Biff
Microsoft Excel MVP


"ghopper01" wrote in message
...
Is there a way to create a dynamic list for use with data validation? For
example: if I have a list of 10 players. In A1, the list will display all
players in the list. However, B1 will show only 9 players from the list
(original 10 minus the one selected in A1).

Thank you.

You asked for it, you got it :), and this is NON-VBA solution. There may be
better solutions out there....but I made this one a while back, and it works
for me....that being said, I will give you the essentials.
First, create a separate tab, this will be your list, my example, the tab is
named Data. This tab is strictly for the list that you will be using for DV
on your main tab which in my example is called Main.
First, I need to define some ranges, name, Names, Names2, Names 3. Go to
Insert|Name|Define to define as follows:
Names: =Data!$A$2:INDEX(Data!$A$2:$A$100,COUNTA(Data!$A$2 :$A$100))
Names2: =Data!$C$2:INDEX(Data!$C$2:$C$100,COUNTA(Data!$C$2 :$C$100))
Names3:
=OFFSET(Data!$E$2,0,0,COUNTA(Data!$E$2:$E$100)-COUNTBLANK(Data!$E$2:$E$100),1)
On Data tab, I still have row 1 empty, and starting in A2, going down, I
type my full actual list of people.
In cell B2: =IF(ROW()=2,2,B1+1)
In cell C2: =IF(COUNTIF(Main!$A$1:$Z$1,A2)=1,"",A2)
In cell D2(array formula, commit formula by pressing CTRL+ALT+ENTER after
typing):
=IF(SUMPRODUCT(--($A$2:$A$100=$C$2:$C$100))<ROW()-1,"",SMALL(IF(Names=Names2,ROW($C$2:$C$100)),ROW(1 :$100)))
In cell E2: =IF(D2="","",VLOOKUP(D2,$B$2:$C$100,2,FALSE))

Go to your main tab, go to cell A1, menu Data|Validation
Allow: List, Source: =Names3
Copy to the right across row 1.

Currently, I have it set up to cover up to 26 columns across the top of row
1 on the Main tab, you can have more, but be sure to expand the formula in
cell C2 on your Data tab (and of course be sure to copy the validation over).
I have it set up to get up to row 100 on the Data tab with individual names,
but if you need more, be sure to expand the formula ranges above that go
through $100.

Hope this helps, and if it does, be sure to check it!

--
John C


"ghopper01" wrote:

Is there a way to create a dynamic list for use with data validation? For
example: if I have a list of 10 players. In A1, the list will display all
players in the list. However, B1 will show only 9 players from the list
(original 10 minus the one selected in A1).

Thank you.

Forgot to mention, copy cells B2 through E2 down to row 100 on the Data tab,
or further as needed.
--
John C


"John C" wrote:

You asked for it, you got it :), and this is NON-VBA solution. There may be
better solutions out there....but I made this one a while back, and it works
for me....that being said, I will give you the essentials.
First, create a separate tab, this will be your list, my example, the tab is
named Data. This tab is strictly for the list that you will be using for DV
on your main tab which in my example is called Main.
First, I need to define some ranges, name, Names, Names2, Names 3. Go to
Insert|Name|Define to define as follows:
Names: =Data!$A$2:INDEX(Data!$A$2:$A$100,COUNTA(Data!$A$2 :$A$100))
Names2: =Data!$C$2:INDEX(Data!$C$2:$C$100,COUNTA(Data!$C$2 :$C$100))
Names3:
=OFFSET(Data!$E$2,0,0,COUNTA(Data!$E$2:$E$100)-COUNTBLANK(Data!$E$2:$E$100),1)
On Data tab, I still have row 1 empty, and starting in A2, going down, I
type my full actual list of people.
In cell B2: =IF(ROW()=2,2,B1+1)
In cell C2: =IF(COUNTIF(Main!$A$1:$Z$1,A2)=1,"",A2)
In cell D2(array formula, commit formula by pressing CTRL+ALT+ENTER after
typing):
=IF(SUMPRODUCT(--($A$2:$A$100=$C$2:$C$100))<ROW()-1,"",SMALL(IF(Names=Names2,ROW($C$2:$C$100)),ROW(1 :$100)))
In cell E2: =IF(D2="","",VLOOKUP(D2,$B$2:$C$100,2,FALSE))

Go to your main tab, go to cell A1, menu Data|Validation
Allow: List, Source: =Names3
Copy to the right across row 1.

Currently, I have it set up to cover up to 26 columns across the top of row
1 on the Main tab, you can have more, but be sure to expand the formula in
cell C2 on your Data tab (and of course be sure to copy the validation over).
I have it set up to get up to row 100 on the Data tab with individual names,
but if you need more, be sure to expand the formula ranges above that go
through $100.

Hope this helps, and if it does, be sure to check it!

--
John C


"ghopper01" wrote:

Is there a way to create a dynamic list for use with data validation? For
example: if I have a list of 10 players. In A1, the list will display all
players in the list. However, B1 will show only 9 players from the list
(original 10 minus the one selected in A1).

Thank you.