Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

Team Consolidation Question .

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
rwebsteris
Asked:
rwebsteris
  • 6
  • 2
1 Solution
 
patrickabCommented:
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
 
patrickabCommented:
Renee do provide other information if it would help - Patrick
0
 
rwebsterisAuthor Commented:
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!

 
patrickabCommented:
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
 
patrickabCommented:
Hi Renee,

How did you get on with the whole issue?

Patrick
0
 
rwebsterisAuthor Commented:
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
 
patrickabCommented:
Renee - Glad it worked - regards Patrick
0
 
Paul_LautmanCommented:
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
 
patrickabCommented:
Paul_Lautman - thanks
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now