# User inputting data

#### hudsonhacker

##### New Member
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!

Paul

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### DippNCope

##### Board Regular
Code:
``=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

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

#### Oaktree

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

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

#### hudsonhacker

##### New Member
Code:
``=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

##### New Member
Code:
``=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!

Replies
1
Views
57
Replies
13
Views
90
Replies
2
Views
43
Replies
5
Views
51
Replies
0
Views
36

1,109,368
Messages
5,528,274
Members
409,813
Latest member
robyrux

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...