View Full Version : Help with ISBLANK
=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>)
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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.