User inputting data

[hudsonhacker]

Okay, have had some great answers on this board helping me get better at designing spreadsheets. I have three questions today.

1) I have a spreadsheet where users need to enter data into a cell. I need the user to enter either the letter "h" or the letter "v". How do I ensure that nothing else can be entered into that cell?

2) Same as above, but user can enter 4 different values, the numbers 4,5,6,7. How do I prevent them from entering any other value? (Pretty sure it is the same answer as above)

3) I have a cell that is dependant on input in another cell from the user. Prior to the user inputting, the destination cell reads "#NUM!" Is it possible for the destination cell to just be blank if the user info has not yet been supplied?
User enters info into A1, based on that input, I have a name in C1. I want C1 to be blank until the info in A1 is inputted. The formula in C1 is a nested If function:
=IF(D33=1, $D$5,IF(D33=2,$D$8,IF(D33=3,$D$11,IF(D33=4,$D$14, IF(D33=5,$D$13,IF(D33=6,$D$10,IF(D33=7,$D$7,IF(D33=8,$D$4))))))))

Hmm, last week I couldn't even figure out the above IF Function, now I am trying to use it to drive the rest of the spreadsheet. This ain't that tough!

Thanks in Advance

Paul

 

[DippNCope]

=IF(D33=1, $D$5,IF(D33=2,$D$8,IF(D33=3,$D$11,IF(D33=4,$D$14, IF(D33=5,$D$13,IF(D33=6,$D$10,IF(D33=7,$D$7,IF(D33=8,$D$4[COLOR=Red],""[/COLOR]))))))))

I added ,"" to you code that will leave cell blank
For your other option can you use 2 data validation lists so the user has a drop down selection of h or v in one and 4,5,6,7 in the other?

 

[njimack]

1 and 2 - Data>Validation>Custom
=OR(A1="h",A1="v")
=AND(A1=INT(A1),A1>=4,A1<=7)

3 =IF(A1,INDIRECT(ADDRESS(CHOOSE(D33,5,8,11,14,13,20,23,26),4)))

 

[Oaktree]

FWIW, this is a good time for the CHOOSE function.

C1=IF(A1="","",CHOOSE(D33,$D$5,$D$8,$D$11,$D$14,$D$13,$D$10,$D$7,$D$4))

 

[hudsonhacker]

=IF(D33=1, $D$5,IF(D33=2,$D$8,IF(D33=3,$D$11,IF(D33=4,$D$14, IF(D33=5,$D$13,IF(D33=6,$D$10,IF(D33=7,$D$7,IF(D33=8,$D$4[COLOR=Red],""[/COLOR]))))))))

I added ,"" to you code that will leave cell blank
For your other option can you use 2 data validation lists so the user has a drop down selection of h or v in one and 4,5,6,7 in the other?

Simplicity itself! I keep overthinking the complexity!

Thanks

 

[hudsonhacker]

=IF(D33=1, $D$5,IF(D33=2,$D$8,IF(D33=3,$D$11,IF(D33=4,$D$14, IF(D33=5,$D$13,IF(D33=6,$D$10,IF(D33=7,$D$7,IF(D33=8,$D$4[COLOR=Red],""[/COLOR]))))))))

I added ,"" to you code that will leave cell blank
For your other option can you use 2 data validation lists so the user has a drop down selection of h or v in one and 4,5,6,7 in the other?

Simplicity itself! I keep overthinking the complexity!

Thanks everyone, hopefully my skill set improves to the point where I stop asking dumb questions!