automating use of complicated formula
 

I dont 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 theyre 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
clients #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 thats 80 sets of program input #s times 3 or 4
scenarios.

So Im trying to see if theres a way to automate this process.

What _cant_ 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 cant 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 vendors
tab. I would need to put 3 €“ 4 templates on each child tab, to allow for
multiple scenarios.

Pros: its doable.
Cons: its laborious to set up, but can be done; it doesnt allow
flexibility€”Im 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 cant move scenarios around easily on
the page€”cant risk losing the link; if I decide I want to make Scenario 3
into Scenario 2, I can certainly move those columns€”but then its illogical
that the second scenario is connected to the third template€”that might screw
things up later if I dont remember that fact and try to €œfix€� things; so
its 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 theres a brilliant
solution Im just unaware of€”that I hope one of you has come across in the
past!

automating use of complicated formula
 

Look at the DataTable 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" wrote in message
...
I dont 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 theyre 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
clients #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 thats 80 sets of program input #s times 3
or 4
scenarios.

So Im trying to see if theres a way to automate this process.

What _cant_ 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 cant 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 vendors
tab. I would need to put 3 €“ 4 templates on each child tab, to allow for
multiple scenarios.

Pros: its doable.
Cons: its laborious to set up, but can be done; it doesnt allow
flexibility€”Im 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 cant move scenarios around easily on
the page€”cant risk losing the link; if I decide I want to make Scenario 3
into Scenario 2, I can certainly move those columns€”but then its
illogical
that the second scenario is connected to the third template€”that might
screw
things up later if I dont remember that fact and try to €œfix€� things; so
its 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 theres a brilliant
solution Im just unaware of€”that I hope one of you has come across in the
past!

automating use of complicated formula
 

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 DataTable 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" wrote in message
...
I dont 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 theyre 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
clients #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 thats 80 sets of program input #s times 3
or 4
scenarios.

So Im trying to see if theres a way to automate this process.

What _cant_ 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 cant 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 vendors
tab. I would need to put 3 €“ 4 templates on each child tab, to allow for
multiple scenarios.

Pros: its doable.
Cons: its laborious to set up, but can be done; it doesnt allow
flexibility€”Im 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 cant move scenarios around easily on
the page€”cant risk losing the link; if I decide I want to make Scenario 3
into Scenario 2, I can certainly move those columns€”but then its
illogical
that the second scenario is connected to the third template€”that might
screw
things up later if I dont remember that fact and try to €œfix€� things; so
its 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 theres a brilliant
solution Im just unaware of€”that I hope one of you has come across in the
past!

automating use of complicated formula
 

Send me your email address

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Kathleen" wrote in message
...
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 DataTable 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" wrote in message
...
I dont 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 theyre 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
clients #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 thats 80 sets of program input #s times
3
or 4
scenarios.

So Im trying to see if theres a way to automate this process.

What _cant_ 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 cant 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
vendors
tab. I would need to put 3 €“ 4 templates on each child tab, to allow
for
multiple scenarios.

Pros: its doable.
Cons: its laborious to set up, but can be done; it doesnt allow
flexibility€”Im 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 cant move scenarios around easily
on
the page€”cant risk losing the link; if I decide I want to make
Scenario 3
into Scenario 2, I can certainly move those columns€”but then its
illogical
that the second scenario is connected to the third template€”that might
screw
things up later if I dont remember that fact and try to €œfix€� things;
so
its 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 theres a brilliant
solution Im just unaware of€”that I hope one of you has come across in
the
past!

automating use of complicated formula
 

Hi, sorry for the delay in responding. Its .



"Niek Otten" wrote:

Send me your email address

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Kathleen" wrote in message
...
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 DataTable 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" wrote in message
...
I dont 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 theyre 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
clients #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 thats 80 sets of program input #s times
3
or 4
scenarios.

So Im trying to see if theres a way to automate this process.

What _cant_ 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 cant 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
vendors
tab. I would need to put 3 €“ 4 templates on each child tab, to allow
for
multiple scenarios.

Pros: its doable.
Cons: its laborious to set up, but can be done; it doesnt allow
flexibility€”Im 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 cant move scenarios around easily
on
the page€”cant risk losing the link; if I decide I want to make
Scenario 3
into Scenario 2, I can certainly move those columns€”but then its
illogical
that the second scenario is connected to the third template€”that might
screw
things up later if I dont remember that fact and try to €œfix€� things;
so
its 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 theres a brilliant
solution Im just unaware of€”that I hope one of you has come across in
the
past!