How to Lock Cells on Local Member Formulas

[former_member595001]

Hello,

I'm trying to lock local member formulas cells. Right now I can lock them but when I change the time dimension in the context and refresh the report they become "unprotected" again.

The formulas I'm using are as followed:

=IFERROR(EPMComparison("finance", EPMMemberID(E33), F33, G33), 0)

=IF(E29="Check Sum", "", IF(ABS(G29)>1, IFERROR(H29/ABS(G29), 0), 0))

I was reading online it has to do with the iferror function, but was wondering if I can get them to lock anyways with them through epmformatting sheet.

Thanks,

Will

[former_member186338]

I have already told you - create a SIMPLE report - reproduce my sample. And do the same what I am doing (including local member names) - you will get a result. Then compare it with your report.

[former_member186338]

P.S. I am unable to check everything on your report - it will take too much time.

[former_member186338]

"When I tried to do as you suggested the Right Click>EPM>Lock Cells/Unlock Cells is greyed" - easy! Incorrect cell selected for right click! You have to select data cell! Look on my sample once again. And please accept the correct answer.

P.S. And it's better to train on very simple report!

[former_member595001]

Unfortunately that didn't make it work either by choosing the data square. 😞

[former_member186338]

First about images - may be you have issue with size limit or limit for 2 images in comment!

Now about your formatting:

In general, if you have some issue - create VERY simple report with single column local member and test using this report.

In the formatting sheet you have to use right click menu to set lock:

And the result will be:

Also I recommend to use normal local member ID's like LM001, LM002 etc... and to use the mentioned ID's on the formatting sheet.

As you can see on my sample the local member lock is working absolutely fine.

[former_member595001]

Oh I never knew there was a limit of captures in a comment. Good to know. When I tried to do as you suggested the Right Click>EPM>Lock Cells/Unlock Cells is greyed out and I cannot chose anything. Thoughts that could make it work?

When I try on any of my epm formulas in the formatting sheet.

Thanks.

[former_member595001]

I figured out what happened. I had 2 of the same report opened. But when I did as you suggested, the cells are still not protected and I get the green triangle in the upper left corner still.

Also, I went back to check on my epm formatting sheet and when I right clicked as before it still shows as neither it being unprotected or protected.

Thanks

[former_member595001]

Not really, but that besides the point. Me asking for help means you should give me some insight on what could be going on with my problem. Like when you said check your EPM formatting sheet and I said it didn't work. You could've said "why don't you attach it and I can take a look at it." You're pretty harsh with your tone.

I gave my problem above that my formulas aren't "locking" or "protecting" when I change the time dimension.

My epmformatting sheet is attached, maybe you can see if I did something wrong.

Thanks.

capture.jpg

capture-2.jpg

capture-3.jpg

capture-4.jpg

[former_member186338]

You don't like my tone, OK!

Just to remind you that my post was exactly about it:

You: "I've tried doing that on the formatting sheet and it didn't work. So I'm not sure what else to try."

Me: "Sorry, but I have no issues with cell lock using formatting sheet! Looks like you are doing something wrong... But without detailed screenshots of what you are doing I can't help."

And I don't like when images are attached using "Insert File" instead of correct "Insert Image" - it's hard to read. I will wait for correct "Insert Image"

[former_member595001]

Unfortunately it won't let me submit my comment with the images. Not sure why it won't do that.

[former_member186338]

"Well what kind of screen shots are you looking for? You need to be more specific for me to get the things for you."

Sorry, but just the opposite!

You need an answer, and you have to provide clear information about your issue. I have already told you that locking cells using formatting sheet is working fine. If you have issues with it - show what you have done on the formatting sheet!

P.S. It's a free service here and you have to do something yourself to get a support.

[former_member595001]

Not really, but that besides the point. Me asking for help means you should give me some insight on what could be going on with my problem. Like when you said check your EPM formatting sheet and I said it didn't work. You could've said "why don't you attach it and I can take a look at it." You're pretty harsh with your tone.

I gave my problem above that my formulas aren't "locking" or "protecting" when I change the time dimension.

My epmformatting sheet is attached, maybe you can see if I did something wrong.

Thanks.

[former_member186338]

Sorry, but I have no issues with cell lock using formatting sheet! Looks like you are doing something wrong... But without detailed screenshots of what you are doing I can't help.

[former_member595001]

Well what kind of screen shots are you looking for? You need to be more specific for me to get the things for you.

[former_member186338]

But how iferror is related to locking???

Use formatting sheet and lock.

[former_member595001]

I've tried doing that on the formatting sheet and it didn't work. So I'm not sure what else to try.

Thanks,

Will