evilcreature
New Member
- Joined
- May 16, 2017
- Messages
- 2
I have 2 sheets in an excel.
1. Performance Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Region
[/TD]
[TD]Chain
[/TD]
[TD]Code
[/TD]
[TD]Q1
[/TD]
[TD]Q2
[/TD]
[TD]Q3
[/TD]
[TD]Q4
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]1
[/TD]
[TD]c
[/TD]
[TD]b
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]2
[/TD]
[TD]a
[/TD]
[TD]c
[/TD]
[TD]b
[/TD]
[TD]a
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]3
[/TD]
[TD]b
[/TD]
[TD]b
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]4
[/TD]
[TD]d
[/TD]
[TD]c
[/TD]
[TD]c
[/TD]
[TD]b
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]5
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]b
[/TD]
[/TR]
</tbody>[/TABLE]
The other Sheet named "Selector"
Where I need to have 3 Drop Downs
In Cell B2, Dropdown Containing Unique Values from Region Column
In Cell B3, Dropdown Containing Unique Value from Chain Column (after applying filter based on B2)
In Cell B4, Dropdown which contains 4 values (Q1, Q2, Q2, Q4)
When the user selects an Item in B2, 1. Filter is applied in PEFORMANCE SHEET and Dropdown List Values in B3 get changed.
When Use Selects an item in B3, filter is applied in PEFORMANCE Sheet.
Finally when user selects an item from B4, the following actions should take place.
1. Sort the Performance Data as per value in CELL B4 on Selector Sheet. If B2=Q1 then sort on column heading Q1, and so on.
2. Copy the entire rows in Code Column (excluding the header)
3. Paste in "Selector Sheet" from B5 onwards
Example. if user selected R1, CC and Q4 on "Selector Sheet". Then the following would be the result on Selector Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Choose A Region ->
[/TD]
[TD]R1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Choose A Chain ->
[/TD]
[TD]CC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Choose A Value ->
[/TD]
[TD]Q4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Resulting Codes ->
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The data here is for sample only. I have 350+ rows in actual sheet.
How can this be achieved in Excel 2016?
Regards
1. Performance Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Region
[/TD]
[TD]Chain
[/TD]
[TD]Code
[/TD]
[TD]Q1
[/TD]
[TD]Q2
[/TD]
[TD]Q3
[/TD]
[TD]Q4
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]1
[/TD]
[TD]c
[/TD]
[TD]b
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]2
[/TD]
[TD]a
[/TD]
[TD]c
[/TD]
[TD]b
[/TD]
[TD]a
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]3
[/TD]
[TD]b
[/TD]
[TD]b
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]4
[/TD]
[TD]d
[/TD]
[TD]c
[/TD]
[TD]c
[/TD]
[TD]b
[/TD]
[/TR]
[TR]
[TD]R1
[/TD]
[TD]CC
[/TD]
[TD]5
[/TD]
[TD]a
[/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]b
[/TD]
[/TR]
</tbody>[/TABLE]
The other Sheet named "Selector"
Where I need to have 3 Drop Downs
In Cell B2, Dropdown Containing Unique Values from Region Column
In Cell B3, Dropdown Containing Unique Value from Chain Column (after applying filter based on B2)
In Cell B4, Dropdown which contains 4 values (Q1, Q2, Q2, Q4)
When the user selects an Item in B2, 1. Filter is applied in PEFORMANCE SHEET and Dropdown List Values in B3 get changed.
When Use Selects an item in B3, filter is applied in PEFORMANCE Sheet.
Finally when user selects an item from B4, the following actions should take place.
1. Sort the Performance Data as per value in CELL B4 on Selector Sheet. If B2=Q1 then sort on column heading Q1, and so on.
2. Copy the entire rows in Code Column (excluding the header)
3. Paste in "Selector Sheet" from B5 onwards
Example. if user selected R1, CC and Q4 on "Selector Sheet". Then the following would be the result on Selector Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Choose A Region ->
[/TD]
[TD]R1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Choose A Chain ->
[/TD]
[TD]CC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Choose A Value ->
[/TD]
[TD]Q4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Resulting Codes ->
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The data here is for sample only. I have 350+ rows in actual sheet.
How can this be achieved in Excel 2016?
Regards