cancel
Showing results for 
Search instead for 
Did you mean: 

Error on Data Validation EPMformattingsheet

0 Kudos

Hi,

I have an issue trying to define data validation on a member. After defining data validation with any formula without dollar key on rows, defining it for the first intersection of the member (Picture 1) it does not work properly on report, and when i check the data validation on the report directly at the Excel seems that the system does not recognize rows correctly (Picture 2)... it shows the row number incorrectly...

If y set the row with dollar key it works properly, but not dynamic for rows... I tried various things but nothing works (Report optiones, user options, ...)

Any suggestion?

Thanks in advance.

Version EPM: SP 25

BPC 10.1

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

Ddata validation formula has to be referenced according to cells in the format sheet, not on the data sheet. It was already discussed number of times!

Vadim

former_member186338
Active Contributor
0 Kudos

You have to use starting row number in the validation rule as a row number on the formatting sheet related to the row number of the cell with conditional formatting. Hope it's clear.

Vadim

P.S. If you provide full screenshots of the formatting sheet and data sheet I will show you the row number...

0 Kudos

Hi Vadim,

Thanks for the reply.

Of course, as i said on the titled post "Error on Data Validation EPMFORMATTINGSHEET". I defined the data validation on a member in the epmformattingsheet, right clic > EPM > Data Validation.

The member is on column axis, and the row defined is the first, but i cannot make it work...

On the report sheet, i use the excel data validation option to check what the epmformattingsheet is doing, as you can see on the screenshots on the top of the post,  it shows that the custom formula the row selected is incorrect showing large number for the selected row when i check for example for row 8, row 9...

Thanks

former_member186338
Active Contributor
0 Kudos

I can't see row numbers on your screenshots. Instead of 13 you have to use row number of the current cell in the formatting sheet!

0 Kudos

Hi Vadim,

That was right¡ Thanks a lot, the solution was to use the row number of the EPMformattingsheet.

Thanks¡

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

Could you explain what the formula =Y($D13 = 0) does?

Thanks,

Varsha

0 Kudos

Hi Varsha,

Thanks for the reply, the problem is not de formula. I just want to make a data validation dynamic for rows, so i define de formula for the first intersection of the member at the report, defined on the epmformattingsheet, without dollar on the row "$D13".

The problem is that when i refresh the report, the formula at data validation shows not D13, D14, or D14 depending on the row for that member, shows D1048501, D1048501, etc...

If i put dollar on the row section at the formula $D$13, the formula seems to work correctly, but the problem is that is not dynamic, for al rows the formula is always:

number ROW: 13 --> $D$13

number ROW: 14 --> $D$13

number ROW: 15 --> $D$13

....

I just wanna know if someone could make this work... Make data validation with custom formula dynamic from EPMformattingsheet.

Thanks¡

J.