Excel challenge

hm...when the user changes the two parameters on one sheet, should they get changed for the other two as well? meaning, are the two parameters being varied the same across the three sheets?

Yeah, thats the idea. So across the three sheets (and in the actual model/calculation) the parameters should change when the user changes them on any one of the three sheets.

hey bud, i'm back. ok, in that case, i think it is easiest to use macros.

VBA will be the easiest way to go about it because you want the parameters to be set by the latest inputs, i.e., in whichever sheet you updated most recently .

someone else help this guy out if they can do it without vba.

Hey, thank you for the input. Unfortunately, the intended recepients can not accept anything with VBA due to security issues.

I guess it comes down to figuring out which cell of a number of cells changed last in a spreadsheet. I am trying to figure that out with circular references right now, but no luck yet.

Anyone with experience doing this?

i'll have a think. when is this due?

i thought of something similar, but assumed OP wants his end-user to not have to "fix" the other sheets when changing the parameters on one. OP asks too much! especially when he disqualifies VBA... if he is wiling to go to other sheets to make adjustments, he might as well visit an input sheet.

Hey guys, thanks for your thoughts. This is due tomorrow by noon, and it's not 100% required to work as described. Someone asked in a meeting if it could be done, and after someone said it's impossible, my VP said that I could probably do it. So I guess it's a bit of a challenge to me.

Anyway, I liked what Downtown described in principle, but practically speaking I think that the binary switches would be more confusing and harder to use than a simple plain input sheet. I am still trying to figure out if I can use circular references (set to 1 iteration, auto calc) to flip those binary switches.

I remember a few years ago I saw an example online of how to use circular references to count the number of times a cell changes. The idea seems straightforward - set a cell (say D5) equal to where the user types over the parameter (say D1) and in D2 put a check to see if D1=D5. When being re-calced, Excel will (hopefully) go down the rows (if I remember correctly, it goes down entire rows when iterating). The user updated D1, triggering the re-calc, but when Excel gets to D2 it will be false, since D5 has not yet been re-calced, so when D2 is false I need to short-circuit a counter cell (D3 for example) to increase it by 1 (hence the 1 iteration). To do so, I set D4 = D3 and D3 = if(D2 = false, D4 + 1, D4). Simple as it sounds, I can't seem to get it to work. Maybe I just need to step back a bit.

Now I can't find where online I saw it, but if I could figure it out then I could probably use that to see which cell was changed last, and refer to it.

Anyways if you guys don't know then don't worry about it, but thanks for your efforts. I thought someone might just happen to remember the trick. That, and I know I always find an Excel puzzle of this sort interesting, so I figured someone on here might enjoy it as well.

Thanks again. -Excel-ignorant monkey.

Another way is to insert a data table on a 4th sheet and then have the output formulas on each of the scenario sheets that utilize the 2 parameters, pull the value from the data table rather than the parameter input cell on the scenario sheet.

In other words, when you change a cell on one of the 3 scenario sheets, the data table cell on the 4th sheet that is used in calculations will take on that value. Since formulas on each scenario sheet point to the global data table cells instead of the scenario sheet input cells, the outputs formulas will "see" the value of the parameter cell that was last changed.

You will need to enable iterative calculations for this to work of course.

Combo boxes... you just select the values from the list, and you just need the numbers to be in one of the sheets, no circular references I use it a lot for scenario analysis

HF, thank you, combo boxes seems to be the right answer. Very easy to set up - just make them all point to the same cell, and they all change values together. Elegant as well, and I don't imagine it slowing anything down as iterative calculation might have. Only issue is discrete inputs, but I think that's acceptable.

Just out of curiousity, in what other circumstance would one use combo boxes? What are they made for? Are they a relic of Excel 95 or something? Seems like an overly complicated way of making dropdowns. I guess they don't occupy a cell though, which might have it's own advantages.

Thanks WSO, you just made my day.