Data Validation List, Multiple Sheets. Update one, update them all

[DragonzBreath]

Hi Guys,

I'm writing a budget spreadsheet. I have the same data validation list on multiple sheets in a workbook. I would like to be able to update the DVL on any one of the worksheets and have it update/mirror on all the other worksheets. I've googled for this and get nothing close to what I'm after. Perhaps my search terms are garbage, I don't know.

Is anyone able to help with the VB code to facilitate this?

Also, I have a pivot table which has the average number of days in a given time period. i.e. a month is 30.42 days (365/12), a quarter is 91.25 days (365/4)
I utilise VLOOKUP to work out costs of bills, by working them back to a daily cost and then multiplying them by the number of days in a give period based on the DVL in the above question.
Is there a more simple way to do this? i.e. does excel have a built in function for working out the average number of days in a month/quarter that doesn't require referencing a pivot table?

Thank you for reading, and thank you in advance for any input

DB

 

[DanteAmor]

Hi Guys,

I'm writing a budget spreadsheet. I have the same data validation list on multiple sheets in a workbook. I would like to be able to update the DVL on any one of the worksheets and have it update/mirror on all the other worksheets. I've googled for this and get nothing close to what I'm after. Perhaps my search terms are garbage, I don't know.

If you put the data from the validation list in a Table then in your DVL, in each, you must refer to the Table name.
When the data grows, automatically all the DVL will take the new data.

See
https://www.youtube.com/watch?v=FV2ki0kRll8

Or search for videos with: "Create a Dynamic Data Validation Drop Down with Excel Tables"

 

[DragonzBreath]

If you put the data from the validation list in a Table then in your DVL, in each, you must refer to the Table name.
When the data grows, automatically all the DVL will take the new data.

See
https://www.youtube.com/watch?v=FV2ki0kRll8

Or search for videos with: "Create a Dynamic Data Validation Drop Down with Excel Tables"

Thanks DanteAmor, Dynamic DV Lists aren't what I'm looking to do however. I want the exact same DV List in cell 2 of Sheet 1, Sheet 2, Sheet 3, Sheet 4. When I update the DV List on Sheet 2, I'd like it to autoamtically be the same value on Sheets 1, 3 and 4. Similarly if I update the value on Sheet 1, I'd like it to automatically update on Sheets 2, 3 and 4. And so on and so forth.

Hopefully this explains better

 

[My Aswer Is This]

A Data Validation list. Is normally a range of Values.

You said when I change the Data Validation list on one sheet I want it to change on all sheets.

How do you change the Data Validation List?

 

[DragonzBreath]

A Data Validation list. Is normally a range of Values.

You said when I change the Data Validation list on one sheet I want it to change on all sheets.

How do you change the Data Validation List?

The Data Validation List is a simple list 'Weekly, Monthly, Fortnightly, Monthly, Bi Monthly, Quarterly, Half Yearly, Annually'. It's changed manually, and is referenced by formula's throughout the whole workbook to calculate costs, income and various other things.

 

[My Aswer Is This]

You said:

It's changed manually

Do you mean you go into the Data Validation List Box On the Ribbon and manually change them there.
Explain Manually


​

 

[DragonzBreath]

Yep, you're right... I'm being clear as mud! Sorry.

I'm referring to the drop down itself, I drop the list choose a time period and the spreadsheet updates all the values. That's what I want reflected/mirrored across all tabs sheets.

 

[DanteAmor]

Put the following code in the events of thisworkbook.

ThisWorkbook EVENT
- Open the VB Editor (press Alt + F11).
- Over in the Project Explorer, double click on ThisWorkbook.
- In the white panel that then appears, paste the below code.

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    Dim ws As Worksheet
    Dim wCell As Range, wRange As Range, wData As Range
    Dim myVal As String
    '
    If Not Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
        myVal = Target.Value
        Application.EnableEvents = False
        For Each ws In Sheets
            For Each wCell In ws.Cells.SpecialCells(xlCellTypeAllValidation)
                Set wRange = Range(Mid(wCell.Validation.Formula1, 2))
                If myVal = "" Then
                    wCell.Value = ""
                Else
                    For Each wData In wRange
                        If wData.Value = myVal Then
                            wCell.Value = myVal
                            Exit For
                        End If
                    Next
                End If
            Next
        Next
        Application.EnableEvents = True
    End If
End Sub

Change a data in any list and automatically all lists will have that data.
Try and tell me.

 

[DragonzBreath]

Thanks mate, there are other drop down lists in the workbook, I note that the VB doesn't specify any cells. Can we specify cell C2 in each sheet?

Thank you again!

 

[DanteAmor]

Thanks mate, there are other drop down lists in the workbook, I note that the VB doesn't specify any cells. Can we specify cell C2 in each sheet?

Thank you again!

Did you try the code?
The code searches only the lists that are related, the other lists do not touch them. (I thought about those)