Hi everyone,
I have a fairly complicated situation here (at least I think it is).
I have a spreadsheet, lets call it "TrackingLog". TrackingLog has a tab for each month of the year (named accordingly - March 2010, April 2010 etc).
Each sheet is identical in respect to it's column headers and type of data store in each column (just the actual values in the cells are different).
There are three columns I specifically need to work with:
1) Action (text - which contains a 2 letter abbreviation) (cell range - B4:B500)
2) Submitter Name (text - which obviously contains the name of the person who submitted the data)(cell range - F4:F500)
3) Region (text - which contains the name of the region the submitter is from)(cell range - L4:L500)
What I need to do is basically work with the data to show the following stats:
1) How many times "NA" and "FA" appear in the Action column for all months combined.
2) How many times each submitter submitted "NA" and "FA" articles and if that number falls within three ranges, they are assigned a certain color:
a) Between 5 and 9 they are green.
b) Between 10 and 19 they are orange.
c) If they are 20 and above they are red.
3) How many of each "NA" and "FA" articles appear for each Region.
I had created a summary sheet which did all this using COUNTIF and SUMPRODUCT formulas and it works, I just don't want it IN the same workbook.
What I would like to do ideally, is to create an entirely seperate workbook that, when opened, grabs the data from the external "TrackingLog" sheet, then calculates and summarizes the requirements above in a userform and then provides the option to save the data to a new excel sheet. The one exclusion to the data shown in the userform would be the submitters. There are hundreds of them, so I would only like to show the top 5 or so.
I hope I explained that well enough.
My problem is that I have been having a great deal of difficulty in grabbing the data from the external workbook the way I want and then manipulating and storing it appropriately so it can be displayed in the userform.
If anyone has some good help to offer I would very much appreciate it.
Thanks,
mobiius
I have a fairly complicated situation here (at least I think it is).
I have a spreadsheet, lets call it "TrackingLog". TrackingLog has a tab for each month of the year (named accordingly - March 2010, April 2010 etc).
Each sheet is identical in respect to it's column headers and type of data store in each column (just the actual values in the cells are different).
There are three columns I specifically need to work with:
1) Action (text - which contains a 2 letter abbreviation) (cell range - B4:B500)
2) Submitter Name (text - which obviously contains the name of the person who submitted the data)(cell range - F4:F500)
3) Region (text - which contains the name of the region the submitter is from)(cell range - L4:L500)
What I need to do is basically work with the data to show the following stats:
1) How many times "NA" and "FA" appear in the Action column for all months combined.
2) How many times each submitter submitted "NA" and "FA" articles and if that number falls within three ranges, they are assigned a certain color:
a) Between 5 and 9 they are green.
b) Between 10 and 19 they are orange.
c) If they are 20 and above they are red.
3) How many of each "NA" and "FA" articles appear for each Region.
I had created a summary sheet which did all this using COUNTIF and SUMPRODUCT formulas and it works, I just don't want it IN the same workbook.
What I would like to do ideally, is to create an entirely seperate workbook that, when opened, grabs the data from the external "TrackingLog" sheet, then calculates and summarizes the requirements above in a userform and then provides the option to save the data to a new excel sheet. The one exclusion to the data shown in the userform would be the submitters. There are hundreds of them, so I would only like to show the top 5 or so.
I hope I explained that well enough.
My problem is that I have been having a great deal of difficulty in grabbing the data from the external workbook the way I want and then manipulating and storing it appropriately so it can be displayed in the userform.
If anyone has some good help to offer I would very much appreciate it.
Thanks,
mobiius
Last edited:
