Inserting a value based on a calculation from another sheet

[amir.companieh]

Hi all
I have a sheet that performs a series of calculations based on a
single value and culminating in another single value. Also the series
of calculations is too complicated and long and numerous to do in a
single calculation. What I have is, in another sheet, I have a range
of that single initial value, from -90% to +90% of the initial value,
and I'd like to somehow calculate that value resulting from all the
various initial values and insert them in the first sheet...how can i
do that?

for example: sheet A contains the range of initial values and the
result of the calculations performed in sheet B

Percentage Initial Value Result
-90% 10
-80% 20
.... ....
0% 100 300
10% 110
20% 120
.... ...
90% 190

sheet B: a series of calculations based on the initial value. Ex:
initial value is set to 100 and results after a long series of
calculations is 300...

Now i'd like to assign something to the -90% row of the Result column
in sheet A such that it would (in the background?) insert 10 into the
initial value of sheet B, do the calculations, and return that final
value to that locations...any ideas?

 

[Niek Otten]

Use the Data>Table command. Look in Help for instructions.
It is a bit of careful work to set up, but it works perfectly.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hi all
| I have a sheet that performs a series of calculations based on a
| single value and culminating in another single value. Also the series
| of calculations is too complicated and long and numerous to do in a
| single calculation. What I have is, in another sheet, I have a range
| of that single initial value, from -90% to +90% of the initial value,
| and I'd like to somehow calculate that value resulting from all the
| various initial values and insert them in the first sheet...how can i
| do that?
|
| for example: sheet A contains the range of initial values and the
| result of the calculations performed in sheet B
|
| Percentage Initial Value Result
| -90% 10
| -80% 20
| ... ....
| 0% 100 300
| 10% 110
| 20% 120
| ... ...
| 90% 190
|
| sheet B: a series of calculations based on the initial value. Ex:
| initial value is set to 100 and results after a long series of
| calculations is 300...
|
| Now i'd like to assign something to the -90% row of the Result column
| in sheet A such that it would (in the background?) insert 10 into the
| initial value of sheet B, do the calculations, and return that final
| value to that locations...any ideas?

 

[amir.companieh]

Hi Niek,
Do you know if all the values, formulas, calculations need to be in
the same sheet? I get an input reference value invalid error and i've
read some posts on other websites that indicated everything needed to
be in one sheet...is that true? that seems awfully messy...is there a
workaround? in my particular case, there is a sheet A that contains
the value I'm trying sweep through, a sheet B that has the
calculations by referencing that value, and a sheet C that i've put in
that has in one column the range of values i'm trying to sweep thru in
one column, and in the adjacent column, the formula (this is the
layout recommended in the help)..any ideas what i'm doing wrong to get
that error?

 

[Niek Otten]

I have an article describing how to do this, but it's a bit lengthy so I'll mail you privately, if you don't mind.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Niek,
| Do you know if all the values, formulas, calculations need to be in
| the same sheet? I get an input reference value invalid error and i've
| read some posts on other websites that indicated everything needed to
| be in one sheet...is that true? that seems awfully messy...is there a
| workaround? in my particular case, there is a sheet A that contains
| the value I'm trying sweep through, a sheet B that has the
| calculations by referencing that value, and a sheet C that i've put in
| that has in one column the range of values i'm trying to sweep thru in
| one column, and in the adjacent column, the formula (this is the
| layout recommended in the help)..any ideas what i'm doing wrong to get
| that error?
|
| > Use the Data>Table command. Look in Help for instructions.
| > It is a bit of careful work to set up, but it works perfectly.
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > | Hi all
| > | I have a sheet that performs a series of calculations based on a
| > | single value and culminating in another single value. Also the series
| > | of calculations is too complicated and long and numerous to do in a
| > | single calculation. What I have is, in another sheet, I have a range
| > | of that single initial value, from -90% to +90% of the initial value,
| > | and I'd like to somehow calculate that value resulting from all the
| > | various initial values and insert them in the first sheet...how can i
| > | do that?
| > |
| > | for example: sheet A contains the range of initial values and the
| > | result of the calculations performed in sheet B
| > |
| > | Percentage Initial Value Result
| > | -90% 10
| > | -80% 20
| > | ... ....
| > | 0% 100 300
| > | 10% 110
| > | 20% 120
| > | ... ...
| > | 90% 190
| > |
| > | sheet B: a series of calculations based on the initial value. Ex:
| > | initial value is set to 100 and results after a long series of
| > | calculations is 300...
| > |
| > | Now i'd like to assign something to the -90% row of the Result column
| > | in sheet A such that it would (in the background?) insert 10 into the
| > | initial value of sheet B, do the calculations, and return that final
| > | value to that locations...any ideas?
|