Excel 2007 cell value of drop-down list change with formula

Yes.  Here's a worksheet_change event in Sheet1's codepage that will check for changes in A2, and if there is, it will make D2 that value, still preserving the data validation list for future changes.  However, you'd need additional code to ensure the change was within the data validation rules.  What you have now, just forces the change.

Here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A2")) Is Nothing Then 'a change was made in A2
        Range("D2").Value = Target.Value
    End If
    
End Sub

Select allOpen in new window

See attached demo workbook.

Enjoy!

Dave
Drop-down-list-change-value-r1.xlsm

Thanks!  The only thing is that the cell A2 is in a different worksheet.  I have linked value to the target worksheet but the value of the target cell won't change unless I clicked on the linked cell and hit enter.  How to fix this?

Is the different worksheet online at the same time, or is it closed and stored on a server somewhere?  You really need to describe your environment more, so I can help you further.  Please be much more explicit with what is going on, to avoid rework.

Thanks,

Dave

All the worksheets are contained in the same workbook so they are online at the same time.  Thanks.

Ok - what is the name of the worksheet that has the code you're trying to put in the data validation dropdown - and what is the range of the cell in that worksheet?  We just need to modify the code to affect the change...

E.g., let's assume Sheet1, Range C2 is where you're making data changes - but you want the result of Range C2 in Sheet2 to go to that data validation dropdown cell.

If I've described all that right, just let me know the details, or upload a simple mock spreadsheet to that effect.

Dave

Yes, your description is correct.  Thanks.

Thanks!  This is what I need.  I apologize for the late reply.