I am converting a product planning sheet from a very manual sheet to a much more automated system.
I have a customer/product matrix setup a little like below, where a '1' indicates if the customer takes the product or not:
<tbody>
</tbody>
I then have on another sheet the customers listed with the products taken across weeks:
<tbody>
</tbody>
At the moment this second table is amended manually, so if a new product is added to a customer new rows have to be added.
*the challenge* What I would like is a way for this sheet to have a formula to add the customers, lookup what products they take based on the matrix, add a line for the total when it has reached the end of the taken products, then move on to the next customer and so on and so forth.
I hope that made sense? If anyone can shed some light on this I would really appreciate it, any other info you need just ask.
Thanks
I have a customer/product matrix setup a little like below, where a '1' indicates if the customer takes the product or not:
Customer 1 | Customer 2 | Customer 3 | Customer 4 | |
Product A | 1 | 1 | 1 | |
Product B | 1 | 1 | 1 | 1 |
Product C | 1 | 1 | ||
Product D | 1 | 1 | 1 |
<tbody>
</tbody>
I then have on another sheet the customers listed with the products taken across weeks:
week 1 | week 2 | week 3 | week 4 | |
Customer 1 | ||||
Product A | ||||
Product B | ||||
Product D | ||||
Customer 1 Total | ||||
Customer 2 | ||||
Product A | ||||
Product B | ||||
Customer 2 Total |
<tbody>
</tbody>
At the moment this second table is amended manually, so if a new product is added to a customer new rows have to be added.
*the challenge* What I would like is a way for this sheet to have a formula to add the customers, lookup what products they take based on the matrix, add a line for the total when it has reached the end of the taken products, then move on to the next customer and so on and so forth.
I hope that made sense? If anyone can shed some light on this I would really appreciate it, any other info you need just ask.
Thanks