Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

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.


Similar Excel Video Tutorials

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.




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.


Hello,

I have written a vlookup formula in which it is looking up a specified cell value against a table on another worksheet. I want the formula to return a "-" if there is an error, or an empty cell with no information on worksheet with the defined range.

For example, on one sheet I have a table


MVRs

state MVR Fee No Hit Credit
OH


I want the OH cell to reference a range of cells on another worksheet that contains a full listing of active states with MVR Fee and No Hit Credits

Alabama blank blank
Arkansas $2.00 $1.00
Arizona $5.00 $1.00
Ohio blank blank

The formula I have written is as follows:

=IF(ISERROR(VLOOKUP(AL!$C$82,'Forms & Filings'!$A$5:$E$48,2,FALSE)),"-")

I was expecting this formula to return the exact value from the table, and if there was nothing (blank) to then return a -

Instead, when I run the formula, it returns FALSE in the cell...I'm not quite sure why.

Does this have something to do with the formatting of the cells? Both worksheets (in the same workbook) are formatted as currency.

Any help would be greatly appreciated! Thanks in advance.


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!


Hi Excel Gurus,

I'm looking for a formula that can do the following:

look @ a range of dates
see if the number below any of the ranges cells equals 2
if one does then return 'yes'
if any of the cells in the range are blank and one equals 2, then it returns 'yes'
if none of the cells equals '2' and at least one is blank, then (nothing)
if all of the cells have a value, but none equal 2, then 'no'


10/1/2007 11/1/2007 12/1/2007 1/1/2008
1 1 1 1

cell output = "no"

10/1/2007 11/1/2007 12/1/2007 1/1/2008
1 1 2

cell output = "yes"

10/1/2007 11/1/2007 12/1/2007 1/1/2008
1 1

cell output = (blank)

Thanks in advance for your insights and I look forward to seeing what you can come up with.

=IF(OR(HLOOKUP(C3,G2:J4,2,FALSE)=2,(HLOOKUP((DATE(YEAR(C3),MONTH(C3)+1,DAY(C3))),G2:J4,2,FALSE)=2),(HLOOKUP((DATE(YEAR(C 3),MONTH(C3)+2,DAY(C3))),G2:J4,2,FALSE)=2),(HLOOKUP((DATE(YEAR(C3),MONTH(C3)+3,DAY(C3))),G2:J4,2,FALSE)=2)),"On Time","")

I've been trying to figure this one out for hours. Only arrived at the following, but couldn't result in blank and the condition of the range which allows for a response only once either a 2 exists or a value is included in the entire range.

Thanks,
The Novice


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)


Hi everyone - I've seen a few posts on various forums with questions that initially seem similar to mine, but the given answer doesn't help me (e.g., here). I'm using XL2007 with Windows XP. Here's my issue:

I have a table of values with a header row (the header row is a series of time points - 0, 4.5, 9, 13.5, 18, etc.). Also, I have two cells that I am referencing for start and stop time points (so, let's say I want to use 4.5 and 13.5). Ideally, my formula would be able to use a time point for an HLOOKUP in the table and then drop down to the appropriate row to mark that cell as the part of the AVERAGE function range. My first attempt looks something like this:

=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,2,FALSE):HLOOKUP($B$3,$D$5:$M$17,2,FALSE))

Excel doesn't like the above formula and my guess is because the HLOOKUP gives me values not cell addresses. Is there a function I can insert to get the cell address from the HLOOKUP? I tried to incorporate the ADDRESS and MATCH functions unsuccessfully. But I'm not even sure if those are what I need.

In the end, I want to be able to change the time points in the referenced cells (for this example, B2 and B3) and have my table of calculated values adjust their averages based on the new window of time points given.

Define my average window:
B2=4.5
B3=9

My table of averages:
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,2,FALSE):HLOOKUP($B$3,$D$5:$M$17,2,FALSE))
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,3,FALSE):HLOOKUP($B$3,$D$5:$M$17,3,FALSE))
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,4,FALSE):HLOOKUP($B$3,$D$5:$M$17,4,FALSE))
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,5,FALSE):HLOOKUP($B$3,$D$5:$M$17,5,FALSE))
etc...

