If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|