Skip to Content
0

Locking/Unlocking of cells - using epm formatting sheet

Apr 17, 2017 at 11:45 AM

132

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded

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

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

Please close it!

0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Vadim Kalinin Apr 17, 2017 at 12:13 PM
0

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


conf.png (20.2 kB)
Show 8 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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

0

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!

0
Former Member
Vadim Kalinin

Thank You.. Got it.

Regards,

Sreeja V

0
Former Member
Vadim Kalinin

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

0

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

0
Former Member
Vadim Kalinin

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

1.jpg (130.8 kB)
0

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

0
Show more comments
Vadim Kalinin Apr 17, 2017 at 11:59 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin May 03, 2017 at 10:18 AM
0

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


cf1.png (120.2 kB)
cf2.png (43.8 kB)
cf3.png (43.9 kB)
Show 3 Share
10 |10000 characters needed characters left characters exceeded

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.

0
Former Member

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

0

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

0