[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-14
8
Medium Priority
?
641 Views
Last Modified: 2012-05-12
Hi, I have a cell with a drop-down list.  Is it possible to automatically change that cell value to one of the values from the drop-down base on the value of another cell?

For example, the drop-down list is in cell D2 with value A, B, C.  I'd like to input a value cell A2 and have D2 automatically updated with the cell in A2 but at the same time preserve the drop-down list for future changes.
Drop-down-list-change-value.xlsx
0
Comment
Question by:JCJG
  • 4
  • 4
8 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36972449
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

Open in new window


See attached demo workbook.

Enjoy!

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

Author Comment

by:JCJG
ID: 36973828
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?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36974517
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:JCJG
ID: 36974972
All the worksheets are contained in the same workbook so they are online at the same time.  Thanks.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36974980
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
0
 

Author Comment

by:JCJG
ID: 36974983
Yes, your description is correct.  Thanks.
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37004101
Were you waiting on me?  Sorry.

make the change in Sheet1, Cell C2.  WHen that change is made, you can go to Sheet2 and see D2 has that data in the dropdown cell.

Here's hte code in SHEET1's codepage:
 
Private Sub Worksheet_Change(ByVal Target As Range)

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

Open in new window


See attached,

Dave
Drop-down-list-change-value-r2.xlsm
0
 

Author Closing Comment

by:JCJG
ID: 37214798
Thanks!  This is what I need.  I apologize for the late reply.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question