Blank cells appearing as zeros

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:17 PM GMT

Question

QUESTION:

I am working on Microsoft2003. The blank spaces are appearing as zeros. I do not want this to occur since I will be computing formulas later on. I have been using the following code to identify what is blank and what is not blank. Everything that is blank is appearing as N/A.

=IF(ISBLANK(SubjectRawData!Z9),"N/A",VLOOKUP(SubjectRawData!Z9, Rawtable!$A$2:$B$7,2))

This has been working for half the column of cells. It does not seem to work for the other half and I cannot figure out why this is. Once I get to a certain cell in the column, it will alter my orignal value and replace it with another random value. This is the code for the cell that does not seem to be working.

=VLOOKUP(SubjectRawData!Z153,Rawtable!$A$1:$B$7,2)

EMily

ANSWER: try using "" or na() rather than "N/A", and let me know how it works

---------- FOLLOW-UP ----------

QUESTION: No it does not work. Once I reach a certain cell in the column, it continues to alter the original value. I have tried this formula on other columns of data as well and it does the same thing. I'm thinking it must be something embedded in my database, but I am unsure of what it is.

Answer

to solve your problem I would need to see the file

now, remotely, I would look the formatting for cells SubjectRawData!Z153, SubjectRawData!Z9 and Rawtable!$A$1:$B$7 and specially that row when the formula stop working

You might have cell filled with spacebars, that appears to be blank but don't

I would try to change your formulas to: =VLOOKUP(SubjectRawData!Z153,Rawtable!$A$1:$B$7,2,0) instead of =VLOOKUP(SubjectRawData!Z153,Rawtable!$A$1:$B$7,2) and =IF(ISBLANK(SubjectRawData!Z9)=TRUE,na(),VLOOKUP(SubjectRawData!Z9, Rawtable!$A$2:$B$7,2,0)) instead of =IF(ISBLANK(SubjectRawData!Z9),"N/A",VLOOKUP(SubjectRawData!Z9, Rawtable!$A$2:$B$7,2))

Yet, I am not sure it will make a difference

Advertisement

©2017 eLuminary LLC. All rights reserved.