If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
# Value error and how to ignore blank cells
Hey All,
Im having a bit of an issue. In a excel sheet in the colum i have some numbers that are copied from another data source. When these are copied into the sheet Sometimes the cells are blank and hence are preventing the formulas throughout the workbook to display a #value.(=Summary!F74/Summary!$G74) will display #value as Summary!F74 is a blank cell Is there a way I can either make all blank cells have a 0 in them easily or ignore the error message i.e. 190 23 3 2 2 18 1 2 2 9 2 3 Thanks |
#2
|
|||
|
|||
# Value error and how to ignore blank cells
numbers that are copied from another data source.
That automatically throws up a caution flag! (=Summary!F74/Summary!$G74) will display #value as Summary!F74 is a blank cell If Summary!F74 was an empty cell *and* Summary!$G74 was either a numeric number or a TEXT number then the result of that formula would be 0. If Summary!F74 is not truly an empty cell but might contain some kind of unseen TEXT character then you'll get the #VALUE! error. It's not uncommon to pull in unseen text characters when you get data from another source, especially a website. Test the cell Summary!F74 to see if it is empty: =CODE(Summary!F74) If the cell is truly empty that formula will return a #VALUE! error. If the formula returns some number then the cell is not empty. I deal with this just about every day. If you do this often you should download this macro which "cleans" unseen characters from data: http://www.mvps.org/dmcritchie/excel/join.htm#trimall After you paste the data, select the range of cells then run that macro. -- Biff Microsoft Excel MVP "Dannn78" wrote in message ... Hey All, Im having a bit of an issue. In a excel sheet in the colum i have some numbers that are copied from another data source. When these are copied into the sheet Sometimes the cells are blank and hence are preventing the formulas throughout the workbook to display a #value.(=Summary!F74/Summary!$G74) will display #value as Summary!F74 is a blank cell Is there a way I can either make all blank cells have a 0 in them easily or ignore the error message i.e. 190 23 3 2 2 18 1 2 2 9 2 3 Thanks |
#3
|
|||
|
|||
# Value error and how to ignore blank cells
"Dannn78" wrote:
(=Summary!F74/Summary!$G74) will display #value as Summary!F74 is a blank cell Is there a way I can either make all blank cells have a 0 in them easily or ignore the error message Try: =N(Summary!F74) / Summary!$G74 F74 probably is not really empty. It just appears to be blank because it contains "" (null string) or even some other text. By the way, if G74 might be zero or blank (empty or ""), it would be prudent to do the following: =IF(N(Summary!$G74) = 0, 0, N(Summary!F74) / Summary!$G74) ----- original message ----- "Dannn78" wrote in message ... Hey All, Im having a bit of an issue. In a excel sheet in the colum i have some numbers that are copied from another data source. When these are copied into the sheet Sometimes the cells are blank and hence are preventing the formulas throughout the workbook to display a #value.(=Summary!F74/Summary!$G74) will display #value as Summary!F74 is a blank cell Is there a way I can either make all blank cells have a 0 in them easily or ignore the error message i.e. 190 23 3 2 2 18 1 2 2 9 2 3 Thanks |
Thread Tools | |
Display Modes | |
|
|