• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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
0
MarkVrenken
Asked:
MarkVrenken
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
helpfinderIT ConsultantCommented:
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
0
 
MarkVrenkenAuthor Commented:
Thanks for the reply! is it also possible to make both a dropdownlist so the Country1 and country2 sheet both have a dropdownlist.
0
 
helpfinderIT ConsultantCommented:
something like this?
sample-v2.xlsx
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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

thanks 2 u both:)
0
 
Ken ButtersCommented:
Sorry should have re-tested after last minute change.

To fix that... I added code to disable event while updating other sheet.

sample:

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

Open in new window

Book1.xlsm
0
 
krishnakrkcCommented:
Hi

You could achieve this with a form control (drop down)

PFA.

Kris
Book1.xlsm
0
 
MarkVrenkenAuthor Commented:
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now