Thanks for the help! Please let me know if something isn't clear.


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))


Hi Group,

I have a range of cells (H10:H24) that contain a 'nested IF-OR formula'
that returns either blank or a value.

How do I set conditional formatting to have the cell not filled if
the formula returns blank but filled if it returns a value?

Thanks for any help
Martin





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 need to create a formula that ignores blank cells or error cells(#VALUE!).
My current formula is:

=SUM(I24*L24)+(J24*M24*K24) - Result is placed in N24. I then have the
result from N24 copied into cell G24 for printing on a quotation.

Which returns the correct sum if a number exists in all cells. However if
cell I24 is blank I want the result in G24 to also be blank.

I tried using conditional formatting to make the number white (backround is
white) if there is an error, but, that did not work either.

Any help appreciated.



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 have a small glitch with a conditional formatting formula that I was once told could be solved by rounding. I can not find the information I was given so I must ask again for this help.

I have a column of times similar to bellow:
_______A
1____5:00:00
2____5:00:00
3____5:00:01
4____5:02:01
5____5:05:01
6____5:08:01
7____5:12:01
8____5:12:01
9____5:12:01
10___5:15:01
11___5:19:01
12___5:22:01
13___5:22:01
14___5:22:01


I am using conditional formatting to blank out the start and end repeated times as follows.


Cell A2: Cell Value is equal to =A1 (then dark blank out)
Cell A3: Cell Value is equal to =A$1+A2-A3 (then dark blank out)
_______Cell Value is equal to =A2+A3-(A$14+TIME(0,0,1)) (then dark blank out)
Cell A4: Cell Value is equal to =A$1+A3-A4 (then dark blank out)
_______Cell Value is equal to =A3+A4-(A$14+TIME(0,0,1)) (then dark blank out)
...and so on.

When it works properly I would only see the times in cells A1, A3 to A12 and A14.
When the glitch occurs I would see the times in cells A1, A3 to A14.


Because of the 0:00:01 (needed so the formatting will DARK BLANK Cell A1 and A2) the dark blanking of A13 and A14 sometimes does not work depending on the times in the cells. Most of the time it works fine, but there seem to be rare situations that the formatting doesn't work.

As I said before there was someone on a board much like this one that told me to ad a rounding function to the formula, but as with many functions in Excel I'm not fluent enough with it to know how to apply it.

Please help
Regards


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.


Good afternoon

I am trying to do up some formula for a cell (CELL A) which refers to a calculation of another two cells (Let's say CELLS B & C). A value in Cell A can only have a value of between 2 and 10 inclusive) (because Cells B and C can only be a value of 1 to 5).

If there is no value in Cells B & C, the result in Cell A will be zero or 1, hence invalid and I want Cell A to give just a blank cell which is working.

This part I have worked out but the next part is that, another cell (CELL D) is to use a number of nested IF formula to produce a result. For example, if Cell A comes up with a result of 9 or 10, then it displays the word "EXTREME", if it is 7 or 8, it displays "HIGH" etc etc all the way down to a value of 2 where it displays "LOW".

Again, a value of 0 or 1 is not valid so in CELL A, it is blank but I can't get Cell D to also be blank. It automatically puts up 'EXTREME"

I have probabaly totally confused and if you got this far in the thread then I am impressed! but if you can help that would be great.


Here is my formula. It needs work. =IF(D9="",B9, IF(D9="x",I9, IF(I9="","",)))

If cell D9 is blank I want my cell to enter the date from B9. This works. If D9 has an X then I want the date from I9, this works too, but only if there is a date in I9. Sometimes I9 can be empty for days or weeks and in that case I want my cell to be blank. Right now when I9 is blank it returns a value of 0-Jan and I want that blank instead. If I change the formatting to General instead of date then it displays a 0 and then all my other cells with dates in them turn to numbers. So how do I get the exact value from I9 and B9 to my cell independent of the cell formatting?

Also when the cell generates a date I want it to turn green. I have tried conditional formatting, but it doesnt seem to have an option to turn a cell a color only when there is something in the cell.

Thanks.


http://img204.imageshack.us/img204/9...celproblem.jpg

Hi everyone -

My project this afternoon 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?


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.