I am working with Excel 2007. Sheet1 has 11 columns and about 20,000 rows or records. My column “A” is called CC and my column “B” is called GROUP. This is the scenario I am trying to accomplish.
On my sheet2 I have the same headings but it should display data from sheet1 based on the user input.
The data in the CC field is alphanumeric & a 3 digit data. The first 2 represent the department. Example I have the following CC data
CC
5P1
5P2
5P3
5P4
161
162
163
Scenario 1. In this example, that means I have a department called “5P” that represents the 1[SUP]st[/SUP] four CC’s on that list & another one called “16” that represents the last three CC’s on that list. Based on this information I want my CC field in sheet2 to have a VBA script that will automatically put the CC field from sheet 1 by departments in a drop down list were when a user will click the drop down, they will see a drop down list like 5P & 16 and by selecting one of them (Lets say 5P), that will automatically populate the column with all CC’s that are 5P on sheet2. Please note that only distinct CC’s are populated. Example if 5P is selected and there are ten 5P2’s on the list in sheet1, means that only one 5P2 should show in the drop down.
Scenario 2. From the results you get in scenario 1, I want to be able to click on any of these cc links and as a result see all the groups displayed in column B under the Group field associated with that cc that was clicked. Example if you select 5P (department) in the CC field, it displays all the cc’s in that department which is the 4 you see at the bottom and if you select one of these let’s say 5P1 it displays All the groups associated with 5P1 on the screen in column B under the group field as seen below. Based on these results, there are 6 groups associated with 5P1 cc. I want the user to be able to select a group from the result by clicking any of the 6 groups displayed which in return will display the remaining 9 fields (columns) associated with that group. I want the CC and the Group to display at the top of their respective column as the user drills down to get the information they are looking for. In this example, 5P1 will be displayed at the top in column A and if 164 was selected from the Group column that will be displayed at the top in column B. How can I achieve this goal. Thanks for your help in advance.
A B
CC Group
5P abc, 1ge, 164,
16 j75, 4d3, 532
5T
5P1
5P2
5P3
5P4
On my sheet2 I have the same headings but it should display data from sheet1 based on the user input.
The data in the CC field is alphanumeric & a 3 digit data. The first 2 represent the department. Example I have the following CC data
CC
5P1
5P2
5P3
5P4
161
162
163
Scenario 1. In this example, that means I have a department called “5P” that represents the 1[SUP]st[/SUP] four CC’s on that list & another one called “16” that represents the last three CC’s on that list. Based on this information I want my CC field in sheet2 to have a VBA script that will automatically put the CC field from sheet 1 by departments in a drop down list were when a user will click the drop down, they will see a drop down list like 5P & 16 and by selecting one of them (Lets say 5P), that will automatically populate the column with all CC’s that are 5P on sheet2. Please note that only distinct CC’s are populated. Example if 5P is selected and there are ten 5P2’s on the list in sheet1, means that only one 5P2 should show in the drop down.
Scenario 2. From the results you get in scenario 1, I want to be able to click on any of these cc links and as a result see all the groups displayed in column B under the Group field associated with that cc that was clicked. Example if you select 5P (department) in the CC field, it displays all the cc’s in that department which is the 4 you see at the bottom and if you select one of these let’s say 5P1 it displays All the groups associated with 5P1 on the screen in column B under the group field as seen below. Based on these results, there are 6 groups associated with 5P1 cc. I want the user to be able to select a group from the result by clicking any of the 6 groups displayed which in return will display the remaining 9 fields (columns) associated with that group. I want the CC and the Group to display at the top of their respective column as the user drills down to get the information they are looking for. In this example, 5P1 will be displayed at the top in column A and if 164 was selected from the Group column that will be displayed at the top in column B. How can I achieve this goal. Thanks for your help in advance.
A B
CC Group
5P abc, 1ge, 164,
16 j75, 4d3, 532
5T
5P1
5P2
5P3
5P4