# Make A Cell Display 0 Or Remain Blank Instead Of #N/A?

Aug 15, 2009How can I make a cell display 0 or remain blank instead of #N/A?

I have several cells that are either VLOOKUP or just normal sum functions but they all display #N/A.

How can I make a cell display 0 or remain blank instead of #N/A?

I have several cells that are either VLOOKUP or just normal sum functions but they all display #N/A.

ADVERTISEMENT

Attached is a screen shot of a bit of a file I made to use daily. It needs to be used by many people and always starts out new each day. I'd like to make the comments on the right stay "full size" when the sheet is scrolled down (it needs to be able to scroll). The top 3 lines are "fixed" and don't scroll. I've tried comments ( currently ), pictures, imported pictures...everything I know to try. How can I make the comments box persist without "rolling up" when the remainder of the sheet is scrolled?

View 2 Replies View Relatedi am wondering if there is a way that i can have these cells that depend on formulas to remain blank, until i have my data entered, or that they may remain blank, but give me running totals of only the cells i have inputted.

View 3 Replies View RelatedAmount Amount Item Total Account

$ -

No Profit$ 629.50 $ - $ 629.50 No Profit$ 46.29 $ - $ 46.29 No Profit$ 2,333.01 $ 233.30 $ 2,566.31 233.3

$ -

No Profit

$ -

No Profit

$ -

No Profit

$ -

No Profit

#1 If there is an amount in column B, I want column D to reflect that amount.

#2 If there is no amount in column B, I want column D to state the words "No Profit."

#3 Already in column B is the formula =C1-A1.

I tried =IF(B1=0,"No Profit",B1) but when I copy that formula down the spreadsheet, it inserts "No Profit" into all of column D. I only want "No Profit" to exist when there is a $0 resulting from the formula mentioned in point #3. When there is a blank cell in column D, I want it to remain blank until an amount is entered in that row?

