Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



How To Make Cell Blank If #n/a

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi All,

I have a cells with a formula that references a table, =HLOOKUP($D$6,Benefits,2,FALSE)


Not all cells are used all the time and appear with #N/A. I want to maintain the formula, but if it returns #N/A I want cell to appear blank. If I can do this with conditional formatting that would be great. Then I won't have to change each cell's formula.

Thanks so much for your help.

View Answers     

Similar Excel Tutorials

Connect the Line for a Chart in Excel and Skip Blanks
How to prevent a line chart from having a blank spot or also dropping to zero when there is a blank cell or a zero ...
Delete All Empty Rows or Blank Cells from a Range in Excel
How to quickly delete all empty cells or rows from a range in Excel.  This allows you to quickly clean your data to ...
Find the Next Blank Row with VBA Macros in Excel
Learn how to find the next empty cell in a range in Excel using VBA and Macros.  This method will skip any blanks ...
Remove Vlookup #N/A Error in Excel
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...

Helpful Excel Macros

Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Change Formulas to Absolute or Relative References
- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means

Similar Topics







Hi guys,

I have a workbook (A) with cells calculated with this formula: =IFERROR(C13/D13-1;""). As you can see, if this calculation returns an error, then it displays a blank value.

Then, on another workbook (B), I simply "pick-up" these results to summarize them with this formula: ='[A.xlsx]Sheet1'!$E$15 and this cell is formatted with percentage style.

After that I created a conditional formatting rule that will fill this cell red, if the cell value is less than 0, green if it's greater than 0. I also created this rule to apply no filling colour in case the cell is blank: =""

All works fine as long as there are values. The problem is that when the calculation formula returns an error (blank on workbook A), the conditional formatting returns green even without any number being returned.

I browsed through the forum but everyone recommends using a conditional formatting based on a formula like = "" but that's what I'm doing and it just won't work.

Appreciate your help.




-I have two columns of numbers.
-There are blank cells in each column that appear sporadically.
-In each row of numbers, I want to create a conditional format that highlights a cell if the number in the cell is greater than a designated value.
-Currently I am using: Conditional formatting 'formula' =ABS(C4-C3)>.1 OR =ABS(D4-D3)<.1 for the respective column of numbers.
-This formula does not work now that I have empty cells between the values. All the blank cells are highlighted and the subtraction can not take place if the cell is blank.
-I want the formula to not see the blank cells and make the comparison between the first vale and the next vale in the series.
-I do not want to sort the blank rows out and then replace.
Thank you,
Daniel

Please see attachment: TESTAE042701.csv



Hi everyone -

My project today has been figuring this formula out and... it's not going well :P

What I need is to use conditional formatting in cell A2 to accomplish the following:

"If for the range C2:H2 any cells are NOT blank, check cells C$1:H$1. If ANY of those corresponding cells in row 1 are blank, highlight cell A2 red."

I'm going to apply this formula in Column A for any customers added to the list (hence the $ in the C$1:H$1 reference). So cell A2 would NOT be highlighted because all quantities in cells C2:H2 have a corresponding lot number above. Cell A3 WOULD be highlighted because cell D3 does not have a corresponding number in cell D1. Does that make sense?

Also posted on ExcelForum.com: http://www.excelforum.com/excel-gene...ing-blank.html


I seem to have a problem with a formula not recognising a blank cell.

In cell X4, I have the following formula:

=IF(L4="",0,IF(I4="S",0,'Employee Master'!AB5*M4*L4))*IF(N4=9999,1,N4)

In L4, the formula is:
=IF(A4="","",IF(F4="OVERTIME",1,IF($J4="C",VLOOKUP($K4,Casual_Days_Lookup,HLOOKUP($F$1,Casual_Days_Lookup,2,0),0),VLOOKUP($K4,Permanent_Days_Lookup,HLOOKUP($F$1,P ermanent_Days_Lookup,2,0),0))))

Nasty, I know....

These formulae work great as long as the result of the L4 cell is not equal to "". As soon as that happens, X4 returns the #VALUE error.

