shstrating
Board Regular
- Joined
- Sep 8, 2009
- Messages
- 61
- Office Version
- 365
- Platform
- Windows
For some reason I cannot get my head wrapped around what I need to do to make this happen, and after 3 fruitless hours of researching innumerable posts in this forum I have to resort to posting my question because I cannot figure out how to make the answers already supplied here work.
Software: WinXP SP2 and Excel 2003 SP3
I have a workbook consisting of one worksheet on which I am performing many calculations. These calculations pull data from the remaining worksheets in the workbook, all of which are the result of CSV data dumps from a Customer Relationship Management(CRM) system. These data worksheets are called things like Sales Calls, Referrals, New Opptys and consist of both alpha and numeric data. These data dumps are performed at least monthly, if not more often, and the number of records changes regularly(at least it had better or else the Sales Reps are going to have to answer some tough questions as to why they aren't making sales related calls ). There is no guarantee that the data fields on a given worksheet are fully populated with values; in other words there could be blank cells and they could be anywhere.
On my calculation worksheet (Calc) I am using various =SUM(IF...) and =SUMPRODUCT(...) formulas to arrive at totals for how each Sales Rep is performing as well as how each affiliate office is performing (made up of one or more Sales Reps). Currently I am 'hard-coding' the ranges into the SUM(IF...) and SUMPRODUCT(...) formulas, but as you can imagine that becomes a maintenance nightmare because they have to be updated every time a new data dump is performed (or at least checked to see if the size of the range changed).
The way the data dumps are performed is that the someone exports the CSV file out of the CRM system and directly into an Excel file. This person then opens the Excel file and performs a simple Copy All and the data is pasted into my workbook, overwriting the existing data on the appropriate worksheet. This is very manual, but it's the way they want it done and as long as I don't have to do it I don't care.
The number of columns should stay consistent from dump to dump while the number of rows changes. I say the columns "should" stay consistent because I assume it is possible someone may structure their query a bit differently in CRM and end up with more or less columns. If it's possible to account for different numbers of rows AND columns then that would be ideal. (Heaven forbid they export the files with a different column order because then I'm royally screwed and someone will be getting a visit from my very large and angry friends Bruno and Vinnie.)
What I am trying to figure out is how to build my SUM(IF...) and SUMPRODUCT(...) formulas such that they properly detect and account for the flucuating data ranges that are being regularly pasted into the Sales Calls, Referrals, etc. worksheets.
An example of one of my formulas (located on the Calc sheet in cell F4) is:
{=SUM(IF('Sales Calls'!$E$2:$E$4136=$A4,'Sales Calls'!$J$2:$J$4136,0))}
where:
$A4 contains a Sales Rep's name
'Sales Calls'!$E2:$E4136 contains a list of all Sales Reps' names
'Sales Calls'!$J2:$J4136 contains a tally of sales calls made by each Sales Rep
I want to make the E:E and J:J ranges dynamic to match the ever changing data.
I know I'm making this way harder than it is, and I'm hoping someone can help me get a grip on what I need to do.
Thanks for whatever guidance you can provide.
Steve
Software: WinXP SP2 and Excel 2003 SP3
I have a workbook consisting of one worksheet on which I am performing many calculations. These calculations pull data from the remaining worksheets in the workbook, all of which are the result of CSV data dumps from a Customer Relationship Management(CRM) system. These data worksheets are called things like Sales Calls, Referrals, New Opptys and consist of both alpha and numeric data. These data dumps are performed at least monthly, if not more often, and the number of records changes regularly(at least it had better or else the Sales Reps are going to have to answer some tough questions as to why they aren't making sales related calls ). There is no guarantee that the data fields on a given worksheet are fully populated with values; in other words there could be blank cells and they could be anywhere.
On my calculation worksheet (Calc) I am using various =SUM(IF...) and =SUMPRODUCT(...) formulas to arrive at totals for how each Sales Rep is performing as well as how each affiliate office is performing (made up of one or more Sales Reps). Currently I am 'hard-coding' the ranges into the SUM(IF...) and SUMPRODUCT(...) formulas, but as you can imagine that becomes a maintenance nightmare because they have to be updated every time a new data dump is performed (or at least checked to see if the size of the range changed).
The way the data dumps are performed is that the someone exports the CSV file out of the CRM system and directly into an Excel file. This person then opens the Excel file and performs a simple Copy All and the data is pasted into my workbook, overwriting the existing data on the appropriate worksheet. This is very manual, but it's the way they want it done and as long as I don't have to do it I don't care.
The number of columns should stay consistent from dump to dump while the number of rows changes. I say the columns "should" stay consistent because I assume it is possible someone may structure their query a bit differently in CRM and end up with more or less columns. If it's possible to account for different numbers of rows AND columns then that would be ideal. (Heaven forbid they export the files with a different column order because then I'm royally screwed and someone will be getting a visit from my very large and angry friends Bruno and Vinnie.)
What I am trying to figure out is how to build my SUM(IF...) and SUMPRODUCT(...) formulas such that they properly detect and account for the flucuating data ranges that are being regularly pasted into the Sales Calls, Referrals, etc. worksheets.
An example of one of my formulas (located on the Calc sheet in cell F4) is:
{=SUM(IF('Sales Calls'!$E$2:$E$4136=$A4,'Sales Calls'!$J$2:$J$4136,0))}
where:
$A4 contains a Sales Rep's name
'Sales Calls'!$E2:$E4136 contains a list of all Sales Reps' names
'Sales Calls'!$J2:$J4136 contains a tally of sales calls made by each Sales Rep
I want to make the E:E and J:J ranges dynamic to match the ever changing data.
I know I'm making this way harder than it is, and I'm hoping someone can help me get a grip on what I need to do.
Thanks for whatever guidance you can provide.
Steve