Possible IF or VLookup Formula

**evier8223** · 05-02-2018, 07:14 PM

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.”

See attachments

Lookups.jpg

Sem and Installments.jpg

**6StringJazzer** · 05-02-2018, 09:14 PM

cell H20 (drop down also) defaults to 1

Do you mean that it is set to 1, but the user can still select a different value from the list? Or that it can only be 1?

I suspect that what you describe must be done with macros, but you will need to attach your actual file. Images have limited value.

The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

**evier8223** · 05-04-2018, 12:39 PM

I cant really post the excel workbook because it contains confidential information.

**evier8223** · 05-04-2018, 02:10 PM

Excel Forum.xlsx

See attachment

**JohnTopley** · 05-04-2018, 02:44 PM

One way:

Create named range "Semester" (M14)

Create named range "Num_of_Installments" (M14-M19)

DV for H6

Allow: List

SOURCE: =IF(H6="Semester",semester,Num_of_Installments)

**JohnTopley** · 05-04-2018, 02:53 PM

If you add the following code in sheet1 it will clear H20 so ensuring a selection has to be made;

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("H6")) Is Nothing Then
  If Target.Validation.Type = 3 Then
   Application.EnableEvents = False
   Target.Offset(14, 0).ClearContents  ' H20
  End If
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

to add code, right click on tab, "view code" and copy/paste above.

**evier8223** · 05-04-2018, 12:40 PM

This is what I have so far:

=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 refine this and put this into a formula?

**6StringJazzer** · 05-04-2018, 01:28 PM

You are describing H6, H20, I20, but none of those cells can be determined from the images you posted. It is difficult to get the context.

Just going by your description

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

I can't tell what you mean by this formula. You have two separate IF functions, which is not legal. Can you just describe the logic of what you want, instead of trying to show it in a formula? The best I can do is make some guesses:

=IF(AND(H6="Semester",H20=1),1,IF(AND(OR(H6="Cohort",H6="A La Carte"),H20<>""),I20,"??"))

There is one condition that you still need to cover. An example is H6="Cohort" AND H20 is blank.

There there is

= 1, however if H20 is blank then I20 =0.

Is this formula in cell I20? If so then

=IF(H20="",0,1)

None of this relates to dropdowns or data validation as you mentioned in your first post. You can't put either of these formulas into cells that have data validation with a dropdown list.

**evier8223** · 05-04-2018, 05:39 PM

If H6 equals "Semester" then H20 is changed to 1 and I20 is changed to 1, if 2 and up is chosen in H20 when H6 equals "Semester" than I20 will equal 0

in addition to that if "Cohort" or "A La Carte" is chosen in H6 (instead of "Semester") and a number 1 or above is chosen in H20 then I20 will equal 1

if H20 is left blank then I20 will equal 0

Possible IF or VLookup Formula

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Possible IF or VLookup Formula

Re: Possible IF or VLookup Formula

Re: Possible IF or VLookup Formula

Re: Possible IF or VLookup Formula

Re: Possible IF or VLookup Formula

Re: Possible IF or VLookup Formula

Re: Possible IF or VLookup Formula

Re: Possible IF or VLookup Formula

Re: Possible IF or VLookup Formula

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VLOOKUP formula or VLOOKUP worksheet.function

[SOLVED] Amend VLOOKUP formula to check if a cell is empty and if so perform VLOOKUP

VBA external VLOOKUP instead of VLOOKUP-formula in cell

[SOLVED] Vlookup with column name instead of col_index_name(3rd Section of Vlookup formula)

[SOLVED] VBA code for change vlookup formula to vlookup formula with ISNA

Vlookup referencing a vlookup formula

vlookup-reate a vlookup formula?

Bookmarks

Bookmarks

Posting Permissions