s1. Offset(1, 2).Range("K4:K464").Formula = "=IF(ISNA(VLOOKUP(C2,Sheet2!$A$2:$B$1063,2,FALSE)),"""",(VLOOKUP(C2,Sheet2!$A$2:$B$1063,2,FALSE)))"

How do you get code like above to work in Offset? When i run it the cells remain blank but the code is above in the fx.

On Sheet 1 I have Data Validation Lists set up. On Sheet 2 I have the information populating from what is entered on Sheet 1, only problem is...when I don't have anything entered in the cell(s) on Sheet 1 then the cell(s) on Sheet 2 reflect 0. I would like the cell(s) to remain blank if nothing is entered on Sheet 1.

View 4 Replies View RelatedI have a formula in a cell....let's say column B.

It basically says "If column A is empty,then = 24:00, otherwise = column A".

It works fine and displays as 24:00 in the proper cells, but if you try to add all the 24:00 together it doesn't work. I guess the simple question is, how do I hard code a proper time value from an If Statement.

e.g. =IF(A1="",24:00,A1)

Is there a way to make a cell display the text difference between two cells?

For example of cell E2 reads "Thank Goodness it is Friday" and cell F2 reads "Thank Goodness" can I put a formula in H2 that will show the text that is in E2 but not F2 ( "it is Friday")?

I am using a “borrowed” formula from a coworker that I do not really understand however it is working for me, sort of.

=+IF(ISERROR(FIND("F",UPPER(CONCATENATE($V17,$W17,$X17,$Y17,$Z17,$AB17,$AC17,$AD17,$AO17,$AZ17)))),I F(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($R$2),"NA",""),"FAIL",""),"PASS",""),4)="FFFFFFFFFFFFF FFFFF","FAIL","PASS"),"FAIL *")

What this does is read the range of cells and immediately fails a quality check if it is in a critical cell, or fails if multiple non critical items fail.

I want to use an if/then to make cell blank if there is no data in one particular cell (which means no quality inspection performed). Here is what I tried:

IF(V18="","",+IF(ISERROR(FIND("F",UPPER(CONCATENATE($V18,$W18,$X18,$Y18,$Z18,$AB18,$AC18,$AD18,$AO18 ,$AZ18)))),IF(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($R$2),"NA",""),"FAIL",""),"PASS",""),4)="F FFFFFFFFFFFFFFFFF","FAIL","PASS"),"FAIL *"))

The leaving the cell blank part of the formula works however it allows the remainder of the formula to only work if “fail” appears in the first cell it reads (in this case V18). If the “fail” appears in any of the other critical cells, it does not work at all.

Also as a side project not sure what the substituting to $R$2 does so if anyone knows how to clean this up, let me know.

I am looking for a formula that will work with my current formula, and check that cells a1 and b1 contains information. If they do contain info then the original formula should carry on as usual and leave the answer in cell c1. I also need it so when no information has been entered in cells A1 and B1, C1 will be blank instead of containing a zero.

This is the formula I have at the moment:

=IF(SUM(A1=0,B1>0),B1-A1,"No Charge?")

A B C

10.1810.82

I have 2 worksheets, i have one sheet that has a form with lists, when nothing is selected the cells are blank. On the other sheet I have a few cells which = some of the sheets on the other worksheet. when nothing is selected, the cell displays 0 even though the cell its supposed to be displaying is blank.

View 4 Replies View RelatedI have several hyperlinks that may or may not contain data, the linked cell will display "0" if there is no data. When I print I dont want the "0"s printing.

How do I make the cell display nothing when a hyperlink cell is nothing?

I want my formula to output a blank cell if the answer is 0. Ex. [B1=IF(A1=0, ???, A1)]. I want B1 = A1 if A1 is not 0. I want B1 to output a blank cell if A1 = 0.

View 3 Replies View RelatedHow do I put in iserror in to this to make the cell go blank with N/A is returned. Only ever done this on vlookups. =IF(I15<E8,"TOOLING REQUIRES CALIBRATION","")

View 2 Replies View RelatedI have a spreadsheet that i want to create a part number that contains 4 parts. Ex 1rl9. The cell i want this to be in is blank. What formula do i use to make the second character in the blank cell = r?

View 9 Replies View RelatedAny way of shortening the attached code. The first part 'Sub Loop1()' works great but I can't figure out how to shorten the second part. As you can see the distance between columns is always consistent i.e. add 5 columns to find the next outcome to work out the next result. Ideally I wanted to somehow loop through the next 5 columns again and again until the cell is blank.

[Code] ......

First, I can get the balance formula, and when I drag the square at the bottom right, I am able to drag the entire formula down...I want to do that. However, when I drag it down, the number keeps popping up. I was wondering if there is a way to be able to drag the entire thing down and be able to make it so that it is hidden (the cell is blank) unless the debit or credit cell in the corresponding row is filled. Also, if possible, I want to be able to drag it down to infinity, so it can go on forever, so that there is no need to always drag it down when I add more things in it. I am attaching what I have till now.

View 5 Replies View Relatedsection 100

section 200

100-1

200-1

I have a large spreadsheet with text, numbers and blank cells. Is there a code a can use to make all blank cell have a dash (-) without affecting the other cell with data.

I am calculating blood sugars for my daughter and putting them in a spreadsheet. The formula is simple:

Blood sugar - target blood sugar / 20 which yields the amount of insulin that she needs to take. I want this cell to be blank unless I enter a value in the cell above it.

Next, I take the value from that formula and add it to the amount of insulin that she takes for eating.

The value in the top formula can be 0 or less, but, if the value in the bottom one is less than 0, I want the cell to be blank.

I am using this formula, but if the referenced cell is blank, it keeps putting a 0 in the cell. I want the cell to be blank if the referenced cell is blank.

=IFERROR(INDIRECT($E$1&"!$C4"),MATCH(INDIRECT($E$1&"!$C4"),""))

Does anyone know how to use a macro to make a cell background blank if it is not orange or dark blue?

View 12 Replies View RelatedI want to make a cell blank if nothing is written in another cell. I have used this formula in C4: =IF(ISNUMBER(C3),IF(C3<180,C3+180,C3-180),"")

View 1 Replies View RelatedMake a cell blank if another cell is blank? This is my formula, it checks two dates. It is in cell M5.

View 4 Replies View RelatedBeen doing pretty good up till now but I can not figure out how to get a combo box in the first blank cell of column A. I am using the named range 'Units' and there are almost 900 of them so I need the combo box.

View 8 Replies View RelatedIn range B4:M4 some cells contain values and some are blank. I am trying to write a formula that will omit the blank values and just give me the nonblank values. The code below returns the FIRST nonblank value, but I can't seem to figure out how to drag it across a row to get the others. I want to ensure that the numbers stay in the same order (so no largest/smallest formulas), but the blank cell values need to be removed.

This would then need to be repeated for (B5:M5, B6:M6, ETC).

[Code] .....

The following are on a sheet:

A1 = 5700

B1 = 235

C1 = 17:14

D1 = 5922

$E$1 = 09:01

$F$1= 3

$E$1 and $F$1 are (the only) absolutes/constants.

A2 contains the following:

=IF(A1>D1,B1,IF(A1+B1<D1,"d",IF(AND(C1<$E$1,B1<$F$1),"",B1))))

This translates as:

=IF(5700>5922,235,IF(5700+235<5922,"d",IF(AND(7:14<09:01,235<3),"",235))))

The result is a variable/number (235), "d" or a blank cell ("").

This formula is in a column and works fine where there are numbers in corresponding cells. The problem arises with corresponding cells which appear blank (show no values) but contain references to other cells: they result in a "d" when nothing should be displayed.

So while a corresponding blank cell is correct if it shows no values, it gives me this problem - I don't want "d" or anything.

Three columns.

A - Date last checked

B - Due Date

C - Actual Date checked

Currently column B is formatted to Date and simply has =A+84 and will display a date 3 months in future. However if there is no date in column A, then column B displays a default 1900 date.. Is there a way of making this blank if there is no date in col A?

I have searched and trying to manipulate various formulas with no success...

I am entering an AverageIF formula into cell J15

I want to calculate the average of the folllowing ranges cells J5:J8,J10:J13

However, I want the cell J15 to display blank if cell J14=0

On reflection I am unsure an AverageIF formula is even correct.

i have a formula that will return both negative and positive numbers. If it's negative it obviously shows a "-" sign. How do I force it to display a "+" if the result is positive?

View 9 Replies View RelatedCell J5 (as an example) contains the following sets of data:

Blank,D,W,T,C,DW,DT,DC,WT,WC,TC,DWT,DWC,DTC,WTC,DWTC.

I want cell BW5 to display a * if cell J5 contains either a blank, or a D,W,T or a C. If neither of those 5 sets of data are present then an empty field is required in BW5.

=IF(AND(AU51,J5=,U5=3,DP5=""),"*","")

Copyrights 2005-15 www.BigResource.com, All rights reserved