none
overwriting a list based on value of another cell RRS feed

  • Question

  • Hi All - I have a sheet (an internal problem resolution register) in which I have a cell 'O6' which uses data validation to provide users with a drop down list with three options

    1) 25% - Under investigation
    2) 50% - Problem understood
    3) 75% - corrective action implemented - awaiting audit

    The list is generated from data in cells AH6 - AH8 (or table name 'ongoing').

    I'm trying to overwrite this list with the statement "100% closed and validated" (currently located in cell AH10) only if another cell (S6) is populated with a date (in this case the date of an audit). With the idea being that a user can take an action so far, but cannot close it off until it is validated by audit.

    I have tried to remove the data validation from cell O6 and instead use a counta string:

    =IF(COUNTA(S6)>=1,AH10,AH6:AH8)

    This sort of works. It gives me closing statement when S6 is populated and takes me to the first entry of my list when it's not - but I cant get the data range (ah6 - ah8) to display as a user selection.

    So is there any way of telling the formula above that range AH6 - AH8 should function as a list? or leaving the data validation of cell O6 in tact and simply overriding it when S6 is populated? or am I missing something / looking at this in the wrong way?

    If I can get the formula to work for cells o6 / s6 I can then just copy it down the entire S and O columns so the sheet is semi automated.

    UPDATE:

    I originally posted this into another section of excel forums but was advised that what I want to do is not possible with formulas and I would need a macros to complete and i should post here instead. At this point I'm ready to give up unless someone here can offer me a lifeline.

    Thanks for the help all.

    Wednesday, January 24, 2018 10:50 AM

All replies

  • Hi leeb1977,

    You had mentioned that,"I'm trying to overwrite this list with the statement "100% closed and validated" (currently located in cell AH10) only if another cell (S6) is populated with a date (in this case the date of an audit). With the idea being that a user can take an action so far, but cannot close it off until it is validated by audit."

    You can try to refer example below.

    When Sheet get activated , I assign those 3 values to combo box.

    On Sheet_change event, I check that IF value of cell G5 is Date then I assign value of cell A5 to combo box.

    Code:

    Private Sub Worksheet_Activate()
    Me.ComboBox1.Clear
    Me.ComboBox1.AddItem (Cells(1, 1))
    Me.ComboBox1.AddItem (Cells(2, 1))
    Me.ComboBox1.AddItem (Cells(3, 1))
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    If IsDate(Cells(5, 7)) Then
    Me.ComboBox1.Clear
    Me.ComboBox1.AddItem (Cells(5, 1))
    End If
    End Sub
    

    Output:

    This is just a sample code to give you an idea.

    Further, You can try to modify the logic, code as per your requirement.

    Let us know, If you have any further question.

    We will try to provide suggestions for that to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 25, 2018 2:42 AM
    Moderator