Good morning…
Let's see if I can explain this coherently :)
1. We're pulling the dealer sales amounts per quarter and want to list it ranked. (I.E. From highest sales to lowest sales)
2. In a second set of cells, we're doing an excel calculation to show the percentage of total sales that that dealer generates.
3. We'd like to do this for each of our Reps.
So…Rep 1:
Cells C3 to C6: Individual dealer sales amounts
Cells D3 to D6: % of total sales based on data in C3 to C6
Rep 2:
Cells C12 to C14: Individual…
Cells D12 to D14: % of total…
etc.
Where we're running into problems is that in the D column, if we don't use absolute excel values…the report moves the cells down (In cell D3, the calculation uses range C3 to C6. In cell D4, that calculation uses range D4 to D7 which causes issues). If we put in absolute values, Jet always references C3 to C6 even where with Rep 2, it should reference C12 to C14.
Edit: So the question I seem to have forgotten to actually ask is -> Is there a way through Jet or Excel or combined where we can have the proper cells referenced as the report gets generated?
This one has both me and my colleague stumped and not sure how to proceed.
Date
Votes
2 comments
-
Jet Reports Historic Posts There is two ways I would tackle this. One would be in the percentage column where I would do a calculation. You can do a calculations of Jet functions so you could use take the value of the individual dealership as a cell reference and divide it by an NL that will get the sum of all of the dealership sales. The problem with that is it will do that calculation each time which may slow down the report.
Another way is to have the Total Sales as a Jet Calculation up in the top of the report and use that for the calculation and absolute reference to that cell. This will be faster since it will do that calculation one time to get all dealership totals and then reference that value in the individual calculations.
Hopefully that explains it clearly, but I also attached a mock-up. -
Jet Reports Historic Posts In looking at my mockup, in sheet 2, all of the divisors should be $D$3 (Excel changed the number as I copied it down), but you always want to divide by the value in $D$3.
Please sign in to leave a comment.