Hi

I have a problem that i need some ideas or advice on how to solve it.

The problem =

I have a list of products, qty & their unique id. I need to find the
total
for each product & display the result on a summary worksheet.

I have my raw data containing product information & unique id on
worksheet "raw".
On the summary page, i have set 2 cells with validation list of the
product name & month.
I want to allow the user to be able to choose the product thery want
to & then display the
product information & their sum.

I have 25 different products with a few products having different
cacapity.

Do i list all the 25 different products in the summary page & fixed
the cell so that for each different product , i have a macro that
will calculate the sum & output to the corresponding
cell on the summary page?

Then if in the future, i need to add im new products, i will need to
create new macro for the new
products, is there some way which i can automate this so that i have a
general macro to calculate the total for different product base on the
2 cells with validation list & then write the
answer back to the correct corresponding cell?

Thank you for reading

I can do it manually but i would like to automate it as more new
products will be added in the
future.

The best way IMO would be to have a formula that creates a list of unique
products from the raw data sheet, and then create data validation in the
summary sheet linking back to the product list. Months you can create a
simple 12 item list.

To get the sums, you can then use a formula like

=SUMPRODUCT(--('Raw data'!$A$2:$A$200=C2),--(TEXT('Raw
data'!$B$2:$B$200,"mmm")=D2),'Raw data'!$C$2:$C$200)

where on Raw data column A is the product, B the date, C the amount, and C2
is the product dropdown, D2 is the month dropdown. C2 and D2 can be extended
down, as can the formula.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"kiwis" wrote in message
oups.com...
Hi

I have a problem that i need some ideas or advice on how to solve it.

The problem =

I have a list of products, qty & their unique id. I need to find the
total
for each product & display the result on a summary worksheet.

I have my raw data containing product information & unique id on
worksheet "raw".
On the summary page, i have set 2 cells with validation list of the
product name & month.
I want to allow the user to be able to choose the product thery want
to & then display the
product information & their sum.

I have 25 different products with a few products having different
cacapity.

Do i list all the 25 different products in the summary page & fixed
the cell so that for each different product , i have a macro that
will calculate the sum & output to the corresponding
cell on the summary page?

Then if in the future, i need to add im new products, i will need to
create new macro for the new
products, is there some way which i can automate this so that i have a
general macro to calculate the total for different product base on the
2 cells with validation list & then write the
answer back to the correct corresponding cell?

Thank you for reading

I can do it manually but i would like to automate it as more new
products will be added in the
future.

Hi Bob

Thank you for your reply.

I have simplifed my problem,so i will be more detailed here.

I have raw information for 12 months in the worksheet "raw".
I have written a marco to split the data into the respective months,
so i have 12 worksheets for each month
containing raw information for that month.

I have created a summary worksheet with 3 cells with data validation -
month, product, capcity size.

On the summary page below the 3 cells with data validation, i have
listed all the products.
I am doing it manually by summing up a product using autofilter &
subtotal to get the sum
& then write it back to the corresponding cell.

I am looking for some idea/advice so that i can automate the process
such that
i can select the product, size & the month then i can get the sum & it
would be best
if it can auto copy the result into the correct corresponding cell.

for eg,

the 3 cells with data validation, i choose Jan, product a, 2GB
the on the summary page , i have this row = product a , 2GB ,
so i want to automate it such that the sum product a will be written
back to the next cell in this row.

I have all the products listed on the summary page below the 3 cells
with data validation.

I hope this is clear, i' m not sure abt your idea.

Thank you for your reply.Hope you can provide more ideas.


On May 15, 4:34 pm, "Bob Phillips" wrote:
The best way IMO would be to have a formula that creates a list of unique
products from the raw data sheet, and then create data validation in the
summary sheet linking back to the product list. Months you can create a
simple 12 item list.

To get the sums, you can then use a formula like

=SUMPRODUCT(--('Raw data'!$A$2:$A$200=C2),--(TEXT('Raw
data'!$B$2:$B$200,"mmm")=D2),'Raw data'!$C$2:$C$200)

where on Raw data column A is the product, B the date, C the amount, and C2
is the product dropdown, D2 is the month dropdown. C2 and D2 can be extended
down, as can the formula.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"kiwis" wrote in message

oups.com...



Hi

I have a problem that i need some ideas or advice on how to solve it.

The problem =

I have a list of products, qty & their unique id. I need to find the
total
for each product & display the result on a summary worksheet.

I have my raw data containing product information & unique id on
worksheet "raw".
On the summary page, i have set 2 cells with validation list of the
product name & month.
I want to allow the user to be able to choose the product thery want
to & then display the
product information & their sum.

I have 25 different products with a few products having different
cacapity.

Do i list all the 25 different products in the summary page & fixed
the cell so that for each different product , i have a macro that
will calculate the sum & output to the corresponding
cell on the summary page?

Then if in the future, i need to add im new products, i will need to
create new macro for the new
products, is there some way which i can automate this so that i have a
general macro to calculate the total for different product base on the
2 cells with validation list & then write the
answer back to the correct corresponding cell?

Thank you for reading

I can do it manually but i would like to automate it as more new
products will be added in the
future.- Hide quoted text -

- Show quoted text -