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!