Forum Discussion
Scenario manager alternatives?
wumolad Absolutely! Give me a little time to make my details anonymous. Thanks for the quick reply!!!!
wumolad Here we go. Have slimmed it down to 3 'customers' ... you'll get the idea I'm sure.
No hidden tabs but there are some named ranges for which you won't be able to get a list I'm afraid.
Any suggestions/feedback is welcomed. Many thanks.
My suggestion will be to use dropdown list in your formula by inserting from developer tab.
I have put an example on Customer B sheet. This will reduce the so many "if functions" used on Target sales 2020 section on column E having this formula:
=IF($B$11="Tier 1",C14*TargetRevGrowthPC_Tier1,IF($B$11="Tier 2", C14*TargetRevGrowthPC_Tier2,IF($B$11="Tier 3", C14*TargetRevGrowthPC_Tier3,IF($B$11="Tier 4", C14*TargetRevGrowthPC_Tier4,IF($B$11="Tier 5", C14*TargetRevGrowthPC_Tier5,IF($B$11="Tier 6", C14*TargetRevGrowthPC_Tier6))))))
which now shows only:
=INDEX($B$5:$B$9,B32,1)*C14
as seen in cell F34.
You can also copy this dropdown list to any part of the workbook and they are synched together in which case you can make changes from the summary tab and see the impact of changes immediately.
Let me know if you need more clarification on using dropdown.
wumolad Oh my. I can see what your formula/dropdown does and I need to get that clear in my head as definitely easier than all those IF functions (not on Excel 365 to be able to use IFS function) but I'm afraid to admit, I'm not clear on how I could use this on the Summary tab to change the incentive % numbers, thus testing different scenarios if I were to play around with those %s.
Ideally I would like to be able to be on the summary tab to instantly see how overall incentive spend changes if, for example, I drop client A's Tier 2 Incentive % from current 1% to .08% and increase client B's from current .60% to 0.65%, all the while maintaining data integrity to each of their respective budget spreadsheets.
I think I basically set this workbook up round the wrong way - probably best to redo the individual budget spreadsheets and their formulas to pull the incentive % numbers from the summary tab, where I can manipulate them and see instant impact, rather than the current order which is 100% the reverse.
I'm definitely going to play more with those form controls though ... very handy and something cool to learn. Thank you ever so much for your assistance and suggestions - I'm very appreciative of your time.
