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 |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
It works but why...?
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
|
|||
|
|||
It works but why...?
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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
DIFFERNCE BETWEEN EXCELL AND WORKS SPREEDSHEETS | Mike | General Discussion | 0 | August 29th, 2005 09:45 PM |
How do I convert works file to excel without works software? | CatMB | General Discussion | 1 | June 21st, 2005 04:12 PM |
How do you open a Works 6.0 doc with Word 2002? | capnhowdyox | General Discussion | 1 | May 3rd, 2005 02:21 PM |
Unable to install Word 2002 as a part of Microsoft Works Suite 20. | Cindy M -WordMVP- | General Discussion | 0 | April 26th, 2005 10:22 AM |
Works Vs. Office | General Discussions | 6 | August 3rd, 2004 08:35 PM |