?
Solved

Team Consolidation Question .

Posted on 2003-03-28
9
Medium Priority
?
380 Views
Last Modified: 2010-04-22
Hi all:

I was wondering if anyone is familiar with the Team Consolidation feature in Lotus 123. I have a Notesdatabase set up and the Spreadsheet alresdy created. My question is, can you lock cetain cells and still use the consolidation feature.

This workbook contains 24 spreadsheets that are distributed to 24 different people. Thses 24 spreadsheets are the rolled up into a single Master spreadsheet. Problem is I have some people who insist on making changes where they should not and it completely mess's up my formulas.

I would like to protect certain sections so that they can not make changes in those areas.

Any assistance would be greatly appreciated.

Renee
0
Comment
Question by:rwebsteris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 8228435
Lotus123 does work in strange ways but there is one thing for certain and that is if your spreadsheet is 'locked' you cannot change protected cells - and you cannot un-protect them either without the password.

The default position for all cells in all worksheet is 'protected' but un-locked. So to make use of the protection scheme you first need to un-lock the spread sheet by file/workbookproperties/security and untick the 'Lock worksheet' item.

Then highlight the cells you do NOT want to protect, rightclick on them and select the 'key' symbol and deselect the 'Protect cells from changes' and then go back into file/workbookproperties/security and tick the 'Lock worksheet' item. This will lock the work sheet but allow changes to those cells that are unprotected.

The confusing part of the whole mechanism is when the worksheet is first used. The file/workbookproperties/security is ticked in the 'Lock worksheet' item. This needs to be un-ticked first. When it is re-ticked you will be asked for a password.

To make any subsequent changes to the worksheet you will need to un-tick the file/workbookproperties/security 'Lock worksheet' item before you can proceed with the changes.

There is a degree of logic in Lotus123's approach in that you don't need to go through every part of the spreadsheet to protect everything. In fact you need to go through it to un-protect all the cells you want to be able to be changed even when the spreadsheet is locked.

The only problem comes if you are attempting to modify by means of a macro a cell that is protect and locked as such.

I do hope this helps - even though it is a tad confusing. By the way do NOT forget the password as for all practical purposes it cannot be cracked!
0
 
LVL 45

Accepted Solution

by:
patrickab earned 300 total points
ID: 8228453
Renee do provide other information if it would help - Patrick
0
 

Author Comment

by:rwebsteris
ID: 8232020
Hi Patrick<

Thanks for the info, but I am aware of the process for locking and unlocking a spreadsheet. My question I guess was not clear enough.

Are you familiar with the Team Consolodate Feature??

You create you Master Spreadsheet in 123 then go to:
Team Consolidate
Distribute Spreadsheet through Lotus Notes.
You are then taken to Lotus Notes, where you must have the Team Consolidate db already created. You open that db and click on Create Master Worksheet Document.

You then choose the Master spreadsheet created in 123. You now have the option of distributing them now or later.
From here this Document distributes each individual worksheet for a workbook to individuals for updating.

They make the changes and mark the sheet ready for consolidation. I then press a button and Lotus Notes updates the Master Workbook with the individual sheets.

I think I am going to run into a problem if I do this with locked spreadsheets. I don't think Lotus 123 will allow the consolidation to take place. You can not update Locked Cells. (I seem to remember reading this someplace).

Anyway if you know any more about this I would appreciate your input.

Renee



0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

Expert Comment

by:patrickab
ID: 8233208
Hi Renee,

The Team Consolidation feature of Notes I am not familiar with. In fact I do not have any Notes experience however I think what I have said about locking a Lotus123 file will still help you achieve what you want.

You are not correct about locked cells. In a Lotus123 file, locked cells can be up-dated but you cannot manually over-write them. The way that you can get it to work is to un-protect all those cells you don't mind having changed before you lock it. That way you can set up all the formulae, and only un-protect those cells (without formulae) in which you are going to allow data entry. The cells with the formulae would be protected but can be up-dated. You don't need to 'protect' any cells as by default all cells are protected when the file is locked. So you need to un-protect first what you need access to, then lock the spreadsheet.

It is a confusing area but if you just do a few experiments on another spreadsheet using my instructions you will find that it can be made to work really quite easily. It just takes a bit of getting used to as it appears to be counter-intuitive.

Regards

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 8246949
Hi Renee,

How did you get on with the whole issue?

Patrick
0
 

Author Comment

by:rwebsteris
ID: 8247188
Thanks Patrick .

I was just now able to tet everything out. Lots of other stuff on my plate.

I was sure I read someplace that you could not lock worksheets and use the Team Consolidate.

Anyway it works.


Renee Webster
0
 
LVL 45

Expert Comment

by:patrickab
ID: 8249030
Renee - Glad it worked - regards Patrick
0
 
LVL 1

Expert Comment

by:Paul_Lautman
ID: 8290508
patrickab said:
"By the way do NOT forget the password as for all practical purposes it cannot be cracked!"

This is true in as much as no crack is needed.

Open the spreadsheet in Notepad and the lock password is displayed in normal text for all to see.

This is not the case for the password used when the sheet is "saved" with password protection, only for the lock one.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 8391617
Paul_Lautman - thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question