To Keep Dropdown entry blank when adjacent cell value changes

[Pappa]

Hi Sir can you help me please to find the solution to my following query

I need the my dropdown entry to become blank when the adjacent left side cells value changes.

Eg, in Cell A5 i have dropdown entry say "X"(using Data validation).

I have selected "X'' as per my entry in A1- A4.

I want this entry (only value "X") to become blank, whenever and whatever changes happen in A1-A4 cells

After the reentry made in A1-A4, I should also be able to select again the dropdown list in A5.

Thanks Pappa

 

[Hany ali]

ooh ...Where's The Excel File AS EXAMPLE ?!!!!!!

 

[Pappa]

Hello, All Good day

The query was not exactly correct, Excuse me for the confusion made. I have attached a sample book for the reference please.

Based on A2:E2 values (which are reflecting as reference value from Sheet 2 entry), I have a dropdown list from F2:F4, which will be selected manually and inserted.

As per the configuration If i change any value in Sheet2 then it will change the values in A2:E2 as well. So when I do this change in Sheet2, I want F2 to go blank, So I should be alerted easily and can make re-entry in F2.

And, If i remove one entry from Sheet 2, which referencing Row 3 in this sheet for example: As per setting the cell range A4:E4 will be shifted up, it is to avoid a blank row.

But here I want my entry in F3 and F4 to go as blank, so that with the new alignment and new entries , I can change the dropdown value in column F.

Thanks in Advance, Pappa

 

[Pappa]

Hi All,

I could able to find a code from Stack Overflow website to solve the issue, as shown below

>>> use code - tags <<<

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    If UCase(Target) = "" Then Target.Offset(, 1) = ""
End Sub

Please help me with the code address above, It works only with A1 and B1 cells, I need it for Column B and Column G.

In Code: If Target. Address <> "$A$1" where it requires to be for column B, and in column G I have my data validation input.

A bit confused with the Offset configuration in case of Column B and Column G. Thanks in advance