Ken,
Many thanks for all your help on this. I'm glad that I at least managed to
suprise with you with one small part of this! Personally I was suprised that
all the results were remembered.
Please also see reply to Bill above.
Regards,
Will
"Ken Wright" wrote:
I think Bill's given you the answer in that Excel remembers the value of all
the parameters. It's not remembering all the variables in the variables
workbook, it's just remembering the results of the parameters in all the If
statements and direct links you have in your estimating workbook, eg:-
In your estimating workbook, in Finishing!T12 (Charge per 1000) you have
the following formula
=IF(Results!C12=TRUE,'F:\4test\[X variables.xls]Variables'!D54,0)
(The path will be different on your machine.)
This has two possible values in it, ie the value that was in [X
variables.xls]Variables'!D54 and the value 0. Excel remembers this first
value from when it was linked to the variables workbook, and so despite the
variables file no longer being there, you are still at liberty to use the
checkbox on your input sheet to include or exclude embossing as an option.
This means you can still use the input sheet to generate different
scenarios, even without the linked file being available. If you try to
update the cell however, it all dies on you and you get a #REF error.
Your estimating workbook doesn't require that the variables workbook do any
calculations once it has been seeded with the appropriate values, with the
exception of a LOOKUP formula that you have, and i must admit that that kind
of threw me as I didn't realise it would retain all the results of the
LOOKUP, but it seems that it does.
In case it helps Bill to see what kind of fomulas you have in your links at
all, ( He is the Links guru :- ) the links you have in the workbook are as
follows
Value
='F:\4test\[X variables.xls]Variables'!$D$5:$D$11
='F:\4test\[X variables.xls]Variables'!$D$45:$D$50
='F:\4test\[X variables.xls]Variables'!$C$19:$C$29
='F:\4test\[X variables.xls]Variables'!$D$19:$D$29
='F:\4test\[X variables.xls]Variables'!$E$19:$E$29
='F:\4test\[X variables.xls]Variables'!$F$19:$F$29
='F:\4test\[X variables.xls]Variables'!$D$34:$D$39
=LOOKUP(Results!C6,Board_1,'F:\4test\[X
variables.xls]Variables'!$D$5:$D$11)
=IF(Results!C7=1,'F:\4test\[X variables.xls]Variables'!D17,0)
='F:\4test\[X variables.xls]Variables'!D18
='F:\4test\[X variables.xls]Variables'!D15
=IF(Results!C12=TRUE,'F:\4test\[X variables.xls]Variables'!D53,0)
=IF(Results!C12=TRUE,'F:\4test\[X variables.xls]Variables'!D54,0)
=IF(Results!C13=TRUE,'F:\4test\[X variables.xls]Variables'!D58,0)
=IF(Results!C14=TRUE,'F:\4test\[X variables.xls]Variables'!D61,0)
=IF(Results!C11=1,0,'F:\4test\[X variables.xls]Variables'!D43)
=IF(Results!C11=1,0,'F:\4test\[X variables.xls]Variables'!D44)
='F:\4test\[X variables.xls]Variables'!$D$64
=IF(Results!C16=TRUE,'F:\4test\[X variables.xls]Variables'!D69,0)
=IF(Results!C16=TRUE,'F:\4test\[X variables.xls]Variables'!D70,0)
='F:\4test\[X variables.xls]Variables'!$E$71
Excel will remember all these variables, though as i say, I was surpised
that
=LOOKUP(Results!C6,Board_1,'F:\4test\[X variables.xls]Variables'!$D$5:$D$11)
was able to recall all the possible answers depending on the choice of
board. I even added a bunch more results to the table, saved both
workbooks, and then got rid of the variables one by renaming it, yet the
estimating sheet was still correctly able to recall all the values within
the lookup range, and use them to return the appropriate value depending on
what Board had been chosen.
With that exception (a big one for me i must admit), it's doing what i would
have expected it to.
Regards
Ken.....................
"will" wrote in message
...
Bill,
Thanks for that, and I understand what you are saying. The estimating
workbook contains the more complicated formulas, as per your description,
so
if one broke the links then the workbook would not work.
It does not answer the original point, though, which is that the linked
estimating workbook must somehow contain all variables within the
variables
workbook for it to function. And if this is the case, then where are those
variables stored, and how would the customer view them if he had a mind to
do
so?
Will
"Bill Manville" wrote:
Will wrote:
I take your point, but presumably this would then mean that the
estimating
workbook would not work as it would only contain those parameters which
were
selected when it was initially sent?
It depends on how you have written your formulas.
If you have just brought individual parameters into cells in the
estimating
workbook by links (e.g. =[clientparams.xls]Sheet1!CostPerPage ) then
breaking
the link will put the relevant value into the cell.
If you have included references to the source workbook in a more
complicated
formula (e.g. =NumberOfPages*[clientparams.xls]Sheet1!CostPerPage )
then you are correct, that formula will not continue to work when you
change
NumberOfPages since the entire formula will be replaced by its current
value.
You might therefore choose to have a MyCostPerPage cell in the estimating
workbook, containing =[clientparams.xls]Sheet1!CostPerPage and change
your
formula to =NumberOfPages*MyCostPerPage .
As I said, the user will easily be able to determine what his parameters
are,
given that the formulas can be seen, but at least he won't be plagued by
"update links?" questions when opening the workbook.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup
|