If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
linking to formula template on different tab
I don't know if I can do this in Excel, but thought it worth asking.
I have a list of 80 clients. I need to know the cost for them, which is a complicated, multi-step calculation. I enter 4 input #s into the calculation (50, 17, 0, 23), and out pops the results: $22,123, $33,123, 0, $99,123. (I created the multi-step calculation formula, btw) However, the #s for the clients change regularly. So now I need to enter my new set of #s (45, 17, 2, 28) in order to get the updated cost figures. And I need to do this for all 80 clients, every time. In addition, I often need to prepare multiple scenarios at one time, which means entering multiple input sets, x 80 clients. Is there an easier way to do this? --can I link the output cell on my Master (summary) page to the formula on a separate tab? The only way I can think of doing this is by having 80 tabs (or one sheet with 80 sets of calculation templates), and linking the output cells to the result cells in those 80 tabs. But first--that's awfully big. And second, it doesn't help me if I need to do multiple scenarios (I'd need 3 calculation templates on each of the tabs, in case I need to do 3 scenarios). The calculation is much too complicated to reduce to an equation in my Master summary sheet; it involves taking my input # (50), multiplying it by ($X * 20%) + ($X x 80%), then adding (50 *.8) * ($Y * 20%) + (50 *.8) * ($Y * 80%), and so on for 2 more iterations. So does anyone have any thoughts on an easier way to do this? Should I try to build something in Access...? The ideal function would be for me to link each of 80 sets (of 4) input cells in the Master tab to the same calculation template, such that it returns a hard # to the output cells in the Master tab (i.e., hard entered, so that when the next input # is entered into the cell below, for the next client, the output for the client above remains the same). I'd then need to hit "run calc" once, and it would update all the output cells at one time (or consecutively). Is this wildly crazy idea at all possible? Thank you! |
Thread Tools | |
Display Modes | |
|
|