R
Rebecca
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!
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!