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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?

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

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))

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

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
2
Views
267
Replies
4
Views
397
Replies
2
Views
255
Replies
20
Views
700
Replies
11
Views
325

1,221,522
Messages
6,160,312
Members
451,637
Latest member
hvp2262

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back