| 0 |
Subject: My Own excel question (drop-down boxes)
Posted by: J
- Leader [049346417] Mon, Mar 14, 2005, 08:53
So, I'm trying to make a user friendly NCAA bracket in excel.
I have the main brackets setup in a spreadsheet.
Lets say I've got "Illinois" in cell B3 and "Fairleigh Dickinson" in cell B5.
In Cell C4 I want to place a drop down box where you can choose from either of those two cells.
Then eventually after everything is selected, I need a way to extract all the picks.
Anyone know how to do this???? |
| 1 | JTSERB
ID: 40135411
Mon, Mar 14, 2005, 15:03
|
If someone knows the answer to this it would also be very helpful to me. :)
|
| 2 | JTSERB
ID: 40135411
Mon, Mar 14, 2005, 15:05
|
The function I use to pull data from a specific cell is the "concatenate" function. I used it when I was running a playoff fantasy league and wanted to make sure someone didnt use the same player twice. I don't know if it would be efficient enough for what you are looking for though J.
|
| 3 | J
Leader
ID: 049346417
Mon, Mar 14, 2005, 15:10
|
nah, I'm looking to use the drop-down boxes like on this page, "Index of Forum Topics" where the 2 options would be "Illinois & Fairleigh Dickinson".
I've used your formula before, its the same though as just using the "&" symbol (=A2&D2)
|
| 4 | JTSERB
ID: 40135411
Mon, Mar 14, 2005, 15:26
|
Here ya go J
5. Add a Drop-Down List
Inserting a drop-down list to a cell is a slick trick that can add a touch of professionalism to your worksheets--and also ensure that erroneous data is not entered into the cell. And best of all: Macros are not required!
Assume that you have an input cell in which the user is supposed to enter a month name: January, February, and so on. Here's how to add a drop-down list to that cell to make data entry a breeze--and save some keystrokes.
1. Enter the items for your drop-down list into a list on the worksheet, one item per cell. In this example, I'll assume that the month names start in cell E1 and extend down to E12, but they can be in any out-of-the-way location on the worksheet. In Excel terminology, a rectangular group of cells (such as E1 to E12) is called a range.
2. Select the cell that will contain the drop-down list. If you'd like more than one cell to display the same list, just select them all now rather than setting them up one at a time. (Click and drag to select a range; hold down Ctrl while you click to select non-adjacent cells.)
3. Choose Data, Validation to display the Data Validation dialog box.
4. Click the Settings tab.
5. In the Allow field, select List.
6. In the Source field, specify the range that contains the list items. In this example, the items are in cells E1 to E12, so type =E1:E12 into the field.
7. Click OK.
Click to view full-size image.
After performing these steps, you'll see a drop-down arrow whenever any of the drop-down cells you just defined is "active" (that is, selected and awaiting input). Click the arrow and choose a month from the list. If you try to type something else into the cell, you'll get scolded in the form of a pop-up message.
If you'd like to provide your own wording for invalid entries, use the Error Alert tab in the Data Validation dialog box, and enter your own text in the 'Error message' field.
If your list of items is relatively small, you can bypass Step 1 and enter the list items directly into the Source field in Step 6. Just separate each list item with a comma.
Heres a link
|
| 5 | C.SuperFreak
ID: 261452514
Mon, Mar 14, 2005, 15:37
|
here try this:
Step 1:
Select View/Toolbars/
view the "control toolbox"
Step 2:
From the toolbox
Select the Combo Box
create combo box in cell c4
Step 3:
Edit the propertes
ListFillRange: B3:b5
LinkedCell: H3
Test it out. You might want to consider not having an empty cell.
|
| 6 | youngroman
ID: 298482214
Mon, Mar 14, 2005, 15:59
|
the key is post 4. try this:
B3 is team1
B5 is team2
B7 is team3
B9 is team4
B11 is =B3
B12 is =B5
B13 is =B7
B14 is =B9
C11 is =C4
C12 is =C8
select C4 and do a data->validation, select List and B11:B12 as source
select C8 and do the same with B13:B14
select D6 and do the same with C11:C12
hide rows 11 to 14
|
| 7 | JTSERB
ID: 40135411
Mon, Mar 14, 2005, 16:33
|
Thanks alot youngroman. I couldnt figure out how to get the blank spaces out of the choices. Now I got it :)
|
| 8 | JTSERB
ID: 40135411
Tue, Mar 15, 2005, 22:41
|
Hey, J ever figure out how to pull the data?
|
| 9 | J
Leader
ID: 049346417
Tue, Mar 15, 2005, 22:46
|
yeah, but I cheated off some other spreadsheet. The end result looks pretty good though!!! :)
Thanks for the posts guys
|
| 10 | FRICK
ID: 3410101718
Tue, Mar 15, 2005, 22:49
|
How do you pull the data. I may have a project at work where this would be useful.
|
| 11 | J
Leader
ID: 049346417
Tue, Mar 15, 2005, 23:55
|
here's what my spreadsheet has...and i'd be happy to share a copy of it if you want...
Lets say you have a bracket for the Illinois vs Fairleigh Dickinson game. I created another sheet where:
A2 = blank
A3 = Illinois
A4 = Fairleigh Dickinson
so, you create the drop down box, go to format control and use A2:A4 as your "input range" and whatever cell the dropdown box is in as your "cell link". I used 3 drop down lines (I wanted each game to be blank originally)
What it does is returns "1" if the dropdown box is blank, "2" if they pick Illinois, "3" for FDU.
I did the same thing for the Texas-Nevada game below it. then I used if statements - =IF(Bracket!C4=2,Secret!A3,Secret!A4)
=IF(Bracket!C8=2,Secret!A6,Secret!A7)
I use those to make the next dropdown box for the winner of both games.
Make sense???
|
| 12 | C.SuperFreak
ID: 20249320
Wed, Mar 16, 2005, 02:27
|
send me your email address and i'll forward you a sample template.
|
| 13 | JTSERB
ID: 40135411
Wed, Mar 16, 2005, 10:56
|
If someone could send me a template of how to pull the data I would greatly appreciate it.
Thanks
|
| 14 | C.SuperFreak
ID: 20249320
Wed, Mar 16, 2005, 11:19
|
JT, spreadsheet sent.
|
| 15 | FRICK
ID: 3410101718
Thu, Mar 17, 2005, 22:21
|
JT, can you send me a copy of the example also.
Thank you
|
| 16 | JTSERB
ID: 40135411
Thu, Mar 17, 2005, 22:56
|
File sent Frick
|
|
|
|
Post a reply to this message: (But first, how about checking out this sponsor?)
|