Link multiple dropdown list to same reference

IronM

New Member
Joined
Jan 20, 2022
Messages
15
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

I know there's been another question similar to this (link), however the OP was more advanced in his solution.

Here's my problem:
I have an workbook with several sheets (Sheet1, Sheet2, Sheet3). I created a dropdown list in A1 cell with 3 currencies: EUR, USD, GBP using Data Validation --> List and adding them manually.

I named A1 cell "currency" and used that reference throughout the workbook. When I change the currency, all my figures change acordingly.

All good until here. However, for the purpose of a faster check, I need to switch back and forth between currencies and see the result very fast (without going to sheet1 cell A1 and then come back in sheet3 to see the result)

So I need to add the same dropdown on all 3 sheets that will have basically the same effect. Wherever I change the currency type, to change on all 3 dropdowns and change my figures accordingly.

Is there a way to achieve this?

Thank you!!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

MCLIFTO8

Board Regular
Joined
Nov 13, 2022
Messages
86
Office Version
  1. 365
Platform
  1. Windows
Yes, building off @My Answer Is This's solution just put a Worksheet_Change event to edit the value of your other cells.

sheet1
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    ThisWorkbook.Sheets("Sheet2").Range("A1").Value2 = ActiveSheet.Range("A1").Value2
    ThisWorkbook.Sheets("Sheet3").Range("A1").Value2 = ActiveSheet.Range("A1").Value2
    Application.EnableEvents = True
End Sub

sheet2
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value2 = ActiveSheet.Range("A1").Value2
    ThisWorkbook.Sheets("Sheet3").Range("A1").Value2 = ActiveSheet.Range("A1").Value2
    Application.EnableEvents = True
End Sub

sheet3
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value2 = ActiveSheet.Range("A1").Value2
    ThisWorkbook.Sheets("Sheet2").Range("A1").Value2 = ActiveSheet.Range("A1").Value2
    Application.EnableEvents = True
End Sub
 
Last edited:
Upvote 0
Solution

IronM

New Member
Joined
Jan 20, 2022
Messages
15
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Awesome!!! Thank you very much kind sir. You made my life just a little bit easier :)
 
Upvote 0

Forum statistics

Threads
1,186,431
Messages
5,957,801
Members
438,320
Latest member
Thatisfree

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top