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
 

Some videos you may like

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
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
7,984
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!
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top