Link to home
Start Free TrialLog in
Avatar of MarkVrenken
MarkVrenkenFlag for Netherlands

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
Avatar of helpfinder
helpfinder
Flag of Slovakia image

if you are OK with zero value in sheet 2 in case there is no value in sheet 1 then attached sample may be helpful for you (but I am sure there should be way how to fix also this)
sample.xlsx
Avatar of MarkVrenken

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
Avatar of Ken Butters
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.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Cells(1, 1) Then
        Sheets("Sheet2").Cells(1, 1) = Target
    End If
   
End Sub

Open in new window


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
@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
then definitely macro is needed if it should work as you described in your last post here ... unfortunately with macro I can not serve
ok ty. I have an idea of how to do it but it can wait till tomorow.

thanks 2 u both:)
ASKER CERTIFIED SOLUTION
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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