Data validation list with if statement to show the content of another cell without needing the dropdown

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
84
In field G9, I've got a data validation list with a source of =IF(F9="Yes",E9,$U$8:$U$19). $U$8:$U$19 is where my list is for the dropdown. If F9=Yes, I want to show the content of E9 in cell G9 and if F9<>"Yes", then I want to provide the dropdown list in G9 to choose from. This is working...sort of. G9 is showing the content of E9 if F9="Yes" but I still have to select it from a dropdown. I want G9 to just show E9 if F9="Yes" without needing to select it from a dropdown. Is this possible?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Not really, at least not how you've described it. If you want E9 to show in G9, you'd have to have a formula in G9, that looks at F9. But if you have DV in G9, any time you select something from the DV list, it will overwrite the formula.

You could potentially do it with VBA. Set up your DV in the G9 cell, with just U8:U19 as the list. Then set up a Change event handler that looks at the F9 cell. If the value is "Yes", it updates G9 to what's in E9. If not "Yes", it just clears G9. Something like this:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, [F9]) Is Nothing Then Exit Sub
    [G9].Value = IIf(Target.Value = "Yes", [E9].Value, "")
    
End Sub
 
Upvote 0
Ok that worked for a single row of data. My data is in a table so can the VBA be modified easily to work for multiple rows?
 
Upvote 0
Try:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("F9:F20")) Is Nothing Then Exit Sub
    Target.Offset(, 1).Value = IIf(LCase(Target.Value) = "yes", Target.Offset(, -1).Value, "")
    
End Sub

Change the range in the first line to reflect the actual number of rows you want to check. If this is an actual Excel table, where the size can dynamically change, let me know and I'll figure out how to incorporate that.
 
Upvote 0
You could just monitor the whole F column:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Target.Offset(, 1).Value = IIf(LCase(Target.Value) = "yes", Target.Offset(, -1).Value, "")
    
End Sub

I don't think that would hurt anything. I'm not sure how else to know what rows to monitor. If there are headers, you could skip those with

Range("F8:F1000000")
 
Upvote 0
I'm still working on it but so far, I can't get it to work. It may be because my "Yes" in column F is a formula examining other fields to determine "Yes" or NULL.
 
Upvote 0
If it's a formula, then the Worksheet_Change macro wouldn't necessarily fire. You could use a Worksheet_Calculate macro instead, which kicks off whenever a formula recalculates, but then we don't have the location of the cell in column F that might have changed. We'd need to basically scan column F looking for "Yes" values.

Something like this maybe:

VBA Code:
Private Sub Worksheet_Calculate()
Dim lr As Long, arr1 As Variant, arr2 As Variant

    lr = Cells(Rows.Count, "F").End(xlUp).Row
    arr1 = Range("E8:F" & lr).Value
    arr2 = Range("G8:G" & lr).Value
    For i = 1 To UBound(arr1)
        If LCase(arr1(i, 2)) = "yes" Then arr2(i, 1) = arr1(i, 1)
    Next i
    Range("G8:G" & lr).Value = arr2
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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
Back
Top