Hi

I have a list in excel of names and underneath that the customers of the sales person. I have a formula which shows the top 5 customers that each sales person has.

If the person "Shekhar" has only 4 customers it appears as below. I dont want to see the #N/A. Is there a formula to hide or make the cells white. If there are less than 5 customers this #N/A appears.

Name |
Shekhar |

Customers

ABC

XYZ

FDO

CHI

regards

shekhar

Combo1.RemoveItem (0)

End Sub

give a try to this

=if(A2<>clean(A2),"Possible Special Characters","")

else

**The TRIM function removes spaces from text except for single spaces between words. The CLEAN function removes all nonprintable characters from text. Both functions were designed to work with 7-bit ASCII, which is a subset of the ****ANSI character set (ANSI character set: An 8-bit character set used by Microsoft Windows that allows you to represent up to 256 characters (0 through 255) by using your keyboard. The ASCII character set is a subset of the ANSI set.)****. It's important to understand that the first 128 values (0 to 127) in 7-bit ASCII represent the same characters as the first 128 values in the Unicode character set.
**

Regards

D

Hi

My data looks somewhat like below;

A B C D E F

#N/A |
|||||||||||||

Volume (mln gal) | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | YTD |

Plan | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A |

Actuals | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

If A1 (which comes as a result of a formula) is #N/A then i dont want to see the data underneath.

regards

shekhar

Hello,

Shekhar if your cell is showing and N/A then I am assuming that you already have a formula in that cell and there is no data for it to get about your salesman or the formula is looking for a Text value where there is a numeric value or visa versa.

I use =if(cell="","",function)

meaning if the original formula is looking at D5 for data and it is not there, then you are just adding infront of your formula this to say if d5 = nothing "", then do nothing "", else do this formula.

one more example lets say the formula in cell A5 is =3/D5 but there is no data in D5, A5 then returns a #DIV/0! error

you still want the formula 3/D5 but not the error you would say in A5 =if(D5="","",3/D5)

so if there is nothing in D5 do nothing else 3/D5

Cheers Don

Shekhar if your cell is showing and N/A then I am assuming that you already have a formula in that cell and there is no data for it to get about your salesman or the formula is looking for a Text value where there is a numeric value or visa versa.

I use =if(cell="","",function)

meaning if the original formula is looking at D5 for data and it is not there, then you are just adding infront of your formula this to say if d5 = nothing "", then do nothing "", else do this formula.

one more example lets say the formula in cell A5 is =3/D5 but there is no data in D5, A5 then returns a #DIV/0! error

you still want the formula 3/D5 but not the error you would say in A5 =if(D5="","",3/D5)

so if there is nothing in D5 do nothing else 3/D5

Cheers Don

Sample.zip

Hi

I have attached a file for your reference. Yes i do have a formula that references the data below.

In the attached file if i select the name as Shekhar, i should not see any data in the 4th row as they are all #N/A's.

I want to see only data till row 3. But for Don, i want to see row 4 as this has data.

Also i dont want to see anything underneath row 3, if i select Shekhar

regards

shekhar

Hi

I have attached a file for your reference. Yes i do have a formula that references the data below.

In the attached file if i select the name as Shekhar, i should not see any data in the 4th row as they are all #N/A's.

I want to see only data till row 3. But for Don, i want to see row 4 as this has data.

Also i dont want to see anything underneath row 3, if i select Shekhar

regards

shekhar

Hi

Place this in B4 and do the same with the others.

=IF(ISNA(VLOOKUP(C4&A15,P14:Q20,2,0)),"",VLOOKUP(C4&A15,P14:Q20,2,0))

HTH

John

Place this in B4 and do the same with the others.

=IF(ISNA(VLOOKUP(C4&A15,P14:Q20,2,0)),"",VLOOKUP(C4&A15,P14:Q20,2,0))

HTH

John

OOPS

Place this in**B15** and do the same with the others.

**NOT B4**

John

Place this in

John

Shekhar,

Thank you for uploading the sample file.

Like I guessed you already had a formula in the cell causing the N/A# value so you needed to add an IF statement.

John is correct in his formula, =if(ISNA( again what it is doing is asking if there is nothing there or an N/A then do nothing, else do the other.

In excel you will find there are many ways to make something happen

Have fun and post again soon.

Don

Thank you for uploading the sample file.

Like I guessed you already had a formula in the cell causing the N/A# value so you needed to add an IF statement.

John is correct in his formula, =if(ISNA( again what it is doing is asking if there is nothing there or an N/A then do nothing, else do the other.

In excel you will find there are many ways to make something happen

Have fun and post again soon.

Don

Hi

Thanks for all your replies.

Yes IF(ISNA does work. But i also have some data(not formula driven) like the month names and other data like actuals.

So what i am looking for is, if against 4 there is #N/A all the data beneath that should appear as blank or simply white.

Sorry if i was not clear with my earlier question.

regards

shekhar

Thanks for all your replies.

Yes IF(ISNA does work. But i also have some data(not formula driven) like the month names and other data like actuals.

4 |
#N/A |
||

Jan | Feb | Mar | |

Actuals | #N/A | #N/A | #N/A |

So what i am looking for is, if against 4 there is #N/A all the data beneath that should appear as blank or simply white.

Sorry if i was not clear with my earlier question.

regards

shekhar

**First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data .****Now, select the cells in one column from the top of your list to the bottom.****Make sure that all the blank cells in this selected range are the rows you want to delete.****Press the****F5**key on your keyboard (or select**Edit, Goto**).**Click the****Special**button.**Click the****Blanks**option and click**OK**. This will select all blank cells in the range you had previously selected.**Now choose****Edit, Delete**, select the**Entire Row**option and click**OK**.

If you work with large lists of data in Excel,

I would have to assume the data in the table is coming from a formula as well, you can just wrap that formula in the ISNA function like you did A1. Or, in 2007+, IfError:

=IfError(myformula,"")

which will return a blank if myformula results in an error.

=IfError(myformula,"")

which will return a blank if myformula results in an error.

I agree with Wally eye, if you have an error or the #N/A in the cell then it is most probably a formula driven error, check again and either use the basic =IF( statement or one of the other +if(INSA or =IFerror if you are still having issues please upload the actual file you are working with and let one of us help.

Don

Don

Excel has several functions that can be used to truncate or remove such unwanted characters. Which function you use depends upon where the unwanted characters are located in the cell.

Check this link

http://spreadsheets.about.com/od/Text-Functions/ss/2011-01-31-excel-2010-truncate-text-strings-left-function.htm

Hi Don

Here is the file that i sent earlier. Sample.zip

If i select Shekhar in name , in Cell B15 i have #N/A, i don't want to see any rows from B15, they should be blank.

B15 comes out of a formula.

If i select Don in name i should see all the names with data.

Hope this is clear.

regards

shekhar

Here is the file that i sent earlier. Sample.zip

If i select Shekhar in name , in Cell B15 i have #N/A, i don't want to see any rows from B15, they should be blank.

B15 comes out of a formula.

If i select Don in name i should see all the names with data.

Hope this is clear.

regards

shekhar