i've build a model in excel.
on the basis of a few variables i made quite a number of sheets. one
variable i had to keep constant, otherwise i would need 3d tables. now that
the model is almost done, this one variable needs to change.
it needs to take discrete values between 0 and 5 (for example, and in steps
of .25) the change of this value has to be calculated in the model and the
result needs to be in a table. every result (which will be 4*6*something)
need to be visible, as they will be used in a graph.
(am i still clear?)

now, how do I change that one value and allow excel to draw the graph?
(if you need more information, ask your question and i will try to answer
them)

An example of the data and any formulae you are using might enlighten us.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"theredspecial" <(E-Mail Removed)> wrote in message
news:03054976-A2B5-451B-966D-(E-Mail Removed)...
> i've build a model in excel.
> on the basis of a few variables i made quite a number of sheets. one
> variable i had to keep constant, otherwise i would need 3d tables. now
> that
> the model is almost done, this one variable needs to change.
> it needs to take discrete values between 0 and 5 (for example, and in
> steps
> of .25) the change of this value has to be calculated in the model and the
> result needs to be in a table. every result (which will be 4*6*something)
> need to be visible, as they will be used in a graph.
> (am i still clear?)
>
> now, how do I change that one value and allow excel to draw the graph?
> (if you need more information, ask your question and i will try to answer
> them)

The number that you are changing (0 to 5) should of been a cell in the
worksheets not hard coded. Then the table that you using should reference
the cell which contains 0 to 5

for example if you graph was based on the following table

old table
A B
1 =3 * 5 =3 * 10

2 =3 * 7 =3 * 15

The new table now has D1 instead of 3. Then change D1 to be 0 to 5 and the
graph will automatically change

new table
A B
1 =D1 * 5 =D1 * 10

2 =D1 * 7 =D1 * 15

"Bob Phillips" wrote:

> An example of the data and any formulae you are using might enlighten us.
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "theredspecial" <(E-Mail Removed)> wrote in message
> news:03054976-A2B5-451B-966D-(E-Mail Removed)...
> > i've build a model in excel.
> > on the basis of a few variables i made quite a number of sheets. one
> > variable i had to keep constant, otherwise i would need 3d tables. now
> > that
> > the model is almost done, this one variable needs to change.
> > it needs to take discrete values between 0 and 5 (for example, and in
> > steps
> > of .25) the change of this value has to be calculated in the model and the
> > result needs to be in a table. every result (which will be 4*6*something)
> > need to be visible, as they will be used in a graph.
> > (am i still clear?)
> >
> > now, how do I change that one value and allow excel to draw the graph?
> > (if you need more information, ask your question and i will try to answer
> > them)
>
>
>

the file itself is approx. 15megs, so that's difficult, but i'll post some
formulas

