# I need to update an excel formula

I need to update the following formula:

=IF(\$D249="M",IFERROR(INDEX('[Report 7-14-15.xlsx]Projects'!\$D\$10:\$D\$199,MATCH(\$R10,'[Report 7-14-15.xlsx]Projects'!\$R\$10:\$R\$199,0))&"",""),IFERROR(IF(F10="","",LEFT(F10,FIND("(E)",F10)+2))&IFERROR(CHAR(10)&IF(AP10="","",TEXT(AP10,"M/D/YY")&" (A)"),""),""))

The formula works great...However, when it finds a blank value in AP10, it returns "1/00/00 (A)" as if it found an actual date.

Is there a way to update the formula that if it finds a blank in AP 10, it returns a blank and not "1/00/00 (A)"

I understand that the formula is saying that its essentially blank, I just don't need it to display "1/00/00 (A)"
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
=IF(ISBLANK(AP10),"",IF(\$D249="M",IFERROR(INDEX('[Report 7-14-15.xlsx]Projects'!\$D\$10:\$D\$199,MATCH(\$R10,'[Report 7-14-15.xlsx]Projects'!\$R\$10:\$R\$199,0))&"",""),IFERROR(IF(F10="","",LEFT(F10,FIND("(E)",F10)+2))&IFERROR(CHAR(10)&IF(AP10="","",TEXT(AP10,"M/D/YY")&" (A)"),""),""))))
Author Commented:
OK, I tried your formula and its still returning the "1/00/00 (A)"

I forgot to mention that there is a formula in AP10. The formula returns a blank in AP10. Not sure if excel sees this as "it's not really blank, but it's blank..." kinda thing.

Commented:
HI,

can we have the formula in Ap10

Regards
Author Commented:
It's a big one...

Also an array formula:

=IF(\$R10="","",SUM(IF(SUMPRODUCT(('R:\Count\[Count.xlsx]MFDATA'!\$B\$2:\$B\$5000=\$R10)*('R:\Count\[Count.xlsx]MFDATA'!\$M\$2:\$M\$5000="N")*('R:\Count\[Count.xlsx]MFDATA'!\$C\$2:\$C\$5000<>"")*ISNA(MATCH('R:\Count\[Count.xlsx]MFDATA'!\$B\$2:\$B\$5000,'R:\Count\[Count.xlsx]MFINFO'!\$D\$5:\$D\$7,0))*('R:\Count\[Count.xlsx]MFDATA'!\$Q\$2:\$Q\$5000=""))>0,"",MAX(IF(('R:\Count\[Count.xlsx]MFDATA'!\$B\$2:\$B\$5000=\$R10)*('R:\Count\[Count.xlsx]MFDATA'!\$M\$2:\$M\$5000="N")*('R:\Count\[Count.xlsx]MFDATA'!\$C\$2:\$C\$5000<>"")*ISNA(MATCH('R:\Count\[Count.xlsx]MFDATA'!\$B\$2:\$B\$5000,'R:\Count\[Count.xlsx]MFINFO'!\$D\$5:\$D\$7,0)),'R:\Count\[Count.xlsx]MFDATA'!\$Q\$2:\$Q\$5000))),IF(SUMPRODUCT(('R:\Count\[Count.xlsx]SRDATA'!\$B\$2:\$B\$5000=\$R10)*('R:\Count\[Count.xlsx]SRDATA'!\$M\$2:\$M\$5000="N")*('R:\Count\[Count.xlsx]SRDATA'!\$C\$2:\$C\$5000<>"")*ISNA(MATCH('R:\Count\[Count.xlsx]SRDATA'!\$B\$2:\$B\$5000,'R:\Count\[Count.xlsx]SRINFO'!\$D\$5:\$D\$8,0))*('R:\Count\[Count.xlsx]SRDATA'!\$Q\$2:\$Q\$5000=""))>0,"",MAX(IF(('R:\Count\[Count.xlsx]SRDATA'!\$B\$2:\$B\$5000=\$R10)*('R:\Count\[Count.xlsx]SRDATA'!\$M\$2:\$M\$5000="N")*('R:\Count\[Count.xlsx]SRDATA'!\$C\$2:\$C\$5000<>"")*ISNA(MATCH('R:\Count\[Count.xlsx]SRDATA'!\$B\$2:\$B\$5000,'R:\Count\[Count.xlsx]SRINFO'!\$D\$5:\$D\$8,0)),'R:\Count\[Count.xlsx]SRDATA'!\$Q\$2:\$Q\$5000)))))
Commented:
pls try EDITED CODE
``````=IF(\$D249="M",IFERROR(INDEX('[Report 7-14-15.xlsx]Projects'!\$D\$10:\$D\$199,MATCH(\$R10,'[Report 7-14-15.xlsx]Projects'!\$R\$10:\$R\$199,0))&"",""),IFERROR(IF(F10="","",LEFT(F10,FIND("(E)",F10)+2))&IFERROR(CHAR(10)&IF(AP10=0,"",TEXT(AP10,"M/D/YY")&" (A)"),""),""))
``````
Author Commented:
Rgonzo...

I tried your formula, but its the same exact one as my original...?????
Commented:
EDITED

Author Commented:
ok.....getting closer....for cells that have a value in R10, it works perfectly.

For cells where there is no value in R10, lets say r11, r12, r13, etc, the formula returns (A). I would need that to return blank...
Commented:
then try
``````=IF(\$D249="M",IFERROR(INDEX('[Report 7-14-15.xlsx]Projects'!\$D\$10:\$D\$199,MATCH(\$R10,'[Report 7-14-15.xlsx]Projects'!\$R\$10:\$R\$199,0))&"",""),
IFERROR(IF(F10="","",LEFT(F10,FIND("(E)",F10)+2))&IFERROR(CHAR(10)&IF(OR(AP10="",AP10=0),"",TEXT(AP10,"M/t/jj")&" (A)"),""),""))
``````

Experts Exchange Solution brought to you by