My validation list is on a different sheet.
I tried what you suggested but it didn't work.
I want (in effect) the same dropdown list on every (or chosen) sheets that will all change the value.
Let me see if I can explain better.
Sheet1 has a drop down list in cell C3 that (now) has a named range with the data coming from Sheet2. When I use the drop down all of my data changes according to what is in C3.
Sheet3 has data that is also referenced to Sheet1 C3 but I want a drop down list on Sheet3 to be able to overide the selectionin C3 on Sheet1
The whole worksheet is based upon what is in Sheet1 c3.
How to I change that value from a drop down box on different sheets.
This is a quick demo of the list box driven by a named range.
When the list box is changed, other cells are recalc.
Is this the basis of your question?
Then, what are you trying to do beyond this?
That doesnt do what I want it to do.
Imagine having a drop down list on every sheet. When you change ANY of the dropdown lists (on any given sheet) it changes the data in one specific cell. The rest of the worksheet is reliant on this one cell to formulate data throughout the whole file on akll the corresponding sheets.
Just want to make sure.
The workbook I attached, has a dropdown on worksheet 1.
If we added another worksheet and added the same drop down on worksheet 2.
If either worksheet's drop down listbox value is selected and changed, it would update the other worksheet's dropdown?
If it can be done for two, then we can assume it could be done for 3 or 10 worksheets.
Did I get that concept correct?
(sorry, was drinking coffee, missed your latest attachment, will open it now)
Question 2 - can the workbook include vba modules?
(remember, the warning will tell users "potentially unsafe" the first time).
Let me know, that would in fact be a little tricky without vba code.
With code, it is possible.
The code would require in Worksheet_Change Event in each sheet.
The cell dropdown only looks at itself for a change event (concept of Scope).
I had to research possibly changing the sort order of the Name Range used for the in-cell drop down. But, came up empty due to the Cell's scope of the change event.
My expectations are that it would work like this:
If the in cell drop-down is cell A1 on Sheet1 Cell B1 on Sheet2.
Right-click the sheet tab of Sheet1 then select "View Code".
Paste this code into the code window:
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targetSheet As Worksheet
If Not Intersect(Target, Range("[B]A1[/B]")) Is Nothing Then ' This responds to [B]A1[/B]
Set targetSheet = ActiveWorkbook.Worksheets("[B]Sheet2[/B]") ' Target
' This line would be copied to include all other Targets EXCEPT the Worksheet that is being changed
' Rx_ remember - use the "actual sheet name" or use 1, 2, 3 without quotes for the worksheets in the order. If the order changes, the numbers will be hosed up. Let me suggest using worksheet names
On Error Resume Next
Application.EnableEvents = False
targetSheet.Range("[B]B1[/B]") = Target.Value ' target cell
'Now the new value to be used is harvested
Application.EnableEvents = True
End If
End Sub
I updated the attachment with a Sheet 1 and Sheet 2
It workes for me.
In Sheet2, right-click the Sheet tab and select "View Code".
Here is the code in that view
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targetSheet As Worksheet
' This is worksheet2 targeting Worksheet 1 - this workbook in cell drop is in B1
If Not Intersect(Target, Range("[B]B1[/B]")) Is Nothing Then ' watch only cell[B]B1[/B]
Set targetSheet = ActiveWorkbook.Worksheets("[B]Sheet1[/B]") ' define the sheet to copy to
On Error Resume Next
Application.EnableEvents = False
targetSheet.Range("[B]A1[/B]") = Target.Value ' Destination is "A1"
Application.EnableEvents = True
End If
End Sub
By using Worksheet 1 A1 and Worksheet 2 B1
I thought you could keep track of how this works.
This should get you started.
If this code works, as the orginator, please to to THREAD TOOLS and change to [Solved].
Also, as the multi-worksheet update framework is created (with out any confidential data) be sure to post a copy to help others.
