dakellafella
New Member
Hi there,
Am relatively new to excel and have the following challenge (please refer to the attachment).
I'm doing a Uni project where I need to conduct a market sizing for 6 products across two categories; BIG and SMALL products. Have developed a basic forecast based on the figures included for calendar year 2017. The tricky part of this forecast is that we have to provide drop down lists that can be toggled based on when the products become available into the market.
I'd like to be able to select any of the drop down lists in B6,B7,B8,B9,B11 and B12, and have the sales for the respective product commence from that date onwards. In otherwords if Product 3 won't be available until March 2017, then I can select the drop down list in B8 and select March 2017. The effect of this would e that January and February figures disappear and only figures from March 2017 to December 2017 would remain for that product.
Be great to know how to code for at least one Product and hopefully I can then drag it across the fields from column E to column P!
The next issue is also related to the question above so if its just as easy to add to the formula please let me know.
If I want to preserve the Grand Total of 24539 (in cell Q12) even if the dates for each Product change, how do I do this? Using the example above if we select March 2017 for Product 3 and we lose the sales for January and February 2017 (a combined amount of 625), I'd like to add 625/10 or 62.5 automatically to the remaining months to keep the product totals (and hence the Grand Total) as is.
Onceagain if any of you know how to do this Excel magic I'd be indebted to you!! (To provide reassurance I've spend the last three nights with my head in you tube trying to understand vlookups embedded in IF statements and using index/row+ and - within arrays to make it work but its done my head in).
Thank you in advance,
Marcus
Am relatively new to excel and have the following challenge (please refer to the attachment).
I'm doing a Uni project where I need to conduct a market sizing for 6 products across two categories; BIG and SMALL products. Have developed a basic forecast based on the figures included for calendar year 2017. The tricky part of this forecast is that we have to provide drop down lists that can be toggled based on when the products become available into the market.
I'd like to be able to select any of the drop down lists in B6,B7,B8,B9,B11 and B12, and have the sales for the respective product commence from that date onwards. In otherwords if Product 3 won't be available until March 2017, then I can select the drop down list in B8 and select March 2017. The effect of this would e that January and February figures disappear and only figures from March 2017 to December 2017 would remain for that product.
Be great to know how to code for at least one Product and hopefully I can then drag it across the fields from column E to column P!
The next issue is also related to the question above so if its just as easy to add to the formula please let me know.
If I want to preserve the Grand Total of 24539 (in cell Q12) even if the dates for each Product change, how do I do this? Using the example above if we select March 2017 for Product 3 and we lose the sales for January and February 2017 (a combined amount of 625), I'd like to add 625/10 or 62.5 automatically to the remaining months to keep the product totals (and hence the Grand Total) as is.
Onceagain if any of you know how to do this Excel magic I'd be indebted to you!! (To provide reassurance I've spend the last three nights with my head in you tube trying to understand vlookups embedded in IF statements and using index/row+ and - within arrays to make it work but its done my head in).
Thank you in advance,
Marcus
