Custom Validation Issue

[former_member206132]

Dear All,

When i'm applying with custom validation formula in DM, Custom Validation shows successfull even then result message is #REF

If result message not showing any number then result should be Failed but it showing successfull

Can some body help me on this ?

Even though Result Message is #REF but still custom validation is showing successful.

Best Regards,

Repakula Manoj

[Marc_Kuipers · Accepted solution]

Manoj

The cell you define the validation on, needs to be 'boolean' (i.e. give a true or false)

Your cell is SUM, so that returns a number - which will not work (as you see)

You can use the Excel ISNUMBER() function

[former_member206132 · Accepted solution]

Thanks a lot Marc!!

It's worked!!

[olga_vasilieva]

manoj.kumar161, what I mean is that you need to enable formula calculation.

Also, Marc is right: as your validation cell (the first one you choose) you need to choose a cell with formula that gives TRUE or FALSE as a result. Your current formula is SUM which doesn't produce those results. You can put an Excel formula like "=E2=SUM(A2,B2,C2,D2)".

[former_member206132]

Dear Olga,

Thanks!! yes those will not produce the results, this formula can be corrected.

But logic i'm not understanding in DM.

Best Regards,

Repakula Manoj

[olga_vasilieva]

manoj.kumar161, the logic of custom validations is simple: get formula result value (TRUE or FALSE) and show a message based on that value.

So to use DM custom validation correctly, you need: 1) to have the right formula (that gives you one of the expected values - TRUE or FALSE) and 2) to be sure that it actually works (that Excel updates the value).

In case you have shown in the first message it looks like you put as a validation cell an non-updated cell with value "TRUE", which for custom validation means that check was successful and the "successful validation" message was shown accordingly. I think after you update it you'll get an expected error that you need to change formula for custom validation.

[Marc_Kuipers]

Hi Manoj

See: https://blogs.sap.com/2017/02/23/disclosure-management-custom-validations/

You defined the validation on the wrong cell. You need to create a cell (with a formula) that results in a true/false

Marc

[former_member206132]

Dear Marc,

Suppose if i select with formula For Example in below case i have selected D5 Cell

It shows an error message that first select either TRUE or FALSE

[olga_vasilieva]

Hello manoj.kumar161,

Since Excel shows "TRUE" as a formula result despite having #REF! in parameters, I'm going to guess that you have automatic formula calculation disabled in Excel. Please check that first.

SAP DM only uses values that Excel provides, it doesn't run your check formula by itself.

[former_member206132]

Dear Olga,

This #REF can be rectified with the formula & Yes automatic formula is already disable in Excel