Locking/Unlocking of cells - using epm formatting sheet

[vsreeja]

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

[former_member186338]

P.S. you have not closed question with my answer:

https://answers.sap.com/questions/54683/index.html

Please close it!

[former_member186338 · Accepted solution]

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

[vsreeja · Accepted solution]

Hi Vadim.

Thanks.. We were able to achieve the conditional formatting part. But need help on giving protection to the greyed out cells.

Regards,

Sreeja V

[former_member186338 · Accepted solution]

You can't have conditional locking of cells in Excel. But you can use Data validation to prevent changing cells based on the condition. Please read Excel help on Data Validation using formula!

[vsreeja · Accepted solution]

Thank You.. Got it.

Regards,

Sreeja V

[vsreeja · Accepted solution]

Hi Vadim,

Need one more suggestion regarding the same.

I have mentioned in my query the column axis is fixed to 10 columns. If the column numbers are dynamic, will we be able to achieve the same.?

When I tried with the below options I encountered following problems:

1) Epmcopyrange with conditional formatting and validation --> In the report it was taking the first cell reference (which we have given in the cell range for epmcopy range formula.) for all the cells.

(Refer to attachment 1)

2) epmformatting sheet --> Here also for all the columns it was taking the same cell reference that we have given in the epmformatting sheet cell reference instead of taking respective column headers.

Regards,

Sreeja V

[former_member186338 · Accepted solution]

Epmcopyrange - don't use.

epmformatting sheet:

use cells relative references related to the cell in formatting sheet!

Ex:

In formatting sheet you have cell F22 then to have a reference to first row in the same column you have to use:

F$1

F will be changed to current column when format is applied to data cell

To reference column B for the same row - use:

$B22

22 will be changed to current row when format is applied to data cell

[vsreeja · Accepted solution]

Hi Vadim,

I was trying in that way only. Please find the same below:

EPMFormatting sheet formula:

=OR(RIGHT($R104,2)<=RIGHT(F$122,2),RIGHT($S104,2)>=RIGHT(F$122,2))

Report after refresh - Please find attached with conditional formula

Thanks and Regards,

Sreeja V

1.jpg

[former_member186338 · Accepted solution]

What cell do you have on the formatting sheet with conditional format?

[vsreeja · Accepted solution]

Cell reference in the formatting sheet - F104.

Please find the screenshot as attached.

[former_member186338]

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

[former_member186338]

In general if in Formatting sheet you have column F and you use F without $ in the conditional formula then F will be changed to the first column of the data range... B in my sample. And the condition itself will invisibly substitude B with B,C,D when checking the rule.

[vsreeja]

Thanks Vadim.. 🙂

I think the date formatting in my input form was creating issue. Date reference cell,i have given in "04/21/2015" format. So it was not taking correct value for comparison.

Now, i recreated the scenario that you have shared. Then realized the formatting issue in the date.

Thanks a lot for your quick reply/help.

Regards,

Sreeja V

[former_member186338]

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

[former_member186338]

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