Possible IF or VLookup Formula

[Sawduster]

I am trying to create a formula when cell H6 (validation criteria – drop down list) – Semester (text) is chosen another cell H20 (drop down also) defaults to 1 and locks the cell (H20) so that other options in the list are not available when H6 (Semester) is chosen from the drop-down list.

How would I put this in a formula? Would I use a VLookup formula or an IF statement? Would I use conditional formatting also? The data validation is pulled from another sheet in the workbook labeled “Lookups.”

 

[JonXL]

If you really want to lock the cells down and change H20 when H6 is selected, you'll need VBA.

If you just want to limit the options available in H20, you can do that with the formulae and data validation you suggest.

1. Click the cell with "1" and name it "One"
2. On H20 add the following data validation: List, Source: =IF(H6="Semester",One,Lookups)

 

[Sawduster]

A slight revision:

Im trying to get it to do this:

if semester is chosen in cell H6 and the number 1 is chosen in cell H20 then a number 1 is populated in cell I20, however, anything greater than a 1 when semester is chosen in H6 triggers a 0 in cell I20.

Heres what I have:

=IF(AND(H6="Semester",H20>"1"),I20,0*(IF(ISBLANK(H20),0,1)))

It doesnt work though

What would be the formula for this?

 

[AhoyNC]

Is this what you want?

[TABLE]
<colgroup><col width="283"></colgroup><tbody>[TR]
   [TD="width: 283"]=IF(AND(H6="Semester",H20=1),1,0)
[/TD]
 [/TR]
</tbody>[/TABLE]

 

[Sawduster]

Thanks

Yes that works however can this be expanded?

=IF(AND(H6="Semester",H20=1),1,0),IF(H6="Cohort"or H6=“A La Carte”, and H20 is not blank then I20 = 1, however if H20 is blank then I20 =0.

How would you put this into a formula?

 

[AhoyNC]

Try:

[TABLE="width: 595"]
<tbody>[TR]
[TD="width: 595"]=IF(AND(H6="Semester",H20=1),1,IF(AND(OR(H6="A La Carte",H6="Cohort"),H20<>""),1,0))
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sawduster]

Thanks this worked