Dirty Laundry
New Member
- Joined
- Jan 17, 2009
- Messages
- 33
I inherited a project from someone else at work and I'm trying to "clean up" their workbook for better functionality. Here's a summary of how it's built:
Sheet #1 (data gathering) contains sales tracking info across multiple columns, as entered by the end user: month assigned, month closed (if sold), activity type, opportunity status, sales person name, product type, revenue type, revenue value, etc. The values in these columns are mainly selected via drop-down menu choices.
Sheet #2 (data reference) contains the info that populates the drop-down menus on Sheet #1. There are also columns on here that concatenate the values of cell selections on Sheet #1 (i.e. month closed + revenue type,), as well as pulling data from Sheet #1 without concatenation (i.e. Revenue Value Type A, Revenue Value Type B). I always like having a separate hidden tab to store this kind of info; less chance that someone will accidentally delete data that is crucial to the workbook's performance.
Sheet #3 (monthly snapshot output) needs to contain a summary of info based on certain conditions, and calculated on a "month ending" (not YTD) basis. It pulls this data from Sheet #2.
For example, Sheet #3 reflects the following: Total revenue quoted (by revenue type), total revenue sold (by type), total count of opportunities quoted, total count of opportunities sold. It's tricky for me because this is not a "grand total" of everything on Sheet #1; it needs to be a monthly snapshot based upon whatever the desired month is.
To further complicate things, this will be based on a lot of IF conditions. There are various activity types to consider, as well as the different types of revenue. It might be easier to describe this via one of the existing formulas on Sheet #2 ...
=SUMIF('Sheet 2'W:W,"Closed SaleJan",'Sheet 2'!AA:AA)
So, wherever there are cells in Sheet #2 column W that contain "Closed - SaleJan" (concatenation of Status and Month Closed), then provide a sum of all related cells in Sheet #2 column AA (Revenue Type A Value).
Translated: I need the ability to filter data so that I can select the month in question (in this case, January) and determine how many much Type A revenue that was sold. Likewise, I need the ability to determine how much Type B revenue was sold, as well as how much of each type of revenue was quoted ... per whatever month I select as the criteria.
As-is, the previous author of this workbook would need to access it each month, manually find every formula referencing Jan and replace it with Feb, then redistribute it to all users, who will then have to copy and paste all their data from the previous version into the new version in order to keep it up-to-date. I vowed to find a better way!!
I've tried various formulas that consider multiple criteria (IF, SUMIF, etc.), but either that won't work the way I need it to, or else I'm not good at creating multiple arguments - not sure which. I've also toyed around with pivot tables, with which I'm unfamiliar, but I don't know if this will give me the snapshot I'm looking for. I don't want to give the end users something they have to "play with" in order to get the data they need. It has to literally be a snapshot, where the only thing they do is select the month and voila ... all data pertaining to that month is right there.
Sorry so long-winded, just wanted to make sure I covered everything. Any assistance, guidance, advice, suggestions, etc. would be deeply, deeply appreciated. The sooner I can get this resolved, the better. Thanks!!
Sheet #1 (data gathering) contains sales tracking info across multiple columns, as entered by the end user: month assigned, month closed (if sold), activity type, opportunity status, sales person name, product type, revenue type, revenue value, etc. The values in these columns are mainly selected via drop-down menu choices.
Sheet #2 (data reference) contains the info that populates the drop-down menus on Sheet #1. There are also columns on here that concatenate the values of cell selections on Sheet #1 (i.e. month closed + revenue type,), as well as pulling data from Sheet #1 without concatenation (i.e. Revenue Value Type A, Revenue Value Type B). I always like having a separate hidden tab to store this kind of info; less chance that someone will accidentally delete data that is crucial to the workbook's performance.
Sheet #3 (monthly snapshot output) needs to contain a summary of info based on certain conditions, and calculated on a "month ending" (not YTD) basis. It pulls this data from Sheet #2.
For example, Sheet #3 reflects the following: Total revenue quoted (by revenue type), total revenue sold (by type), total count of opportunities quoted, total count of opportunities sold. It's tricky for me because this is not a "grand total" of everything on Sheet #1; it needs to be a monthly snapshot based upon whatever the desired month is.
To further complicate things, this will be based on a lot of IF conditions. There are various activity types to consider, as well as the different types of revenue. It might be easier to describe this via one of the existing formulas on Sheet #2 ...
=SUMIF('Sheet 2'W:W,"Closed SaleJan",'Sheet 2'!AA:AA)
So, wherever there are cells in Sheet #2 column W that contain "Closed - SaleJan" (concatenation of Status and Month Closed), then provide a sum of all related cells in Sheet #2 column AA (Revenue Type A Value).
Translated: I need the ability to filter data so that I can select the month in question (in this case, January) and determine how many much Type A revenue that was sold. Likewise, I need the ability to determine how much Type B revenue was sold, as well as how much of each type of revenue was quoted ... per whatever month I select as the criteria.
As-is, the previous author of this workbook would need to access it each month, manually find every formula referencing Jan and replace it with Feb, then redistribute it to all users, who will then have to copy and paste all their data from the previous version into the new version in order to keep it up-to-date. I vowed to find a better way!!
I've tried various formulas that consider multiple criteria (IF, SUMIF, etc.), but either that won't work the way I need it to, or else I'm not good at creating multiple arguments - not sure which. I've also toyed around with pivot tables, with which I'm unfamiliar, but I don't know if this will give me the snapshot I'm looking for. I don't want to give the end users something they have to "play with" in order to get the data they need. It has to literally be a snapshot, where the only thing they do is select the month and voila ... all data pertaining to that month is right there.
Sorry so long-winded, just wanted to make sure I covered everything. Any assistance, guidance, advice, suggestions, etc. would be deeply, deeply appreciated. The sooner I can get this resolved, the better. Thanks!!
