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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
Awesome!!! Thank you very much kind sir. You made my life just a little bit easier :)
 
Upvote 0

Forum statistics

Threads
1,218,625
Messages
6,143,573
Members
450,493
Latest member
Woejeber

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
Back
Top