How to refresh a formula in data validation?

[danbates]

Hi,

I have the following formula in a list via data validation in column E.

=IF(OR(D243=Value1),List1,IF(OR(D243=Value2),List144,IF(OR(D243=Value3),List192,IF(OR(D243=Value4),ListM,IF(OR(D243=Value5),Azera,IF(OR(D243=Value6),ListMM,IF(OR(D243=ValueK),ListK,IF(OR(D243=Value7),List216))))))))

It all works fine if the operator enters the data in correctly but if they make a mistake or change their mind then the formula doesn't keep up with the changes.

I have tried the following excel refresh suggestions without any success:

• To refresh the current cell - press F2 + Enter
• To refresh the current tab - press Shift + F9
• To refresh the entire workbook - press F9
• CTRL+ALT+SHIFT+F9

To make it work I go into data validation and select the formula and then press ok and that seems to refresh the cell.

Now here is what I need help with please:

I have recorded a macro to do the "refresh" but it only works for that cell. I need help to change the following macro to the active cell or better still the first empty cell in column E.

Here is the macro:

Sub Macro2()'
' Macro2 Macro
'


'
    Application.Run "'PO COVER 2019.xlsm'!UnprotectTheActiveSheet"
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=IF(OR(D243=Value1),List1,IF(OR(D243=Value2),List144,IF(OR(D243=Value3),List192,IF(OR(D243=Value4),ListM,IF(OR(D243=Value5),Azera,IF(OR(D243=Value6),ListMM,IF(OR(D243=ValueK),ListK,IF(OR(D243=Value7),List216))))))))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Application.Run "'PO COVER 2019.xlsm'!ProtectTheActiveSheet"
End Sub

Any help would be much appreciated.

Thanks

Dan

 

[Kamolga]

Hi,

You can have a macro launched when they change the value where target is the cell

Private Sub Worksheet_Change(ByVal Target As Range)
'You code
End Sub

This is to be placed in sheet, not a module (right click sheet name and click view code)

so that if they enter a wrong selection, the value is deleted for example and formula set up again.

 

[danbates]

Hi Kamolga,

Sorry I forgot to mention that after every completed entry the next row down becomes available for the next entry.

The problem with using recorded macro is that it will always look at cell D243.

Thanks

Dan

 

[Fluff]

@danbates
You should know better by now. Please supply links to any other site where you have asked this question.

 

[danbates]

Hi Fluff,

Yes, sorry. I am doing it now. I didn't do it before because I got called away from my PC at work.

Here is a link to another forum which I have posted it on:

https://www.excelforum.com/excel-ge...eference-to-cell-on-the-left.html#post5022845