How to lock cells in BPC

[Former Member]

Hi,

I am developing a BPC solution for client. I have a requirement to lock certain cells so that user is not able to enter/change any values in these cells. I don't want to lock the entire sheet. Please advise if you've resolved similar requirement.

Thanks,

Raman

[Former Member]

I ran into this one about a month ago myself. The cell that you want to allow users to input on cannot be "Locked." The locking mechanism that controls my input schedules is excel based, not BPC.

The following steps will show you how to "unlock" the cell for inputs while leaving the rest of the worksheet locked so the users cannot change it:

1) Select cell(s) you want to unlock

2) Format Cells (one of two ways)

a) Right click and hit "format cells"

b) Click the "Format menu" --> "Cells" option

3) Click on the Protection tab of the "Format Cells" window

4) Deselect the "Locked" checkbox

You can then lock the worksheet by going through the Tools Menu --> Protection --> Protect Sheet.

I recommend allowing them to select locked and unlocked cells (the first two options) and then password protecting it.

The expansions will still work accordingly and the first cell of the expansion will copy down through the expansion, permitting all cells in that column to be user input cells.

I hope that helps. I am using Excel 2003 with BPC 7.0M

Good Luck,

Nick

[former_member190501]

Hi,

Try Excel Data Validation to lock cells from editing.

eg: Only ACTUAL cols has to be locked.

	A	B	C	D
1	ACTUAL	ACTUAL	BUDGET  BUDGET
2				
3				
4				

Step 1:

Select cell A2

Step 2:

On the Data menu, click Validation. From Settings tab select Custom as Allow and maintain Formula as =IF(A1="ACTUAL",0,1)

Step 3:

Goto Error Alert tab, Select style as Stop give title as Error and Error message as "ACTUAL Data cannot be changed"

Step 4:

Click OK.

If Expansion is ON in your report/input schedule just maintain validation as above in first data cell and select Expand all to get copied into remaining all data cells .

hope it helps...

regards,

Raju

[former_member185837]

Hi,

I'd also need to use Excel's data validation feature in a BPC input schedule--not to lock specific cells from user input, as in this example, but to allow users only to choose from a list of values.

Unfortunately, validation is not copied in data cells when expanding. All the rest is copied--formats, values, formulas. Only validation seems to have this problem.

What should I do to have validation being copied during expansion?

I am on BPC NW 7.5.

Thanks,

  Davide

* UPDATE

I've solved this issue. The solution is described here:

http://scn.sap.com/thread/3209213

 VaraPrasadraju Potturi wrote:
Hi,
Try Excel Data Validation to lock cells from editing.
eg: Only ACTUAL cols has to be locked.
     A     B     C     D
1     ACTUAL     ACTUAL     BUDGET  BUDGET
2                    
3                    
4                    


Step 1:
   Select cell A2
Step 2: 
  On the Data menu, click Validation. From Settings tab select Custom as Allow and maintain Formula as  =IF(A1="ACTUAL",0,1)
Step 3:
  Goto Error Alert tab, Select style as Stop give title as Error and Error message as "ACTUAL Data cannot be changed"
Step 4:
   Click OK.

If Expansion is ON in your report/input schedule just maintain validation as above in first data cell and select Expand all to get copied into remaining all data cells .



hope it helps...
regards,
Raju

Message was edited by: Davide Cavallari

[Former Member]

Hi Raman,

one option would be using Work Status.

( http://help.sap.com/saphelp_bpc70sp02/helpdata/en/8c/8529da752f4dc8a5148f095f15fdab/frameset.htm )

Another way would be using formatting within the report. There you can identify your cells and use standard Excel protection mechanism. ( http://help.sap.com/saphelp_bpc70sp02/helpdata/en/87/2e5940a305405f957eb987daf30d5b/frameset.htm )

Hope, it helps.

Regards

Steve

[Former Member]

Hi Raman,

For these kind of requirements, EVDRE comes with formatrange. In the formatrange, you can define your conditions and specify what kind of cell formatting you need. You can search for a document called "rolling forecast in BPC". This document has been published by me only. This document basically talks about rolling forecast. However, it has a section on formatrange. You can refer it for further information.

Another option would be to use conditional formatting. You can select the cell, and apply conditional formatting to them.

Hope this helps.