MarkVrenken
asked on
excel dropdownlist on one sheet same value on another sheet in same workbook
Dear experts,
Is it possible to make 2 dropdown lists on 2 different sheets that always have the same value. Like if i have 2 lists with the same countries they both change. for example
if list1 = USA then List2 will be USA if list2 = England then list1 = england and so on!
thanks,
Mark
Is it possible to make 2 dropdown lists on 2 different sheets that always have the same value. Like if i have 2 lists with the same countries they both change. for example
if list1 = USA then List2 will be USA if list2 = England then list1 = england and so on!
thanks,
Mark
ASKER
Thanks for the reply! is it also possible to make both a dropdownlist so the Country1 and country2 sheet both have a dropdownlist.
something like this?
sample-v2.xlsx
sample-v2.xlsx
attached sample uses a small bit of macro code to change the value on sheet2 when sheet1 changes... and another piece to do the reverse.
The Country list is in a table on sheet3.
go to formula tab / Name manager to see the defined names used in the code.
Here is the code that is on each of sheet1 / sheet 2.
What the code sample above does... is check to see if Cell A1 on sheet1 changed.... if it did... it just sets the sheet2 cell A1 equal to the same value.
By putting similar code on Sheet2.... the values of the two dropdown lists are kept in sync.
Additional Note: Don't know if you've used the new excel tables yet or not... but the nice thing about defining them this way... is that you can easily add new rows to the table on sheet3... and the new dropdown values will be automatically added to the your dropdown lists.
to add a new row to the table on sheet3... go to the last cell of the table and hit the tab key. It will add a new row for you.
Book1.xlsm
The Country list is in a table on sheet3.
go to formula tab / Name manager to see the defined names used in the code.
Here is the code that is on each of sheet1 / sheet 2.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Cells(1, 1) Then
Sheets("Sheet2").Cells(1, 1) = Target
End If
End Sub
What the code sample above does... is check to see if Cell A1 on sheet1 changed.... if it did... it just sets the sheet2 cell A1 equal to the same value.
By putting similar code on Sheet2.... the values of the two dropdown lists are kept in sync.
Additional Note: Don't know if you've used the new excel tables yet or not... but the nice thing about defining them this way... is that you can easily add new rows to the table on sheet3... and the new dropdown values will be automatically added to the your dropdown lists.
to add a new row to the table on sheet3... go to the last cell of the table and hit the tab key. It will add a new row for you.
Book1.xlsm
ASKER
@helpfinder that's not really what i meant. i want to change dropdown 1 if i change dropdown two and i want to change dropdown 2 if i change dropdown 1
@buttersk. Your code gives me a infinite loop;) not desirable
@buttersk. Your code gives me a infinite loop;) not desirable
then definitely macro is needed if it should work as you described in your last post here ... unfortunately with macro I can not serve
ASKER
ok ty. I have an idea of how to do it but it can wait till tomorow.
thanks 2 u both:)
thanks 2 u both:)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Buttersk,
I'm sorry to bother you again but when i use your code and change something in the sheet i get a type mismatch because the code fires with every change on the sheet. Is there a way to make it specific for that cell?
Thanks in advance
I'm sorry to bother you again but when i use your code and change something in the sheet i get a type mismatch because the code fires with every change on the sheet. Is there a way to make it specific for that cell?
Thanks in advance
sample.xlsx