Skip to Content
author's profile photo Former Member
Former Member

How can you lock a cell in BPC spreadsheet using EVDRE or EVEXP?

Does anyone know how to lock a cell within a BPC spreadsheet? I can check the box to lock the cell, but if protect the spreadsheet then I'm unable to drill down on my rows. For example, I'm working on planning, but Jan-July are actuals, with Aug-Dec being forecast. I need to lock users from even being able to enter anything into Jan-July. How I'm seeing the security work is that a user can enter new numbers in the Jan-July columns, but the new information won't send to the database; however, it would be nice if we could lock those particular cells so the user can't enter anything in them.

Thank you for your help.

Teri

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Jun 13, 2008 at 05:05 PM

    In your custom format options, find a method, e.g. CATEGORY = ACTUAL, to define a format that locks each cell. Then use the Protect Workbook option (eTools > Workbook Options) to lock the workbook. This should allow your report to expand as desired and lock users out from changes to the actuals.

    I also suggest using the "GetOnly" parameter to prevent DRE from attempting to send actual data back to the server. Most users should be unable to submit based upon security. However, it is "cleaner" to prevent DRE from attempting to submit and having the records rejected.

    Add a comment
    10|10000 characters needed characters exceeded

    • Teri - I'm going to assume the data needs to be locked dynamically by column. Here are the format range options you can use to dymamically lock the cells in EVDRE:

      (1) Base the lock on one of the dimension key values in the column. For example:

      - Criteria: KEY="ACTUAL"

      - Evaluate In: COL

      - Format: whatever format is applied be sure the LOCK property is set correctly

      - Use: LOCK

      - Parameters: leave blank

      - Apply To: DATA

      (2) base the lock on one of the header values, which can be defined by an IF statement. In this instance, change the Criteria to something like the following:

      - Criteria: HEADING="LOCK"

      Both of these options assume the use of EVDRE report and dynmic formatting. In a static report, you can format the cells manually and simply protect the worksheet. Dynamic formatting is more limited using the other expansion options (EVEXP, EVNXP or EVENE).

      Hope that helps...

      Jeff

  • author's profile photo Former Member
    Former Member
    Posted on Jun 15, 2008 at 03:12 AM

    GETONLY is the best!

    You can also use Work Status to lock down a range of data. This will inhibit users sending new data to a locked data range.

    Hope this helps, please reply with an update.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi,

      you can use data validation.

      Or you can protect the sheet like this:

      go to all the cells they are able to input and make them unlocked (format - cells - protection tab) and make them unlocked

      then go to tools - protection sheet - and only select unlocked cells

      Now they will only be able to select the cells you've unlocked.

      Hope this makes it clear?

      Let me know

      best regards,

      Benjamin

  • author's profile photo Former Member
    Former Member
    Posted on Jun 13, 2008 at 05:39 PM

    I think that the easiest way is indeed using a getonly range

    best regards,

    Benjamin

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.