Automating a Worksheet
 

I have a lengthy worksheet which requires a single numerical input on A1 and
it produces a single numerical output on F30. It has numerous formulas.

I would like to apply this operation to a series of input data listed on,
say, cells M1 to M20 and to produce related results on cells N1 to N20.

Is there a simple way to achieve this?

Automating a Worksheet
 

A "One variable data table" should do this for you
To get a quick n easy hang of using it/setting it up,
here's a link to a blast from my past archives:
http://cjoint.com/?dwamPHboMz
One_variable_data_table_example.xls
Inspiring? Hit the YES below
--
Max
Singapore
---
"hankb" wrote:
I have a lengthy worksheet which requires a single numerical input on A1 and
it produces a single numerical output on F30. It has numerous formulas.

I would like to apply this operation to a series of input data listed on,
say, cells M1 to M20 and to produce related results on cells N1 to N20.

Is there a simple way to achieve this?

Automating a Worksheet
 

Hi,

Upload the workbook to www.mediafire.com and then post a link to that
workbook in the body of the message

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"hankb" wrote in message
...
I have a lengthy worksheet which requires a single numerical input on A1
and
it produces a single numerical output on F30. It has numerous formulas.

I would like to apply this operation to a series of input data listed on,
say, cells M1 to M20 and to produce related results on cells N1 to N20.

Is there a simple way to achieve this?

Automating a Worksheet
 

Thank you Max. Your solution assumes a direct mathematical relationship
between the input A1 and the output F30. There isn't. It is a long
cumbersome set of relationships and functions to derive the output F30.
These involve additions, subtractions, multiplications by constants etc.
You are right, in that I want to achieve one variable input, with a
corresponding set of resultant outputs.
hankb

"Max" wrote:

A "One variable data table" should do this for you
To get a quick n easy hang of using it/setting it up,
here's a link to a blast from my past archives:
http://cjoint.com/?dwamPHboMz
One_variable_data_table_example.xls
Inspiring? Hit the YES below
--
Max
Singapore
---
"hankb" wrote:
I have a lengthy worksheet which requires a single numerical input on A1 and
it produces a single numerical output on F30. It has numerous formulas.

I would like to apply this operation to a series of input data listed on,
say, cells M1 to M20 and to produce related results on cells N1 to N20.

Is there a simple way to achieve this?

Automating a Worksheet
 

That's the underlying principle and it should work fine. As long as o/p F30
varies dependent on i/p A1, no matter how complex the relationship in-between
is, Data Table will do it for you. Exactly what kind of errors were you
getting? I recollect only an implicit subtlety in that the data table needs
to be set-up on the *same sheet* as the source o/p formula and/or source i/p.
If you need to reflect the results elsewhere, just use simple links pointing
to the relevant cells in the table. Give it another go?
--
Max
Singapore
---
"hankb" wrote:
Thank you Max. Your solution assumes a direct mathematical relationship
between the input A1 and the output F30. There isn't. It is a long
cumbersome set of relationships and functions to derive the output F30.
These involve additions, subtractions, multiplications by constants etc.
You are right, in that I want to achieve one variable input, with a
corresponding set of resultant outputs.
hankb

Automating a Worksheet
 

Max,
Thanks for your persistence. I got it to work just fine. Really do
appreciate your help.
hankb

"Max" wrote:

That's the underlying principle and it should work fine. As long as o/p F30
varies dependent on i/p A1, no matter how complex the relationship in-between
is, Data Table will do it for you. Exactly what kind of errors were you
getting? I recollect only an implicit subtlety in that the data table needs
to be set-up on the *same sheet* as the source o/p formula and/or source i/p.
If you need to reflect the results elsewhere, just use simple links pointing
to the relevant cells in the table. Give it another go?
--
Max
Singapore
---
"hankb" wrote:
Thank you Max. Your solution assumes a direct mathematical relationship
between the input A1 and the output F30. There isn't. It is a long
cumbersome set of relationships and functions to derive the output F30.
These involve additions, subtractions, multiplications by constants etc.
You are right, in that I want to achieve one variable input, with a
corresponding set of resultant outputs.
hankb

Automating a Worksheet
 

Glad to hear. Do take a moment to hit the YES below
--
Max
Singapore
---
"hankb" wrote:
Max,
Thanks for your persistence. I got it to work just fine. Really do
appreciate your help.
hankb