Need selected dropdown value in a cell of one sheet to populate same cell on other sheets

SnakeDoctor · #1 09-16-2024, 08:10 AM

Same old worksheet covering six aircraft.

Column C of each sheet uses a dropdown list to say the row is for either: -200 ONLY, -300 ONLY, or BOTH.

Currently, each sheet has its own data validation drop down list source range (same cell range, just re-entered on each sheet).

My question is, is there a way to make a selection in the cell on one sheet populate the corresponding cell in every other sheet?

So, let's say that in cell C9 of Sheet 3, someone selects "-200 ONLY" from the dropdown. It should set cell C9 of Sheet 1, 2, 4, 5, and 6 to -"-200 ONLY" also.

Is this possible?

Guessed · #2 09-16-2024, 10:59 PM

If you select multiple sheets BEFORE making a change to a cell then that change will be made on all the selected sheets.

To select multiple sheets, you either hold the Shift key (to select from the current sheet to the sheet you click on) or Ctrl key (to individually select/deselect sheets) before clicking on other sheet tab names.

NoSparks · #3 09-17-2024, 10:06 AM

That doesn't quite work with my Excel 2010 (don't know about newer versions)

If an entry is made in a 'normal' cell it's fine but a selection via data validation drop down doesn't fill in on the other sheets.
I would need to use a macro in the 'ThisWorkbook' module
Something along the lines of this

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim i As Long, sht, shtsArr
    
    '   only one cell ?
    If Target.CountLarge > 1 Then Exit Sub
    '   within chosen range ?
    If Intersect(Target, Sh.Range("C2:C20")) Is Nothing Then Exit Sub
    '   which sheets ?
    shtsArr = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")
    '   is this a sheet in shtsArr ?
    For Each sht In shtsArr
        '   if so
        If Sh.Name = sht Then
            '   disable events
            Application.EnableEvents = False
            '   write to each sheets in shtsArr
            For i = 0 To UBound(shtsArr)
                Sheets(shtsArr(i)).Range(Target.Address) = Target.Value
            Next i
            '   re-enable events
            Application.EnableEvents = True
            '   no need to check further once found
            Exit For
        End If
    Next sht

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Want to get the cell value on the bases of dropdown select. Want to copy the data from cell	Puneet Singh	Excel Programming	0	02-08-2022 11:04 AM
How to paste the value of a selected cell to another sheet?	Learner7	Excel Programming	3	06-30-2019 10:35 PM
Using Cell Values in Active Sheet to Populate Path to External Reference Link	wowcrofty	Excel	4	12-19-2016 12:29 PM
Change cell color everytime a value is selected in dropdown list	angelica_gloria	Excel	4	01-27-2012 06:47 PM
Auto-populate an MS Word table cell with text from a diff cell?	dreamrthts	Word Tables	0	03-20-2009 01:49 PM