Validation List - Populate Selection on Multiple Sheets

[zoom_bean]

Hi, I'm trying to populate a list selection response onto multiple sheets. For example in cell A1 is a validation list across multiple sheets, all sheets having the same list. If you select an option on sheet 1, that same response is populated into sheets 2, 3, etc.

Does anyone know how to do achieve this?

Is it possible to have a cell be a validation list if the same cell on the prior sheet is blank, if not blank to be a fixed value determined by the prior sheet?

Any other thoughts or suggestions welcome?

Thanks in advance.

 

[scott_n_phnx]

Are you just looking to have the value that is selected in the Data Validation list on Sheet1 to populate across the other sheets? If so, a simple solution would be to use <b>=IF(ISBLANK(Sheet1!A1),"",Sheet1!A1)</b> on each of the sheets, in the appropriate locations. Or are you needing to have Data Validation lists on other sheets to be set with the same selection as the first one? That would probably take a macro to accomplish.

 

[zoom_bean]

Thanks scott_n_phnx I was hoping to have the drop down list on all sheets yeah. I searched around a little more online to find an alternative and it was all pointing towards the macro as you mentioned for doing it.

As a quick easy (somewhat ugly) fix what I did was create a very thin barely visible column and stuck a formula similar to the one you mentioned in the column next to the cell I wanted the list to appear so when there is a value on the previous sheet it overlaps into the next cell and appears as though that option has been selected from the drop list but then still shows the drop down list when clicking on the cell.

 

[MickG]

This code must go in the "ThisWorkbook" module.
Click "Alt +F11" to see VBCode window.
Click "Ctrl+R" to see "Project window" , then look down the list for "ThisWorkBook", Double click to Open, Paste code into this Module.

Private [COLOR="Navy"]Sub[/COLOR] Workbook_SheetChange(ByVal Sh [COLOR="Navy"]As[/COLOR] Object, ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] sht [COLOR="Navy"]As[/COLOR] Worksheet
Application.EnableEvents = False
  [COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] sht [COLOR="Navy"]In[/COLOR] ActiveWorkbook.Worksheets
        sht.Range("A1").Value = Target
    [COLOR="Navy"]Next[/COLOR] sht
  [COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[scott_n_phnx]

@zoom_bean - I am not sure if MickG was able to answer your question or if you are still looking for an answer. As I understand it, you have a Data Validation list on Sheet1. When the value is changed, you want the other sheets to update with the list that it refers to, or to the same list with the same value selected? I have built something that goes off of the first premise. On sheet one, I used a Data Validation list with <b>Names1</b> and <b>Names2</b>. When either is selected, it calls a macro that goes through each sheet and changes the Data Validation list in A1 to either the Names1 list (just a few names) or to the Names2 list (same names, just added <b>2</b> to the names).

This must be placed in the Worksheet module for Sheet1(or whatever you have named your sheets). I would recommend a test workbook, so you don't lose anything important.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If Target.Value = "Names1" Then
        Call AddNames1
    ElseIf Target.Value = "Names2" Then
        Call AddNames2
    ElseIf Target.Value = "" Then
        Call RemoveNames
    End If
End Sub
Sub AddNames1()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Sheet1" Then
    ws.Select
    ws.Range("A1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Names1" 'Change to your named range
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    End If
Next ws
Sheets(1).Select
Application.ScreenUpdating = True
End Sub
Sub AddNames2()
Application.ScreenUpdating = True
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Sheet1" Then
    ws.Select
    ws.Range("A1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Names2" 'Change to your named range
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    End If
Next ws
Sheets(1).Select
Application.ScreenUpdating = True
End Sub
Sub RemoveNames()
Application.ScreenUpdating = True
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Sheet1" Then
            ws.Select
            ws.Range("A1").Select
                With Selection.Validation
                    .Delete
                End With
    End If
Next ws
Sheets(1).Select
Application.ScreenUpdating = True
End Sub