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
73
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,822
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
 

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
73
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?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,822
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.
 

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
73

ADVERTISEMENT

This is a query so the number of rows can/will change on each refresh.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,822
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")
 

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
73
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.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,822
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,441
Members
417,025
Latest member
MusterDuster

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