Pop-up list?

P

picktr

I need to create the following, but don't know how.

=IF(A1="MIA")

TRUE - Show a list of names (range of cells?) to choose from

FALSE - This I already hav
 
D

DNF Karran

Have you considered data validation? if you set the type as list the
put:

=IF(A1="MIA",INDIRECT([range of cells containing values for th
list]),[Action if false])

Dunca
 
P

picktr

Duncan,

I tried that, but it produces #########

I don't know but I may need a macro?

This is a golf league
There are the regular players and the substitute players

On worksheet1, I enter scores for individual players and the
substitute players.

On worksheet2, the scores are transfered from worksheet1,
and further calculations are made.

Under perfect conditions, if no substitute players are playing,
all the scores I entered on worksheet1, transfer to worksheet2,
and all is right with the world, my work is done, all I have to do is
print.

However, If a regular player is "MIA", that transfers to worksheet2 a
"MIA", calculations are NOT made. I have to
then manually go in and replace everything for that row.

I'm trying to cut down the keystrokes.

My Wish:

After I'm finished entering scores on worksheet1, including
any "MIA's". I then go to worksheet2, and the cell where the "MIA" is
shows a drop down list where I can chose the
substitute player, whom I've already entered a score.

I choose a player, hit "enter", their score is now in the box,
the calculation is made, my work is done.

I hope this helps.

To
 
D

DNF Karran

I have attached a file for your ref that does the following- there ar
no macros.

On sheet 1 there is a list of players- a, b, d and MIA and on sheet
there is an IF statement that pulls the details over if there is
value "=IF(ISBLANK(Sheet1!A5),"",Sheet1!A5)"

on the player name column which starts in cell A1 on sheet 2 dat
validation is required. If you set the validation to "=INDIRECT(A1)
you will get an error message. Ignore it and continue.

On sheet 3 ("Sub Players") there is a list of the sub players (z,x,y
and the range that these are in has been named "MIA".

The overall effect is that when a cell in column a has "MIA" in it, yo
will be able to select a value from a drop down list which is populate
from the list of sub players.

Dunca

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=54035
 
P

picktr

Duncan,

I've attached my file, no macros.
After I enter weekly scores in HCPCALC!, including subs,
I then go to WEEKLY! You will see the MIA's,
What I would like is when I have that drop-down list
to choose a sub, when I choose the sub, it replaces the
Name of the Player with the Sub and all of the subs scores.
Only those subs that have played will have numbers in
the cells.

ex: D42 is MIA, drop down list, I choose s-Kim Warren

result: MIA is replaced with s-Kim Warren, and contents of cell
F42,G42,H42 are replaced with contents of cells F69,G69,H69

To

Attachment filename: golfpractice.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=54051
 
D

DNF Karran

I'd tell you about how all over the place my day has been but I wouldn'
want to bore you- suffice to say I have been in about 10 meeting
taking up 95% of the day trying to negotiate a new contract.

I will have nothing left to do tomorrow so I promise I will make tim
for you!

Dunca
 
P

picktr

No problem!

I hope you don't mind, but I borrowed some of your
phrasing on your CV. I'm trying to re-do my resume,
and I liked your OBJECTIVE statement.

To
 
D

DNF Karran

Hi,

The attached should do what you need.

I have used data validation linked to a named range via the INDIREC
function. This enables you to create a data validation that is onl
activated when another cell is a particular value.

There is then a VLOOKUP based on what is selected from the drop dow
list to return details of the player.

Would have been back to you this morning but I was dragged into
"mystery" meeting this am that lasted 3 hours. How am I supposed to ge
some decent xl files done if people keep doing this! Ah well, only
weeks till I move elsewhere and get the 50% pay rise I have alway
dreamed of...

Dunca

Attachment filename: golfpractice.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=54317
 
P

picktr

Thanks Duncan!!!

I'll check it out and get back to you with any questions.

Good luck on the Job Hunt!!!

To
 
P

picktr

Duncan,

What about the GROSS SCORE?
I tried to copy that formula over
to that cell but it gives me
a big red line with an arrow.
Even when I change the reference.

Do I need to change it somewhere else?

Tom
 
Top