need some advices/ideas
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.
|