User inputting data

hudsonhacker

New Member
Joined
Mar 10, 2011
Messages
6
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

DippNCope

Board Regular
Joined
May 21, 2009
Messages
77
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
Joined
Jun 17, 2005
Messages
7,764
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,015
Office Version
  1. 365
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
Joined
Mar 10, 2011
Messages
6
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
Joined
Mar 10, 2011
Messages
6
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!
 

Forum statistics

Threads
1,141,142
Messages
5,704,529
Members
421,353
Latest member
jekoxien15

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top