Assigning values to drop down boxes

[andrewb90]

Hello all,

I am trying to set up a event sheet using drop down boxes to track points and events for each player.

In the event tab:
I have A3:A25 with different events
I have three other sheets with players names on it (A3:A30) labeled Group 1,2,& 3.
Each event uses two players and I would like to (on the event page) select the two players (from 3 lists) and the points that they deserve and have that recorded. If that can fill in a cell on the event sheet, I can use the data to populate a stats printout, I am just unsure how to make these drop down boxes work for me.

Is this doable or should I be looking at another sort of function.
I hope I was pretty clear with what I am looking for.

Thanks,
Andrew

 

[DragonWood]

Actually, this is very doable.

I tested it this way:

I made a workbook with 4 sheets.

Sheet1 = Events
Sheet2 = Group 1
Sheet3 = Group 2
Sheet4 = Group 3

Using the same cells you specified, I made Event 1 through Event 23 on the Events sheet. I made Player 1-1 through Player 1-28 (change the "1" for consecutive group sheets) on the Group 1 sheet.

To make a drop down, you need a "list". After making the Players I selected them all and named each Group_1, Group_2 or Group_3, as was appropriate.

On the Events sheet, I made another list called Group_Select which lists the group names as Group 1, Group 2 and Group 3.

In Cell B1 I used the Data Validation feature and set the cell to reference the Group_Select list.

I now have a drop down menu that can be used to select the group I want to use.

In cell B3 (for the first player) I again used the Data Validation to assign a list, however instead of simply selecting a list, I entered this formula:

=IF($B$1="Group 1",Group_1,IF($B$1="Group 2",Group_2,IF($B$1="Group 3",Group_3)))

Now when I select Group 1 in cell B1 I get the players in the Group_1 list.

You can drag to copy cell B3 down to fill in the rest of the cells with the same formula.

I hope this helps.

 

[andrewb90]

Ok, I'm a bit confused here.

On the Events sheet, I made another list called Group_Select which lists the group names as Group 1, Group 2 and Group 3.

Am I typing that into three different cells and then naming them Group_Select?
Then:

In Cell B1 I used the Data Validation feature and set the cell to reference the Group_Select list.

Do I just assign a data validation to B1 and use custom and pit Group_Select in the formula box?

In cell B3 (for the first player) I again used the Data Validation to assign a list, however instead of simply selecting a list, I entered this formula:

Am I putting data validation on this same cell? I tried that and all it did was change my current validation.
Sorry, data validation is not my strongest area.

Thanks,

Andrew

 

[DragonWood]

Every list you make you need to put the data in separate cells. For example: with the Group_Select list I used cells AG2, AG3 & AG4. Then I select all three at the same time. In the upper left hand corner of the page (to the left of the Formula bar) is a box that has the name of the cell you have selected.
While you have all three cells for your list selected, you click in that box and type Group_Select.
Note: Make sure you type it correctly the first time, you cannot rename a group using the same selected cells.

To assign the list to a cell, you select the cell, (using Office 2007 or 2010) you select the Data tab, then Data Validation. In the window that pops up, under Allow, choose List.
In the Source field put:

=Group_Select

When you select B3 (where you want to have the Player Names listed) follow the same Data Validation steps above except you put the formula I provided in the Source field.

 

[andrewb90]

So I put =Group_Select in B1
then I put =IF($B$1="Group 1",Group_1,IF($B$1="Group 2",Group_2,IF($B$1="Group 3",Group_3)))
in b2
both in the list section of data validation.
When I try to select ok I get this message:
The list source must be a delimited list, or a reference to a single row or column.

 

[DragonWood]

You don't put the formulas in the cells, you select the cell, put the formulas in the Data Validation: Source field.

You also need to make sure the lists of players names are created correctly and named what the formula says. If you named the lists of players something else, you will have to change the formula to match.

I don't know how to include my sample file here, but I'll keep looking into that.

In the meantime, use the formulas I gave you as a guide and read this:

http://www.exceldigest.com/myblog/2009/08/13/data-validation-in-excel-2007-drop-down-list/

I hope that helps.