Automating a Worksheet
in [Functions]
|
From: hankb on 21 Mar 2010 16:01 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?
From: Max on 21 Mar 2010 19:20 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?
From: Ashish Mathur on 21 Mar 2010 22:42 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" <hankb(a)discussions.microsoft.com> wrote in message news:0458CB52-A258-4372-807D-DCF6D08E91C4(a)microsoft.com... > 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?
From: hankb on 22 Mar 2010 17:03 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?
From: Max on 22 Mar 2010 18:53
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 |