To test if there was a problem, I put the following formula in cell X5:
=IF(L4="","EMPTY","FULL")
which returns "EMPTY" if the result of L4 is "".

Is someone able to explain to me why I'm getting a #VALUE error in cell X4 instead of "0" when L4 returns "", but an "EMPTY" value when testing for the ""???

I keep looking at it and looking at it....I'm sure it's something simple, but I just can't see it!!!

I'm using Excel 2003.

Thanks,
gooniegirl180


I am trying to make a conditional formatting formula that applies formatting based on whether the column A cell has a value.
Basically, if the conditional formatting is applied to cells G3:L3 and A3 is blank (or value is blank/0) then apply formatting.

I have attached an example (that doesn't work this way)

How can I change the formula to do this?


Hi,

Coud anyone help how I can get around using =ISBLANK to return true blank cell result when a cell contains a formula?

For example I would like to use conditional formatting to colour cell RED if B1 does not match A1. If I use ISBLANK this still returns the same false result (ie colours the cell RED) on cells which contain no match but contain a formula what has resulting in a blank expresson (ie "") which returns a blank cell.


What I want to do is be able to make a formula that references a blank cell, that returns a blank cell.

So I have =A1. If A1 is blank that returns a 0. I want it to return an blank. Is there a way to do that?


I want to make a graph where I have numbers and cells that are blank. Right now I have numbers and zeros so when my data stops the graphs jumps down to zero and stay there.

Hope that's clear, thanks!


Formula
=IF(ISNA(HLOOKUP(E735,Pricing!$B$3:$AP$5,3,FALSE)),"",HLOOKUP(E735,Pricing!$B$3:$AP$5,3,FALSE))*P735

Works perfect except on a blank row where it naturally returns #VALUE!. I would normally do a simple [=IF(A735,] at the beginning of the formula and [,"")] at the end but I can't make it work here.

Can someone help me clean this up? Thanks


Hi all,

I'm using the following as conditional formatting but the green one isn't quite working as intended - it's formatting blank cells as green when I want them to stay blank

Goes Red: Formula is =AND((G4=TODAY()),(H4=""))

The reason for the H4="" is that the H4 column is completed date - I want these cells to change colour only if there is no completed date i.e. they're outstanding. Otherwise the cell should stay blank - in the case of there being nothing in G4 or H4 the cell stays blank.

The red formula seems to be working OK, blank cells not affected (when I remove the green condition the cell stays blank), but blank cells are going green when this condition is present.

Confused much? :D

Ruth


I have a basic VLOOKUP formula. But, the table it is looking at will often have blank cells and non-blank cells. For the cells with dates in them, the formula works fine. For the cells without dates, it pulls back a zero, rather than the blank cell.

What can I add to this formula to allow the blank cells to be returned with a blank cells instead of zeros?

=VLOOKUP($B4,'Reference Tables'!$D$3:$E$200,2,FALSE)




I am using this formula;

=VLOOKUP(A1,Sheet1!A:L,10,FALSE)

In worksheet that contains my array, the cells in column 10 either have a time in them (in 24hr format), or are completely blank. The formula is working fine as long as there is a time in the cell column 10. If the cell in column 10 is completely blank, the forumla returns 0:00 which is midnight. That's not what I want. If the cell in column 10 is blank, I want my formula result to be blank. How can I accomplish that?

I am trying to have a conditional format where if there is a value in cell A7 then C7 will be become highlighted yellow. The problem is that we made a change and the A7 value is now determined by a formula, even if the formula returns a blank the old conditional format we used thinks the formula is a value. Does anyone have any idea how to adjust or change the formula we were using? The old formula we are using is:

=AND(NOT(ISBLANK(A7)),ISBLANK(C7))


I've got this OR/AND conditional formatting formula in all cells across 4 columns:

=OR(AND(F22>$C$6,ISNUMBER(SEARCH("downstream",$B$8))),AND(F22>$C$4,ISNUMBER(SEARCH("upstream",$B$8))))

It works great. When I apply my pivot filter in $B$8 and choose either "upstream" or "downstream" it correctly colors my pivot table cells 'Red' as specified by my conditional format criteria, if the cell value is also greater than > than the absolute reference cell threshold value in $C$6. Great, right?

But when I add a field in my pivot table where I need it to look at a different absolute reference cell value, which I need it to again color the cell G22 'Red', IF the cell value is less than < than the absolute reference cell threshold value in $D$6, suddenly the formula doesn't work properly. It shades some cells 'Red', that are lower in value than $D$6, and some that are higher. It also shades rows 'Red' outside the pivot table area...

Here's my formula that doesn't work (see below). I think it has something to do with the condition if the cell value is zero '0' or 'blank'. I think I need to make this formula more robust to accomodate those 2 conditions. But then again, why did the first formula condition work perfectly?

=OR(AND(G22

In a pivot table how does one set up conditional formatting with a formula
with references to calculated field values such that the conditional
formatting can be copied to other cells in the pivot table? I can set up the
conditional formatting for a single cell in the pivot table just fine. But
when I attempt to copy the formatting from one cell in the data area to
another cell, the formula in the conditional formatting is not using relative
references. I am running Office XP.





I have a vlookup formula which looks at a table and returns the value of a cell which cross-matches the value in the row and specific column (Not sure I am explaining this correctly). Let me see if I can be more clear. I have test questions which only apply for certain workout types. My formula looks at the workout type (column B) and matches the question ID (Row 4) to the table on sheet 2. Where these intersect, the cell is either blank or contains a "x". I would like to replace the formula with conditional formatting to gray the cell out if where the workout status and question ID intersects is blank.

I have attached a spreadsheet. The top table on Sheet1 contains the results of the vlookup formula. The bottom table on Sheet1 shows how the conditional formatting would look. I need to use conditional formatting because if the question applies to the workout status, the tester will need to input into the cell. If the workout status does not apply, they do not need to answer the question. I need this to be visible so the tester does not waste time answering questions which do not apply.

The table for the vlookup formula is on Sheet2 of the workbook

Please let me know if any additional clarification is needed. AND a big thanks of appreciation for the help/support.

I have a large spreadsheet which I require people to regularly complete. Due to the complexity of my business there are 180 columns, each of which has a variety of rules towards its completion.

I have used conditional formatting to check as many of the rules as possible (e.g. if somebody enters that delivery is required then the columns for delivery address cannot be blank).
Each column has the same conditional formatting but can have 1,2 or 3 rules. Min is always 1.
Most columns are fairly unique conditional rules.
There is a variable number of rows each time it is filled out (i.e. people add rows as required) but they copy and paste an existing row so conditional formatting is copied as well.
All conditional formatting rules use the "formula" setting and nearly all use many "AND", "OR" statements to make all the necessary checks.
If a cell is found to have failed the validation checks (e.g. is blank when should have an entry, has an entry when it should be blank) then it highlights the cell in red.

I now want to have a count of the number of "red" cells to show me quickly if it has been completed "correctly" or not.

I have written the vba code to check all the cells but cannot find out how to record if the conditional formatting is being used or not. "colorindex" returns the original colour of the cell not the conditional formatting.

Any help greatly appreciated.


I have the following formula in a column:
=IF(ISBLANK(HLOOKUP(xxx)),NA(),HLOOKUP(xxx))

Basically, do an hlookup and if the value returns a blank, return NA(). Basically, I'm returning #N/A.
Why would I return an error for my column? Because the column is being used for a line chart and I don't want to chart zero values. By doing the NA(), it returns #N/A and thus no zero values get charted.

My problem is this, my column of values looks ugly w/ the #N/A. I'm trying to conditionally format so that if any cell in the column has #N/A, then the font will equal the pattern so essentially, the #N/A will be invisible. No condition in the conditional formatting is working either in "Cell Value Is" or "Formula Is". What does the condition have to be in order to catch the #N/A? I've tried "#N/A", "NA()", NA(), , 0, isblank(), iserror(), etc., etc.,...

Is there a better way to do all of this?

Thanks.


I am using a vlookup formula to reference a cell on another tab. I have tried 2 different formulas to do this.

The first one I used:
=IF(ISNA(VLOOKUP($A10,'ESC''s'!18:18,4,FALSE))," ",VLOOKUP($A10,'ESC''s'!18:18,4,FALSE))
I had issues when copying this formula over across the row into other columns to reference the other cells. I couldn't get the formula to automatically adapt the cell location without manually changing it it.

I then had someone explain that I could reference another cell location that would help it to adapt to my needs, which works, but still returns a 0 instead of a blank.

This is my new formula:
=IF(ISNA(VLOOKUP($A10,'ESC''s'!18:18,C1,FALSE)),"",VLOOKUP($A10,'ESC''s'!18:18,C1,FALSE))


I need a blank cell or else my conditional formating on the spreadsheet fails. I know this has been discussed before, but nothing seems to work when I try it.


I am trying to apply conditional formatting to a range of cells, let's say A1:C3.

I want the cells to have a box around them if they are not blank.

I can do this for once cell with no issue, however when I try to apply it to multiple cells in a range (or named range of the same cells), the formatting is applied if any of the cells are not blank.

I need each cell to apply formatting if and only if that cell is not blank.

I thought a simple formula like

if("","box","nobox")

would accomplish this, but it isn't working.

Box and no box are not going to apply the format of course, but you know what I mean.


In cell D9 I have the the formula

=IF(ISNA(VLOOKUP(C9,Labour!$A$3:$L$12,HLOOKUP(A9,Labour!$B$1:$L$2,2,FALSE),FALSE)),,VLOOKUP(C9,Labou r!$A$3:$L$12,HLOOKUP(A9,Labour!$B$1:$L$2,2,FALSE),FALSE))

This is working fine and is a blank cell if A9 and C9 are blank.
If I have used A9 and C9 to give me a result in D9, then delete what is in A9 and C9 I get 0.00 in D9.
I don't want the 0.00, I want a blank cell again.

How can I solve this problem?

Thanks in advance for any help.

Matt


Hello all,

When the cell's value is above 15 I want another cell to show a warning. I have the warning cell already typed and in white formatting so it is not seen. If the value cell is greater than 15 I want to use conditional formatting to "reveal" it by making the text red. The problem is excel apparently sees a cell with a formula as being >0 because the formula works perfectly except when it is blank. For example, when B1 is blank, it causes B2 to reveal itself. Help?

Here's the formula I'm using in the conditional formatting. It points to L27, which contains a formula.

=AND(NOT(ISBLANK(L27)),L27>15)

Thanks for any help.


Hello All, using Excel XP

I am having a problem writing a formula in conditon formatting in the following example.

Cell A1 can contain a blank or a whole number from 1 to 8. (in this example A1 is blank). In cell B1 I want a conditonal format so that if A1 is blank OR contains an 8 I would like B1 to be shaded in black.

I have tried this formula but it doesn't shade cell B1.

In B1 in the conditional formatting:

Formula Is =A1((OR(A1=8, A1="")))

Would appreciate it if someone could steer me in the right direction on this one.

Thanks, Mike


Hi

I've got this HLOOKUP formula in my sheet:

Code:

=HLOOKUP($O$9,CALENDER!$B$3:$M$34,ROW(A2)-ROW(A$1)+1,FALSE)


How can I get it to return a blank (""), if the value in the table array is blank?

Cheers


Hi

I am a bit stuck. I have entered a conditional format so that the cell (H5) will turn red if it is below 75% of the value of another cell (F5). It turns amber if it is betwwen 75% and 100% of value of the cell (F5), and green if it is over 100% of the cell value (F5).

This all works fine. However I would like to copy this conditional formatting down the column from H5 to H300. When I attempt to do so it keeps the the same cell references in the formulas within the conditional formatting - therefore refering to cell F5.

I would like the formula to automatically change the same cell reference to the F column on that same row. E.g. If the formatting is in cell H15 I want the formula to refer to F15.

Is is possible for this to happen automatically as to do these changes by hand will take forever.

Cheers.

Simon.


I am drawing a blank. I want to use conditional formatting in cell b2 that will outline the cell(I know how to do that) based on a formula. The formula I need would determine if cell a2 is 26,27, or 28.
Or function just returns true/false. I would rather not use 2 formulas.