Hello,
I have created a report which is produced monthly within excel. This report is run for 5 services who's data is stored on five different sheets (i know this isn't ideal data management but all these sheets contain tens of thousands of rows and they wouldn't all fit into one sheet). I have a macro which runs a bunch of SUMPRODUCT formula's on this data and returns the results into tables.
Currently the report is constructed to report only high level information (basically the information is calculated for individual Assistant Directors). I have now been asked to produce this report at team level.
I feel that the best way of achieving this without hardcoding the values in VBA would be to create a unique list of teams and then use this list to create a drop down list with the Macro looking at this drop down list. The teams are contained within all five service sheets, so what i need is a formula that will compare all teams across the five sheets and return a unique list.
Hope that makes sense to everyone/anyone.
Cheers
I have created a report which is produced monthly within excel. This report is run for 5 services who's data is stored on five different sheets (i know this isn't ideal data management but all these sheets contain tens of thousands of rows and they wouldn't all fit into one sheet). I have a macro which runs a bunch of SUMPRODUCT formula's on this data and returns the results into tables.
Currently the report is constructed to report only high level information (basically the information is calculated for individual Assistant Directors). I have now been asked to produce this report at team level.
I feel that the best way of achieving this without hardcoding the values in VBA would be to create a unique list of teams and then use this list to create a drop down list with the Macro looking at this drop down list. The teams are contained within all five service sheets, so what i need is a formula that will compare all teams across the five sheets and return a unique list.
Hope that makes sense to everyone/anyone.
Cheers