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.

Nicole,

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

Mike

"Nicole" wrote:

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.

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.

--
Regards
Roger Govier

"Nicole" wrote in message
...
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.

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






On Dec 8, 6:37*pm, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
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.

--
Regards
Roger Govier

"Nicole" wrote in message

...



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.- Hide quoted text -

- Show quoted text -

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

On Dec 8, 12:36*pm, Nicole wrote:
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

On Dec 8, 6:37*pm, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:



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.

--
Regards
Roger Govier

"Nicole" wrote in message

...

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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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


On Dec 8, 8:43*pm, Pete_UK wrote:
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

On Dec 8, 12:36*pm, Nicole wrote:



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

On Dec 8, 6:37*pm, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:

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.

--
Regards
Roger Govier

"Nicole" wrote in message

....

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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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

On Dec 8, 1:05*pm, Nicole wrote:
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

On Dec 8, 8:43*pm, Pete_UK wrote:



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

On Dec 8, 12:36*pm, Nicole wrote:

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

On Dec 8, 6:37*pm, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:

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.

--
Regards
Roger Govier

"Nicole" wrote in message

...

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..- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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.

--
Regards
Roger Govier

"Pete_UK" wrote in message
...
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

On Dec 8, 1:05 pm, Nicole wrote:
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

On Dec 8, 8:43 pm, Pete_UK wrote:



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

On Dec 8, 12:36 pm, Nicole wrote:

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

On Dec 8, 6:37 pm, "Roger Govier"
<roger@technology4unospamdotcodotuk
wrote:

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.

--
Regards
Roger Govier

"Nicole" wrote in message

...

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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Thanks for feeding back, Roger.

Pete

On Dec 8, 4:47*pm, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
Hi

For the benefit of other respondents and the archives :-

Hi Pete

Looking forward to meeting up with you at the Excel User Conference in
London next April.
I see you have signed up for both days.

http://excelusergroup.org/blogs/nick...onference.aspx

--
Regards
Roger Govier

"Pete_UK" wrote in message
...
Thanks for feeding back, Roger.

Pete

On Dec 8, 4:47 pm, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
Hi

For the benefit of other respondents and the archives :-

On Dec 10, 5:35*am, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:
Hi Pete

Looking forward to meeting up with you at the Excel User Conference in
London next April.
I see you have signed up for both days.

http://excelusergroup.org/blogs/nick...12/05/uk-excel...

--
Regards
Roger Govier

"Pete_UK" wrote in message

...



Thanks for feeding back, Roger.

Pete

On Dec 8, 4:47 pm, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
Hi

For the benefit of other respondents and the archives :-- Hide quoted text -

- Show quoted text -
My problem is now solved, many thanks to Roger, really appreciate it.

Hi Roger,

likewise from me - I'll be wearing the pink carnation !! <bg

Pete

On Dec 9, 9:35*pm, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
Hi Pete

Looking forward to meeting up with you at the Excel User Conference in
London next April.
I see you have signed up for both days.

http://excelusergroup.org/blogs/nick...12/05/uk-excel...

--
Regards
Roger Govier

"Pete_UK" wrote in message

...



Thanks for feeding back, Roger.

Pete

On Dec 8, 4:47 pm, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
Hi

For the benefit of other respondents and the archives :-- Hide quoted text -

- Show quoted text -