dynamic summary report


N

Nicole

i have a detail 20 salespersons' total sales volume on 100 products
for Jan to Nov 2008. How do I create a dynamic summary report to sum
up the total sales volume per sales person for a particular month?
I.e. if i specify January in cell B2, the report will show the sales
person in row under first column and their respective January total
sales volume in second column of the report.If i change the month in
cell B2 to February, the report will show February data and so on.
 
Ad

Advertisements

M

Mike H

Nicole,

We need to know the columns for the salespersons name, the date and the
volumes.

Mike
 
R

Roger Govier

Hi Nicole

Dependent upon the exact layout of your data, the easiest way would be to
use a Pivot Table.
Can you post an example of what your data looks like.

Alternatively, if you want to send me direct, a workbook with some sample
data, I will set up a PT for you.
To mail direct
roger at technology4u dot co uk
Change the at and dots to make a valid email address.
 
N

Nicole

Hi Roger,

I thought of using Pivot table initially but for the reader who
doesn't know excel at all, i was thinking of creating a summary report
with just one click of a button. I will send a section of the file to
yr email and show you what I have done so far but stuck on the
flexibility on the month's data show. Nicole
 
P

Pete_UK

You could do this with a SUMPRODUCT formula, with maybe a drop-down to
select the date, but as Mike said earlier you will need to tell us
what columns you are using for what data.

Pete
 
N

Nicole

In the summary report, i have created a drop down combo box for the
reader to choose which month he wants to read and the cell link of
this drop down box is cell C4. cell B7to B13 is the list of
Salesperson's name and cell C7 to C13 is their respective total sales
volume for the month selected in the drop down box.

For cell C7 to C13, I have use =SUMIF(Data!$B$4:$B$18,$B7,Data!$C$4:$C
$18) where Data!$B$4:$B$18 is the list of Salesperson's name in the
source data, $B7 is a particular salesperson name, and Data!$C$4:$C$18
is the January data to retrieve the data from "Data" sheet. This is
where I got stuck as not sure how to include the month selected
information in the formula to make it into a dynamic function.
Nicole
 
Ad

Advertisements

P

Pete_UK

Hi Nicole,

Could you describe the layout of data in your Data sheet? Do you have
Jan data in column C, Feb data in column D etc?

Does your drop-down give choices like Jan, Feb, Mar, or month numbers
1, 2, 3 etc?

Pete
 
R

Roger Govier

Hi

For the benefit of other respondents and the archives :-

I received a Workbook form Nicole with a subset of her data on a Sheet
called Data.
Nicole has a sheet called Summary Report, with a Combobox dropdown on cell
B4 liked to cell C4
In cells B7:B13 there were names of Salespersons.
The Data sheet contained Product in column A, Salesperson in B and Months
Jan through Nov in C:M

These are the notes I sent back to Nicole with the workbook
to explain the formula I created in cell C8 of her Summary Report

=SUMIF(Salesperson,$B7,Usedata)

I moved your Months lists to a sheet called Setup, as normally your data
will be extending down the sheet called Data.
I also got rid of row 1 (month numbers not necessary) and row 3 (blank row).

I set up some named ranges
lrow = COUNTA(Data!$B$B) as this gives us a count of how many rows of
data we have including the header).

data = Data!$C$2:INDEX(Data!$C:$C,lrow)
This gives the data for the first Month. It is a Dynamic formula, so the
range will grow as more lines are added, and lrow gets increased.

offset = 'Summary Report'!$C$4 just a name for the linked cell to your
dropdown, which gives us the Month number that has been selected.

usedata = OFFSET(data,,offset-1) this will offset the column of data
selected by data, by the number of columns as per the offset value minus 1,
as when the Month is January (1) we don't want an offset, so it must always
be 1 less.
(Alternatively, you could make Offset = $C$4-1)

salesperson = Data!$B$2:INDEX(Data!$B$B,lrow) this gives just the list of
Salesperson names for use in the Sumif formula.

months = Setup!$B$1:$B$12 just a list of the full month names. I added
the year on the end, so you don't get varying spaces between the length of
the month name and your 2008 which was in a separate cell.
Choosing the long month name is now easy. We know the Index number from C4
so INDEX(Months,C4) gives us the value for the heading.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top