Excel file with dropdown and datasheet editable from the other sheet

[azazafzal]

Hi All,

I have my data in sheet1 (Id,Name,Age,Sex and Marital Status).
Firstly, In Sheet2 I want to create two drop down, first one will show marital status ('married' or 'unmarried) and second will show age =>35 years after which I should get the data in sheet2 which matches both the criteria.
Secondly, If I make any changes to any of the data in sheet2, I want that change to be updated in sheet1 in that against that Id in the same.

For example, if I change the name from D to E then the name should get updated in sheet1.

Any help is appreciated..!!

 

[Luke M]

First part is done using Adv. Filter, second part is a little bit tricker, but you can do since you have an ID column. See attached.

 

[azazafzal]

Thank you very much Luke...it works gr8..!!
Appreciate your help...Thank you once again

 

[azazafzal]

One more question Luke.
If I want to add more filter such as 'Name' and 'Sex' and add more columns in Sheet1. Then what are the modification I need to make in the VBA?

 

[Luke M]

Nothing to the VB, hopefully. If you look in the Formulas - Name Manager, you'll see several defined ranges. The VB looks for these named ranges, which give you/user benefit in that they don't have to edit code directly. The important one for you is the rngCrit. If you add more filters, you'll want to make sure that you get those values over


Then, change the definition of rngCrit to be I1:L2, or whatever you decide to make it.

If you start getting fancy with the criteria, I'd suggest reading this tutorial:
http://www.contextures.com/xladvfilter02.html

 

[azazafzal]

Thank you veryyyy much Luke