in sheet Vekeersaanbod in cfell D29
=IF(Routekeuze!D29=1;INDEX('Verkeersaanbod
(totaal)'!$B$3:$J$27;MATCH(D$5;'Verkeersaanbod
(totaal)'!$B$3:$B$27;MATCH($B$3;'Verkeersaanbod
(totaal)'!$B$3:$J$3);TRUE)*VLOOKUP(Tijdwinst!D29;Hulptabellen!$C$4:$G$105;5);0)
(a similar formula is in each cell in this sheet till ceel T600something)

The vlookup returns a percentage that might change, and goes:
=(C$3+$B5)*Reistijd!$F$29
C3 and B5 yield a percentage times F29. This F29 is the variable which will
change now, but for which I need to record the change and draw a graph...

The Routekeuze!D29 formula goes:
=IF(AND('Routevoorkeuren (m tol)'!D29<'Routevoorkeuren (m
tol)'!E29;'Routevoorkeuren (m tol)'!D29<'Routevoorkeuren (m tol)'!F29);1;0)

The D29 i refer to here is:
=IF($C29>0;(($C29*Reistijd!$E$12)*(Tijdwinst!E29-Tijdwinst!D29));"")

Reistijd!E12 is a set value that might change, but that not relevant to the
question
TijdwinstE29 and D29 are the results of formulas, but they won't change either

Does this help?

"Bob Phillips" wrote:

> An example of the data and any formulae you are using might enlighten us.
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "theredspecial" <(E-Mail Removed)> wrote in message
> news:03054976-A2B5-451B-966D-(E-Mail Removed)...
> > i've build a model in excel.
> > on the basis of a few variables i made quite a number of sheets. one
> > variable i had to keep constant, otherwise i would need 3d tables. now
> > that
> > the model is almost done, this one variable needs to change.
> > it needs to take discrete values between 0 and 5 (for example, and in
> > steps
> > of .25) the change of this value has to be calculated in the model and the
> > result needs to be in a table. every result (which will be 4*6*something)
> > need to be visible, as they will be used in a graph.
> > (am i still clear?)
> >
> > now, how do I change that one value and allow excel to draw the graph?
> > (if you need more information, ask your question and i will try to answer
> > them)
>
>
>

i made a workaround so this problem isn't relevant anymore

"theredspecial" wrote:

> the file itself is approx. 15megs, so that's difficult, but i'll post some
> formulas
>
> in sheet Vekeersaanbod in cfell D29
> =IF(Routekeuze!D29=1;INDEX('Verkeersaanbod
> (totaal)'!$B$3:$J$27;MATCH(D$5;'Verkeersaanbod
> (totaal)'!$B$3:$B$27;MATCH($B$3;'Verkeersaanbod
> (totaal)'!$B$3:$J$3);TRUE)*VLOOKUP(Tijdwinst!D29;Hulptabellen!$C$4:$G$105;5);0)
> (a similar formula is in each cell in this sheet till ceel T600something)
>
> The vlookup returns a percentage that might change, and goes:
> =(C$3+$B5)*Reistijd!$F$29
> C3 and B5 yield a percentage times F29. This F29 is the variable which will
> change now, but for which I need to record the change and draw a graph...
>
> The Routekeuze!D29 formula goes:
> =IF(AND('Routevoorkeuren (m tol)'!D29<'Routevoorkeuren (m
> tol)'!E29;'Routevoorkeuren (m tol)'!D29<'Routevoorkeuren (m tol)'!F29);1;0)
>
> The D29 i refer to here is:
> =IF($C29>0;(($C29*Reistijd!$E$12)*(Tijdwinst!E29-Tijdwinst!D29));"")
>
> Reistijd!E12 is a set value that might change, but that not relevant to the
> question
> TijdwinstE29 and D29 are the results of formulas, but they won't change either
>
> Does this help?
>
> "Bob Phillips" wrote:
>
> > An example of the data and any formulae you are using might enlighten us.
> >
> > --
> > HTH
> >
> > Bob
> >
> > (there's no email, no snail mail, but somewhere should be gmail in my addy)
> >
> > "theredspecial" <(E-Mail Removed)> wrote in message
> > news:03054976-A2B5-451B-966D-(E-Mail Removed)...
> > > i've build a model in excel.
> > > on the basis of a few variables i made quite a number of sheets. one
> > > variable i had to keep constant, otherwise i would need 3d tables. now
> > > that
> > > the model is almost done, this one variable needs to change.
> > > it needs to take discrete values between 0 and 5 (for example, and in
> > > steps
> > > of .25) the change of this value has to be calculated in the model and the
> > > result needs to be in a table. every result (which will be 4*6*something)
> > > need to be visible, as they will be used in a graph.
> > > (am i still clear?)
> > >
> > > now, how do I change that one value and allow excel to draw the graph?
> > > (if you need more information, ask your question and i will try to answer
> > > them)
> >
> >
> >

FYI this workaround is the trilookup formula/add-in downloadable somewhere
(use google to find it)

"theredspecial" wrote:

> i made a workaround so this problem isn't relevant anymore
>
> "theredspecial" wrote:
>
> > the file itself is approx. 15megs, so that's difficult, but i'll post some
> > formulas
> >
> > in sheet Vekeersaanbod in cfell D29
> > =IF(Routekeuze!D29=1;INDEX('Verkeersaanbod
> > (totaal)'!$B$3:$J$27;MATCH(D$5;'Verkeersaanbod
> > (totaal)'!$B$3:$B$27;MATCH($B$3;'Verkeersaanbod
> > (totaal)'!$B$3:$J$3);TRUE)*VLOOKUP(Tijdwinst!D29;Hulptabellen!$C$4:$G$105;5);0)
> > (a similar formula is in each cell in this sheet till ceel T600something)
> >
> > The vlookup returns a percentage that might change, and goes:
> > =(C$3+$B5)*Reistijd!$F$29
> > C3 and B5 yield a percentage times F29. This F29 is the variable which will
> > change now, but for which I need to record the change and draw a graph...
> >
> > The Routekeuze!D29 formula goes:
> > =IF(AND('Routevoorkeuren (m tol)'!D29<'Routevoorkeuren (m
> > tol)'!E29;'Routevoorkeuren (m tol)'!D29<'Routevoorkeuren (m tol)'!F29);1;0)
> >
> > The D29 i refer to here is:
> > =IF($C29>0;(($C29*Reistijd!$E$12)*(Tijdwinst!E29-Tijdwinst!D29));"")
> >
> > Reistijd!E12 is a set value that might change, but that not relevant to the
> > question
> > TijdwinstE29 and D29 are the results of formulas, but they won't change either
> >
> > Does this help?
> >
> > "Bob Phillips" wrote:
> >
> > > An example of the data and any formulae you are using might enlighten us.
> > >
> > > --
> > > HTH
> > >
> > > Bob
> > >
> > > (there's no email, no snail mail, but somewhere should be gmail in my addy)
> > >
> > > "theredspecial" <(E-Mail Removed)> wrote in message
> > > news:03054976-A2B5-451B-966D-(E-Mail Removed)...
> > > > i've build a model in excel.
> > > > on the basis of a few variables i made quite a number of sheets. one
> > > > variable i had to keep constant, otherwise i would need 3d tables. now
> > > > that
> > > > the model is almost done, this one variable needs to change.
> > > > it needs to take discrete values between 0 and 5 (for example, and in
> > > > steps
> > > > of .25) the change of this value has to be calculated in the model and the
> > > > result needs to be in a table. every result (which will be 4*6*something)
> > > > need to be visible, as they will be used in a graph.
> > > > (am i still clear?)
> > > >
> > > > now, how do I change that one value and allow excel to draw the graph?
> > > > (if you need more information, ask your question and i will try to answer
> > > > them)
> > >
> > >
> > >