E
Ellens80
I don’t know if there is an Excel or Access solution to my problem.
The issue is that I have a list of 80 clients I need to calculate the costs
for; each client can have 4 programs they’re involved in. To get the cost, I
need to use a very complicated formula that takes input # (# people) and
multiplies it by different costs over several steps; I have a great template
set up to calculate the cost.
The problem is that the use of the template is manual – I need to enter the
client’s #s for the 4 programs into the template, then paste the $ values
back into the client spreadsheet. In addition, I now need to do multiple
scenarios for each client, so that’s 80 sets of program input #s times 3 or 4
scenarios.
So I’m trying to see if there’s a way to automate this process.
What _can’t_ be done is to put list of clients & their input # (#
people/program) on one tab, and formula on another, and drag down the
formula, then link input and output cells in each tab. This can’t be done
because the formula is much too complicated.
The only solution I can think of now is to create 81 tabs: 1 master vendor
tab, and then 1 template (formula calculation) tab for each vendor. Then
link cells in master vendor tab to the input/output cells in that vendor’s
tab. I would need to put 3 – 4 templates on each child tab, to allow for
multiple scenarios.
Pro’s: it’s doable.
Con’s: it’s laborious to set up, but can be done; it doesn’t allow
flexibility—I’m locked into using Column E for first scenario, Column M for
second scenario, etc. (I mean, I can add columns, so M becomes N—and N will
be the one linked to template; but I can’t move scenarios around easily on
the page—can’t risk losing the link; if I decide I want to make Scenario 3
into Scenario 2, I can certainly move those columns—but then it’s illogical
that the second scenario is connected to the third template—that might screw
things up later if I don’t remember that fact and try to “fix†things; so
it’s just big and unwieldy to use); it makes a big file
Is there a smarter way to do this? –using either Excel or my throwing
together a simple database in Access. I feel like there’s a brilliant
solution I’m just unaware of—that I hope one of you has come across in the
past!
The issue is that I have a list of 80 clients I need to calculate the costs
for; each client can have 4 programs they’re involved in. To get the cost, I
need to use a very complicated formula that takes input # (# people) and
multiplies it by different costs over several steps; I have a great template
set up to calculate the cost.
The problem is that the use of the template is manual – I need to enter the
client’s #s for the 4 programs into the template, then paste the $ values
back into the client spreadsheet. In addition, I now need to do multiple
scenarios for each client, so that’s 80 sets of program input #s times 3 or 4
scenarios.
So I’m trying to see if there’s a way to automate this process.
What _can’t_ be done is to put list of clients & their input # (#
people/program) on one tab, and formula on another, and drag down the
formula, then link input and output cells in each tab. This can’t be done
because the formula is much too complicated.
The only solution I can think of now is to create 81 tabs: 1 master vendor
tab, and then 1 template (formula calculation) tab for each vendor. Then
link cells in master vendor tab to the input/output cells in that vendor’s
tab. I would need to put 3 – 4 templates on each child tab, to allow for
multiple scenarios.
Pro’s: it’s doable.
Con’s: it’s laborious to set up, but can be done; it doesn’t allow
flexibility—I’m locked into using Column E for first scenario, Column M for
second scenario, etc. (I mean, I can add columns, so M becomes N—and N will
be the one linked to template; but I can’t move scenarios around easily on
the page—can’t risk losing the link; if I decide I want to make Scenario 3
into Scenario 2, I can certainly move those columns—but then it’s illogical
that the second scenario is connected to the third template—that might screw
things up later if I don’t remember that fact and try to “fix†things; so
it’s just big and unwieldy to use); it makes a big file
Is there a smarter way to do this? –using either Excel or my throwing
together a simple database in Access. I feel like there’s a brilliant
solution I’m just unaware of—that I hope one of you has come across in the
past!