Skip to Content
avatar image
Former Member

Locking/Unlocking of cells - using epm formatting sheet

Hi all,

We have a specific requirement for one input form as below.

Project and Components are dimensions added to the row axis. Start date and end date are the dates specific to a component which is maintained as master data, and retrieved in the sheet using local member and epm formula. In the column we are overriding with 10 years. So in the report section, users should only enter in the relevant years ( white cells) within the start date and end date. Grey cells has to be prohibited from data entry (by protecting the cells).

Please help us with this scenario - how we can grey out the cells as well as protect it?

Thanks and Regards,

Sreeja V

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Apr 17, 2017 at 12:13 PM

    Sample of conditional formula to be used for conditional formatting and data validation:

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 17, 2017 at 11:59 AM

    For this specific case - only conditional formatting and data validation!

    Add comment
    10|10000 characters needed characters exceeded

  • May 03, 2017 at 10:18 AM

    Start with something simple:

    For example we want to show in yellow all columns with month > 3

    On the report we will have:

    And:

    Conditional formatting will be applied to the range B2:D2 with the formula:=RIGHT(B$1;2)*1>3

    when condition is checked B in this formula will be replaced by: B,C,D from range

    Add comment
    10|10000 characters needed characters exceeded

    • Format of date has nothing to do with RIGHT(...

      RIGHT can be used on text fields, not on date fields!

      For date fields use YEAR()

      And to get last 2 digits = YEAR(XX)-2000 :)

      To compare it with part of text string use:

      RIGHT(xx,2)*1

      *1 to convert to number