Formula seems to work and then get #Value error

[Cammandk]

This formula seems to work ok and then suddenly kicks up #value error.
As this is a key formula in my many sheets I would appreciate any help.
Thanks DK.
IF(ISBLANK(!$AB19),IF(!$I19="R",0,IF(TEXT(EDATE(IF(!$V19="P",!$AR19,!$J19),IF(!$V19="P",0,!$AS19)/30),"yyyymm")=TEXT(!AV$5,"yyyymm"),!$U19,0)),IF(!$I19="R",0,IF(TEXT(EDATE(IF(!$V19="P",!$AR19,!$AB19),IF(!$V19="P",0,!$AS19)/30),"yyyymm")=TEXT(!AV$5,"yyyymm"),!$U19,0)))
AB19 Date Date
I19 "" or "R" Text
V19 ""/ "P"/"U"/"A"/" ""F" Text
AR19 24-Apr-14 Date
J19 24-Apr-14 Date
AS19 0 / 30 / 60 Number
AV5 Apr-14 Date
U19 100.00 Number

 

[Debraj]

Hi DK..

try this..

=IF(ISBLANK(WhatTheSheetName!$AB19),IF(WhatTheSheetName!$I19="R",0,IF(TEXT(EDATE(IF(WhatTheSheetName!$V19="P",WhatTheSheetName!$AR19,WhatTheSheetName!$J19),IF(WhatTheSheetName!$V19="P",0,WhatTheSheetName!$AS19)/30),"yyyymm")=TEXT(WhatTheSheetName!AV$5,"yyyymm"),WhatTheSheetName!$U19,0)),IF(WhatTheSheetName!$I19="R",0,IF(TEXT(EDATE(IF(WhatTheSheetName!$V19="P",WhatTheSheetName!$AR19,WhatTheSheetName!$AB19),IF(WhatTheSheetName!$V19="P",0,WhatTheSheetName!$AS19)/30),"yyyymm")=TEXT(WhatTheSheetName!AV$5,"yyyymm"),WhatTheSheetName!$U19,0)))

Update Sheet Name Accordingly..

 

[Faseeh]

Cammandk

can you specify the particular case where it give Value Error?

Deb,

i think without sheet name & just exclamation mark, this formula will not execute at all.

 

[Cammandk]

The formula is held within a defined name so that I can amend it if needed as it is used across many different sheets.
The ! mark allows the formula to be used across different sheets. Thats my understanding?
DK

 

[Somendra Misra]

@Cammandk

Can you can post a sample file with the formula where it gave you right result and where it gave you error, with the purpose of the formula.

Regards,