asked on

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)"

Missus Miss_Sellaneus

=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)"),""),""))))

wrt1mea

ASKER

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.

Thank you for your help

Rgonzo1971

HI,

can we have the formula in Ap10

Regards

wrt1mea

ASKER

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)))))

Rgonzo1971

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)"),""),""))

Open in new window

wrt1mea

ASKER

Rgonzo...

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

Rgonzo1971

EDITED

=0 instead of =""

wrt1mea

ASKER

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...

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wrt1mea

ASKER

BINGO!!!!!!!!!

Thanks for the help. The solution is perfect....