Ok, I've searched high and low for a solution and I can't find one that works. I've tried ISNA, ISERROR and several others to no avail.
I have an IF VLOOKUP formula and I need to hide the NA errors. Unfortunately some of my co-workers aren't bright enough to understand what the error means so I need it to display "No Data Returned" instead. Here's the formula I'm currently using
=IF(0<$E5<11,VLOOKUP($A5,table_1,2),VLOOKUP($A5,table_2,2))
Any ideas?!
You can use Conditional Formatting
http://excel-it.com/excel_conditional_formatting.htm
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
This worked for me, though it's brute force. Try it and let us know:
=IF(ISNA(IF(0<$E5<11,VLOOKUP($A5,table_1,2),VLOOKUP($A5,table_2,2))),"No data returned",IF(0<$E5<11,VLOOKUP($A5,table_1,2),VLOOKUP($A5,table_2,2)))
Something like this perhaps:
Please Login or Register to view this content.
Your initial test of 0<$E5<11 will always evaluate to FALSE. It needs to be an AND()
If you do the formula evaluation you will see (assumes E5=5)
0<5<11
TRUE<11
FALSE
Is there any way to hide the N/A# and still use all 3 conditional formats?
Or is there a way to show the cell contents only when we have put in data in the cell that requires it? eg. I am doing a VLookup, and have copied the formula down the entire column. But it now shows N/A# in the cells that have not calculated a formula yet.
How do I hide this as i am using conditional formatting for colours too?
Which version of Excel are you using? It helps if you put this in your User Profile
How do you want to hide the #N/A? If you use the method I suggested above, you use the form:Originally Posted by cell-dweller
=IF(ISNA(<your formula>),"message for NA case",<your formula>)
where <your formula> is the same both times.
If you do this, it's completely separate from conditional formatting. (I'm still not sure why royUK answered this question by pointing to conditional formatting.)
Because an easy way to hide non-significant errors is to use CF to make the font the same color as the background.I'm still not sure why royUK answered this question by pointing to conditional formatting
Entia non sunt multiplicanda sine necessitate
Good point. I have used a white font to hide content, though soon realized that as soon as the user highlighted a range including the "hidden" data, they could see it. So it depends on how hidden you want it to be.
It gives users a pretty good hint that things are under control. Maybe. Perhaps. Possibly.
It is actually covered in depth on the page linked to. The major downsides to hiding errors with formulas is that you might not be aware of a problem with the data, that's why Excel has the error warnings,and these formulas increase calculation time,so too many can slow a workbook right downI'm still not sure why royUK answered this question by pointing to conditional formatting
Couldn't quite "get" how to use the conditional formatting but this worked perfectly. Thanks you!
MySillyBear,
The formula provided by 6String carries over your 0<$E5<11 flaw. This will always evaluate to FALSE, forcing the table_2 to be used no matter the value in E5.
From one of my previous posts
This would be the correct formula:Your initial test of 0<$E5<11 will always evaluate to FALSE. It needs to be an AND()
If you do the formula evaluation you will see (assumes E5=5)
0<5<11
TRUE<11
FALSE
Please Login or Register to view this content.
Your right, thank you! I didn't notice at first because I wasn't using FALSE in my vlookup and it was finding similar strings in table_2 so the formula fields were being populated giving the impression (at first glance) that it was working.MySillyBear,
The formula provided by 6String carries over your 0<$E5<11 flaw. This will always evaluate to FALSE, forcing the table_2 to be used no matter the value in E5.
From one of my previous posts
This would be the correct formula:
Please Login or Register to view this content.
I have a similar issue,
I am using an if vlookup and i am trying to hide the #N/A error and nothing would work.
This is the code
=IF(D17=0,VLOOKUP(A17,'MT4 Paste'!A3:B30,2,FALSE),0)
I am getting the info from another worksheet and i really have to get rid of the #N/A .
Couldnt make it with conditional formating.
Welcome to the forum, tsioumiou.
Please take a few minutes to read the forum rules, and then start your own thread.
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks