Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
will
 
Posts: n/a
Default It works but why...?

Can someone explain this to me, so that I can better understand what is
happening:-

We are a print company and we want to give our customer an estimating
workbook, that is made up of lots of different calculations, based on about
20 different types of variable. The value of these 20 variables change from
customer to customer, and they will be for things like price of a printing
plate, or price to run the printing press etc etc. The estimating workbook
would therefore, for example, be able to specify how many printing plates are
needed, and given the price for the printing plates for that particular
customer, thereby calculate a total printing plate price which will be part
of the overall price.

In order to maintain the confidentiallity of these 20 individual variables,
the workbook gives only the final price per printed output - ie the overall
price. Also in order to maintain confidentiallity, I have written the
estimating workbook separate from the variables worksheet - the estimating
workbook uses links to the variables worksheet to calculate the final price.

We send the customer just the estimating workbook, and not the variables
worksheet (as we obviously dont want them to see the individual variables).
Yet the estimating workbook still works for them, even though they cannot
open the link to the variables worksheet - which is what I dont understand.
Are the variables in fact somehow encoded intot the estimating workbook, and
therefore not as hidden as we first thought?

When I have tried sending just the estimating workbook to another PC that
does not have access to the variables worksheet, the estimating workbook
works fine, and I cannot "find" the data from the variables worksheet (other
than that I know it is part of the calculation within the estimating
workbook), which makes me think it is still kept hidden.

Does this make sense?!

Will


  #2   Report Post  
Zack Barresse
 
Posts: n/a
Default

Hello Will,

Great explanation, but I'm afraid it doesn't do much, for me anyway. Can
you post some of your actual formulas? Without looking at any actual data,
it gets a little difficult to troubleshoot something like this. As I am
unsure of your data setup/structure, I hesitate to go any further as it
would be a lot of speculation.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"will" wrote in message
...
Can someone explain this to me, so that I can better understand what is
happening:-

We are a print company and we want to give our customer an estimating
workbook, that is made up of lots of different calculations, based on
about
20 different types of variable. The value of these 20 variables change
from
customer to customer, and they will be for things like price of a printing
plate, or price to run the printing press etc etc. The estimating workbook
would therefore, for example, be able to specify how many printing plates
are
needed, and given the price for the printing plates for that particular
customer, thereby calculate a total printing plate price which will be
part
of the overall price.

In order to maintain the confidentiallity of these 20 individual
variables,
the workbook gives only the final price per printed output - ie the
overall
price. Also in order to maintain confidentiallity, I have written the
estimating workbook separate from the variables worksheet - the estimating
workbook uses links to the variables worksheet to calculate the final
price.

We send the customer just the estimating workbook, and not the variables
worksheet (as we obviously dont want them to see the individual
variables).
Yet the estimating workbook still works for them, even though they cannot
open the link to the variables worksheet - which is what I dont
understand.
Are the variables in fact somehow encoded intot the estimating workbook,
and
therefore not as hidden as we first thought?

When I have tried sending just the estimating workbook to another PC that
does not have access to the variables worksheet, the estimating workbook
works fine, and I cannot "find" the data from the variables worksheet
(other
than that I know it is part of the calculation within the estimating
workbook), which makes me think it is still kept hidden.

Does this make sense?!

Will




  #3   Report Post  
Ken Wright
 
Posts: n/a
Default

By all means send me a copy and I'll take a look at what you have in there
vs what you don't.

If I read you correctly you have two workbooks, one with calcs and one with
variables, with the calcs one referencing the variables one. You then send
out just the one with calcs but it still appears to be able to reference the
variables one?

Is that correct?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"will" wrote in message
...
Can someone explain this to me, so that I can better understand what is
happening:-

We are a print company and we want to give our customer an estimating
workbook, that is made up of lots of different calculations, based on
about
20 different types of variable. The value of these 20 variables change
from
customer to customer, and they will be for things like price of a printing
plate, or price to run the printing press etc etc. The estimating workbook
would therefore, for example, be able to specify how many printing plates
are
needed, and given the price for the printing plates for that particular
customer, thereby calculate a total printing plate price which will be
part
of the overall price.

In order to maintain the confidentiallity of these 20 individual
variables,
the workbook gives only the final price per printed output - ie the
overall
price. Also in order to maintain confidentiallity, I have written the
estimating workbook separate from the variables worksheet - the estimating
workbook uses links to the variables worksheet to calculate the final
price.

We send the customer just the estimating workbook, and not the variables
worksheet (as we obviously dont want them to see the individual
variables).
Yet the estimating workbook still works for them, even though they cannot
open the link to the variables worksheet - which is what I dont
understand.
Are the variables in fact somehow encoded intot the estimating workbook,
and
therefore not as hidden as we first thought?

When I have tried sending just the estimating workbook to another PC that
does not have access to the variables worksheet, the estimating workbook
works fine, and I cannot "find" the data from the variables worksheet
(other
than that I know it is part of the calculation within the estimating
workbook), which makes me think it is still kept hidden.

Does this make sense?!

Will




  #4   Report Post  
will
 
Posts: n/a
Default

Thanks for the replies.

Ken - yes, more succinctly put than me, but yes!

I have emailed you the spreadsheets so you can see what I mean.

Regards,

Will

"Ken Wright" wrote:

By all means send me a copy and I'll take a look at what you have in there
vs what you don't.

If I read you correctly you have two workbooks, one with calcs and one with
variables, with the calcs one referencing the variables one. You then send
out just the one with calcs but it still appears to be able to reference the
variables one?

Is that correct?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------

"will" wrote in message
...
Can someone explain this to me, so that I can better understand what is
happening:-

We are a print company and we want to give our customer an estimating
workbook, that is made up of lots of different calculations, based on
about
20 different types of variable. The value of these 20 variables change
from
customer to customer, and they will be for things like price of a printing
plate, or price to run the printing press etc etc. The estimating workbook
would therefore, for example, be able to specify how many printing plates
are
needed, and given the price for the printing plates for that particular
customer, thereby calculate a total printing plate price which will be
part
of the overall price.

In order to maintain the confidentiallity of these 20 individual
variables,
the workbook gives only the final price per printed output - ie the
overall
price. Also in order to maintain confidentiallity, I have written the
estimating workbook separate from the variables worksheet - the estimating
workbook uses links to the variables worksheet to calculate the final
price.

We send the customer just the estimating workbook, and not the variables
worksheet (as we obviously dont want them to see the individual
variables).
Yet the estimating workbook still works for them, even though they cannot
open the link to the variables worksheet - which is what I dont
understand.
Are the variables in fact somehow encoded intot the estimating workbook,
and
therefore not as hidden as we first thought?

When I have tried sending just the estimating workbook to another PC that
does not have access to the variables worksheet, the estimating workbook
works fine, and I cannot "find" the data from the variables worksheet
(other
than that I know it is part of the calculation within the estimating
workbook), which makes me think it is still kept hidden.

Does this make sense?!

Will





  #5   Report Post  
Ken Wright
 
Posts: n/a
Default

Hi Will - just checked and no email received. Did you take out the NOSPAM
but from my email address?

Should be ken dot wright at ntlworld dot com

Regards
Ken......................

"will" wrote in message
...
Thanks for the replies.

Ken - yes, more succinctly put than me, but yes!

I have emailed you the spreadsheets so you can see what I mean.

Regards,

Will

"Ken Wright" wrote:

By all means send me a copy and I'll take a look at what you have in
there
vs what you don't.

If I read you correctly you have two workbooks, one with calcs and one
with
variables, with the calcs one referencing the variables one. You then
send
out just the one with calcs but it still appears to be able to reference
the
variables one?

Is that correct?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------

"will" wrote in message
...
Can someone explain this to me, so that I can better understand what is
happening:-

We are a print company and we want to give our customer an estimating
workbook, that is made up of lots of different calculations, based on
about
20 different types of variable. The value of these 20 variables change
from
customer to customer, and they will be for things like price of a
printing
plate, or price to run the printing press etc etc. The estimating
workbook
would therefore, for example, be able to specify how many printing
plates
are
needed, and given the price for the printing plates for that particular
customer, thereby calculate a total printing plate price which will be
part
of the overall price.

In order to maintain the confidentiallity of these 20 individual
variables,
the workbook gives only the final price per printed output - ie the
overall
price. Also in order to maintain confidentiallity, I have written the
estimating workbook separate from the variables worksheet - the
estimating
workbook uses links to the variables worksheet to calculate the final
price.

We send the customer just the estimating workbook, and not the
variables
worksheet (as we obviously dont want them to see the individual
variables).
Yet the estimating workbook still works for them, even though they
cannot
open the link to the variables worksheet - which is what I dont
understand.
Are the variables in fact somehow encoded intot the estimating
workbook,
and
therefore not as hidden as we first thought?

When I have tried sending just the estimating workbook to another PC
that
does not have access to the variables worksheet, the estimating
workbook
works fine, and I cannot "find" the data from the variables worksheet
(other
than that I know it is part of the calculation within the estimating
workbook), which makes me think it is still kept hidden.

Does this make sense?!

Will









  #6   Report Post  
Bill Manville
 
Posts: n/a
Default

To maintain confidentiality of the parameters you would need to use
Edit / Links / Break Link (or Edit / Copy; Edit / Paste Special /
Values) before sending the workbook.

If the formulas are present then the user will be able to see the value
of the parameter which came across the link.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #7   Report Post  
will
 
Posts: n/a
Default

Bill,

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?

Will


"Bill Manville" wrote:

To maintain confidentiality of the parameters you would need to use
Edit / Links / Break Link (or Edit / Copy; Edit / Paste Special /
Values) before sending the workbook.

If the formulas are present then the user will be able to see the value
of the parameter which came across the link.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


  #8   Report Post  
will
 
Posts: n/a
Default

Ken,

Doh - thanks! I'll try again.

Will

"Ken Wright" wrote:

Hi Will - just checked and no email received. Did you take out the NOSPAM
but from my email address?

Should be ken dot wright at ntlworld dot com

Regards
Ken......................

"will" wrote in message
...
Thanks for the replies.

Ken - yes, more succinctly put than me, but yes!

I have emailed you the spreadsheets so you can see what I mean.

Regards,

Will

"Ken Wright" wrote:

By all means send me a copy and I'll take a look at what you have in
there
vs what you don't.

If I read you correctly you have two workbooks, one with calcs and one
with
variables, with the calcs one referencing the variables one. You then
send
out just the one with calcs but it still appears to be able to reference
the
variables one?

Is that correct?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------

"will" wrote in message
...
Can someone explain this to me, so that I can better understand what is
happening:-

We are a print company and we want to give our customer an estimating
workbook, that is made up of lots of different calculations, based on
about
20 different types of variable. The value of these 20 variables change
from
customer to customer, and they will be for things like price of a
printing
plate, or price to run the printing press etc etc. The estimating
workbook
would therefore, for example, be able to specify how many printing
plates
are
needed, and given the price for the printing plates for that particular
customer, thereby calculate a total printing plate price which will be
part
of the overall price.

In order to maintain the confidentiallity of these 20 individual
variables,
the workbook gives only the final price per printed output - ie the
overall
price. Also in order to maintain confidentiallity, I have written the
estimating workbook separate from the variables worksheet - the
estimating
workbook uses links to the variables worksheet to calculate the final
price.

We send the customer just the estimating workbook, and not the
variables
worksheet (as we obviously dont want them to see the individual
variables).
Yet the estimating workbook still works for them, even though they
cannot
open the link to the variables worksheet - which is what I dont
understand.
Are the variables in fact somehow encoded intot the estimating
workbook,
and
therefore not as hidden as we first thought?

When I have tried sending just the estimating workbook to another PC
that
does not have access to the variables worksheet, the estimating
workbook
works fine, and I cannot "find" the data from the variables worksheet
(other
than that I know it is part of the calculation within the estimating
workbook), which makes me think it is still kept hidden.

Does this make sense?!

Will








  #9   Report Post  
Bill Manville
 
Posts: n/a
Default

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

  #10   Report Post  
will
 
Posts: n/a
Default

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




  #11   Report Post  
Bill Manville
 
Posts: n/a
Default

Hi Will

If the user can see the formulas then the user can see the value of the
parameters, wherever they come from, by highlighting the relevant part
of the formula in the formula bar and pressing F9.

To prevent that you need to hide the formulas and protect the
worksheet:
Format / Cells / Protection / Hidden
Tools / Protection / Protect Sheet (with a password)

If the user is to be allowed to change some cells then they first need
to be unlocked on Format / Cells / Protection before the sheet is
protected.

If you had a worksheet containing the values of the variables you don't
want the user to see (which could be the result of simple linking
formulas) you could then hide that worksheet from the user.
Format / Sheet / Hide
and protect the workbook structure
Tools / Protection / Protect Workbook (with a password)

The formulas in the user-visible worksheet would reference the copies
of the variables in the hidden sheet (I would name the cells concerned
for clarity in the formulas).

You can break the link before sending the workbook out and the values
in the hidden sheet will be preserved.

Hope this helps


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #12   Report Post  
Ken Wright
 
Posts: n/a
Default

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




  #13   Report Post  
will
 
Posts: n/a
Default

Bill,

Many thanks for your help.

I have the calculating worksheets on the estimating workbook hidden and
protected, so hopefully OK. In fact the reason why I structured it this way
(ie with a separate variables workbook) was to try and make it more secure -
given the lack of security that Excel passwords seem to offer. Now it seems
as if it probably doesn't help on that count much anyway, but I suppose it is
another complication that might just put the customer off from nosing out my
variables!

Regards,

Will


"Bill Manville" wrote:

Hi Will

If the user can see the formulas then the user can see the value of the
parameters, wherever they come from, by highlighting the relevant part
of the formula in the formula bar and pressing F9.

To prevent that you need to hide the formulas and protect the
worksheet:
Format / Cells / Protection / Hidden
Tools / Protection / Protect Sheet (with a password)

If the user is to be allowed to change some cells then they first need
to be unlocked on Format / Cells / Protection before the sheet is
protected.

If you had a worksheet containing the values of the variables you don't
want the user to see (which could be the result of simple linking
formulas) you could then hide that worksheet from the user.
Format / Sheet / Hide
and protect the workbook structure
Tools / Protection / Protect Workbook (with a password)

The formulas in the user-visible worksheet would reference the copies
of the variables in the hidden sheet (I would name the cells concerned
for clarity in the formulas).

You can break the link before sending the workbook out and the values
in the hidden sheet will be preserved.

Hope this helps


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


  #14   Report Post  
will
 
Posts: n/a
Default

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
DIFFERNCE BETWEEN EXCELL AND WORKS SPREEDSHEETS Mike Excel Discussion (Misc queries) 0 August 29th 05 09:45 PM
Works 4.0(wks) needs to be converted to Excel 2003 BJB Excel Discussion (Misc queries) 2 July 15th 05 03:49 PM
How do I convert works file to excel without works software? CatMB Excel Discussion (Misc queries) 1 June 21st 05 04:12 PM
Save As... MS Works 2000 spreadsheet Thomas Excel Discussion (Misc queries) 2 May 7th 05 06:56 PM
Microsoft Excel --> Microsoft Works Spreadsheet ChrisLouie Excel Worksheet Functions 0 April 3rd 05 10:37 PM


All times are GMT +1. The time now is 03:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"