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!

Look at the Data>Table command.

I do have a description of how it can be used for batch-processing of
complex (multi-sheet) calculations with many inputs and outputs.
If you give me your e-mail address I'll mail you the recipe.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ellens80" <(E-Mail Removed)> wrote in message
news:B1B5BDF0-A98F-4F6D-A800-(E-Mail Removed)...
>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!
>

Hi Niek,

Exploring the questions looking for ideas to make my file easier to process
and see that Ellens may be similiar to mine - at least with size and
complexity. Except mine is too big to use in Access or I'm too
inexperienced. I'm moving past column HW with this report and so far 25+
columns are formula based with a few list box columns (over 400 rows. Ugh! )
In many cases, one column is based on the answer in another. I'm still
trying to work through various scenarios to set up conditional formatting, if
then statements, alerts to time sensitive changes, etc to cover all the
possiblities. I'm not sure what batch processing is, other than using it in
one of our vendor program systems to pull reports. If you wouldn't mind
sharing I would be grateful.

Kathleen

"Niek Otten" wrote:

> Look at the Data>Table command.
>
> I do have a description of how it can be used for batch-processing of
> complex (multi-sheet) calculations with many inputs and outputs.
> If you give me your e-mail address I'll mail you the recipe.
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Ellens80" <(E-Mail Removed)> wrote in message
> news:B1B5BDF0-A98F-4F6D-A800-(E-Mail Removed)...
> >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!
> >
>

Send me your email address

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Kathleen" <(E-Mail Removed)> wrote in message
news:A3B93D73-B2CA-4BDF-8995-(E-Mail Removed)...
> Hi Niek,
>
> Exploring the questions looking for ideas to make my file easier to
> process
> and see that Ellens may be similiar to mine - at least with size and
> complexity. Except mine is too big to use in Access or I'm too
> inexperienced. I'm moving past column HW with this report and so far 25+
> columns are formula based with a few list box columns (over 400 rows.
> Ugh! )
> In many cases, one column is based on the answer in another. I'm still
> trying to work through various scenarios to set up conditional formatting,
> if
> then statements, alerts to time sensitive changes, etc to cover all the
> possiblities. I'm not sure what batch processing is, other than using it
> in
> one of our vendor program systems to pull reports. If you wouldn't mind
> sharing I would be grateful.
>
> Kathleen
>
> "Niek Otten" wrote:
>
>> Look at the Data>Table command.
>>
>> I do have a description of how it can be used for batch-processing of
>> complex (multi-sheet) calculations with many inputs and outputs.
>> If you give me your e-mail address I'll mail you the recipe.
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>> Microsoft MVP - Excel
>>
>> "Ellens80" <(E-Mail Removed)> wrote in message
>> news:B1B5BDF0-A98F-4F6D-A800-(E-Mail Removed)...
>> >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!
>> >
>>

Hi, sorry for the delay in responding. Its (E-Mail Removed).



"Niek Otten" wrote:

> Send me your email address
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Kathleen" <(E-Mail Removed)> wrote in message
> news:A3B93D73-B2CA-4BDF-8995-(E-Mail Removed)...
> > Hi Niek,
> >
> > Exploring the questions looking for ideas to make my file easier to
> > process
> > and see that Ellens may be similiar to mine - at least with size and
> > complexity. Except mine is too big to use in Access or I'm too
> > inexperienced. I'm moving past column HW with this report and so far 25+
> > columns are formula based with a few list box columns (over 400 rows.
> > Ugh! )
> > In many cases, one column is based on the answer in another. I'm still
> > trying to work through various scenarios to set up conditional formatting,
> > if
> > then statements, alerts to time sensitive changes, etc to cover all the
> > possiblities. I'm not sure what batch processing is, other than using it
> > in
> > one of our vendor program systems to pull reports. If you wouldn't mind
> > sharing I would be grateful.
> >
> > Kathleen
> >
> > "Niek Otten" wrote:
> >
> >> Look at the Data>Table command.
> >>
> >> I do have a description of how it can be used for batch-processing of
> >> complex (multi-sheet) calculations with many inputs and outputs.
> >> If you give me your e-mail address I'll mail you the recipe.
> >>
> >> --
> >> Kind regards,
> >>
> >> Niek Otten
> >> Microsoft MVP - Excel
> >>
> >> "Ellens80" <(E-Mail Removed)> wrote in message
> >> news:B1B5BDF0-A98F-4F6D-A800-(E-Mail Removed)...
> >> >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!
> >> >
> >>
>