PDA

View Full Version : Help with ISBLANK



SUJU
10-26-2005, 03:19 AM
=IF(ISNUMBER(INDEX(data!D:D,MATCH(A6&B6,data!A:A,0))),INDEX(data!D:D,MATCH(A6&B6,data!A:A,0)),0)


Hi

Can someone pls amend the above formula so that it accounts for a condition when i have a blank cell and it must return this as blank too...and NOT 0.

Thanks in advance

Bob Phillips
10-26-2005, 03:26 AM
=IF(ISNUMBER(INDEX(data!D:D,MATCH(A6&B6,data!A:A,0))),INDEX(data!D:D,MATCH(A6&B6,data!A:A,0)),0)


Hi

Can someone pls amend the above formula so that it accounts for a condition when i have a blank cell and it must return this as blank too...and NOT 0.

Thanks in advance


=IF(ISNA(MATCH(A6&B6,data!A:A,0)),"",
IF(ISNUMBER(INDEX(data!D:D,MATCH(A6&B6,data!A:A,0))),
INDEX(data!D:D,MATCH(A6&B6,data!A:A,0)),0))

Zack Barresse
10-26-2005, 10:59 AM
xld, why the extra call???? You lost me there ..

Why not just ...


=IF(ISNUMBER(INDEX(data!D:D,MATCH(A6&B6,data!A:A,0))),INDEX(data!D:D,MATCH(A6&B6,data!A:A,0)),"")

Bob Phillips
10-26-2005, 11:15 AM
xld, why the extra call???? You lost me there ..

Why not just ...


=IF(ISNUMBER(INDEX(data!D:D,MATCH(A6&B6,data!A:A,0))),INDEX(data!D:D,MATCH(A6&B6,data!A:A,0)),"")

Fogged brain I guess!

Zack Barresse
10-26-2005, 11:25 AM
LOL! Well .. uh .. try harder next time!! ROFL!!

Bob Phillips
10-26-2005, 11:35 AM
LOL! Well .. uh .. try harder next time!! ROFL!!

Noted (where is the hangs head in shame smiley>)

SUJU
10-26-2005, 04:52 PM
Thnx Fireftr and XLD. I have tried that option before. My problem is as follows:
IF the value in data! D:D ISNUMBER, then return that number (including 0's).
IF the value in ISERROR, then return 0
IF ISTEXT then return blank
IF ISBLANK, then return BLANK
IF is #N/A N.A return a 0
I cant figure out how to write the code for these different happenings.

Your input is appreciated.

Zack Barresse
10-27-2005, 09:50 AM
I would suggest putting this in a seperate column/cell ..


=INDEX(data!D:D,MATCH(A6&B6,data!A:A,0))

Then you could easily reference it as such (assuming the cell it goes into is cell E6 and down)...



=IF(E6="","",IF(OR(ISTEXT(E6),ISBLANK(E6)),0,IF(ISERROR(E6),0,IF(ISNUMBER(E6),E6,""))))


Does this work for you?