Vlookup returns "0"

I am using the following formula
=VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE 
and it is returning a zero if there is no data found in 
that cell.  If there is no data found I would like it to 
display nothing.
How can I do this?
0
anonymous (74717)
9/16/2004 1:33:47 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
541 Views

Similar Articles

[PageSpeed] 26

Carolyn,

There are a couple ways so do this.  One is to test it, which makes for
doing the VLOOKUP function twice.

1)
=if(VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)=0, "" ,
VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE))

This gets a little messy.  Better yet, and simpler is to hide the original
cell (column, whatever), and refer to it in another column.  If your
original formula is in B2, put this in another cell:

=if( B2 = 0,  "",  B2).

Now hide column B, and let this one display.

2) Simply format the original formula (Format - Cells - Number - Custom)
with

General; General;;General

The third term is nothing, so when it yields a 0, you get nothing.

3)

Use Tools - Options - View - deselect "zero values."  This will apply to all
cells in the worksheet with 0.
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Carolyn" <anonymous@discussions.microsoft.com> wrote in message
news:05fa01c49b8d$3610a110$a301280a@phx.gbl...
> I am using the following formula
> =VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE
> and it is returning a zero if there is no data found in
> that cell.  If there is no data found I would like it to
> display nothing.
> How can I do this?


0
nothanks4548 (968)
9/16/2004 2:06:16 AM
Carolyn-


Here are a couple of approaches:

1.  Wrap your VLOOKUP in an IF() function:
	IF(VLOOKUP(A1,E1:H1,2,FALSE)=0,"",VLOOKUP(A1,E1:H1,2,FALSE))

2.  Use conditional formatting:
	Format -> Conditional Formatting...
	Cell value is | equal to | 0
	Click on 'Format' and set the text color to white (or
	whatever the fill color for the cell is.)

I use both of these methods all the time - VLOOKUP is one of my most 
frequently used functions.



Mike Argy
Custom Office solutions and
Windows/UNIX applications



Carolyn wrote:
> I am using the following formula
> =VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE 
> and it is returning a zero if there is no data found in 
> that cell.  If there is no data found I would like it to 
> display nothing.
> How can I do this?
0
Mike
9/16/2004 2:24:34 AM
I'd use this to check for an empty cell.

=if(VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)="","",
    VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE))

Then if a 0 were returned, you know that it wasn't an empty cell--it was really
0.



Carolyn wrote:
> 
> I am using the following formula
> =VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE
> and it is returning a zero if there is no data found in
> that cell.  If there is no data found I would like it to
> display nothing.
> How can I do this?

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/16/2004 9:37:33 PM
Reply:

Similar Artilces:

How to access table buffer in GP 10.0?
Hi, I am trying to access table buffer of table SOP_HDR_WORK attached to SOP_Entry form. But if I use the same piece of code I was using for GP 9.0 - if 'SOP Number' of table SOP_HDR_WORK <> "" then {some code} end if; I am not getting back any result. Is there any change the way we can access the table buffers in GP 10.0? Thanks. ...

Recurring bills in GP 8.0
I need someone to advise me on how to enter and post recurring bills as in utilities. I do not want to have to enter a bill each month and then go back and do a manual payment is there an easier way? Thank you, Shelly ...

GP 9.0 user logins passwords
We have this same problem, but only for a few users. A user will be told that their GP password is not valid for FRx, so the SQL admin will reset the FRx password. This allows the user to log into FRx, but on the next log in for GP, GP forces the user to change their password. Once the GP password is changed (so the user can get into GP), the FRx password again is locked and needs to be reset by the SQL admin. It is a vicious circle, so any help on this will be greatly appreciated. Again, it is strange because half of the users have no problem at all. TIA ryanb. ==== Newsgroups:...

Outlook and Proxy Server 2.0
I am trying to connect an Outlook client to a POP3 Server through a MS Proxy Server 2.0 and can not get it to work. What are the additional steps I need to take? ...

CRM3.0 to GP9 Integration
Anyone know if they have released the CRM3.0 to GP9.0 integration. I heard they released it, then pulled it. ...

VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...

RSS 2.0 Extensibility
I'm wondering if any subscribers would care to offer brief comments -- pro *and* con -- regarding the fact that RSS 2.0 is now extensible, notably by using namespaces. <%= Clinton Gallagher NET csgallagher@REMOVETHISTEXTmetromilwaukee.com URL http://www.metromilwaukee.com/clintongallagher/ ...

Access is Denied in CRM 3.0 after upgrading from 1.2
After an upgrade of Microsoft CRM 1.2 to 3.0 without any errors, I can not log into the Microsoft CRM web page. I get the following error "you do not have enough privileges to access the microsoft crm object or perform the requested operation" I have searched everywhere for a solution for this but i am now at a loss. During the install i recieved two warnings 1. verify domain user account spn for the microsoft crm asp .net application pool account 2. setup was unable to verify that the "trust for delegation" property is enabled for the computer object. If I create a new...

Upgrade from 7.5 to 8.0 #2
I am upgrading from Great Plains 7.5 to 8.0. I received the following: Ugrade fail. HR_Inquiry HR_Tests Payroll Benefit Master UPR00600 Payroll Benefit Setup UPR40800 Payroll Master - Archvie UPR_MSTR Payroll Work Benefit UPR10205 How can I fixed these errors and continue my upgrade -- tk ...

#DIV/0! Error
Hi I get an error (#DIV/0!) when I divide one cell by the other and there are no entries in the cells. How can i get the folrmula to surpress the error (show a blank cell) when there are no entries in the cells that the formula refers to? Could you please provide an example Thanks John =IF(B1=0,"",A1/B1) -- HTH Bob Phillips "Joohn Calder" <JoohnCalder@discussions.microsoft.com> wrote in message news:4A736933-8B5E-4D25-A67F-AADE56803802@microsoft.com... > Hi > > I get an error (#DIV/0!) when I divide one cell by the other and there are > no ent...

CRM 4.0 Custom Contact View Problems
In our Microsoft Dynamics CRM 4.0 system we have created a custom view for the "Contacts" entity. One of the users of our system who has user privileges at the organizational level can access contact records when they search for them in the system; however, whenever they select the view, no records display. This appears to be a problem only for the user in question because other users in the system, myself included, can view records using the view. All permissions/user roles for the user in question appear to be fine, which is supported by the fact that they can access records when ...

Access 2.0 to Access XP?
Hello, My customer has an Access 2.0 database which they would like to upgrade to Access XP. Can I just use decompile/recompile/compact? Is it necessary to take any other steps? Thanks in advanc. Tracy No you can't just do that. Converting from A2.0 to any later version may involve a major exercise. Beyond writing a book, which you can buy, tell us what specifically is your problem? If you don't know A2.0, ie the "from" AND the "to", then you are not qualified to convert it, is my impression. Having said that, conversions are not too difficult if you jus...

Same query: Subform 0, combobox 1, wins by KO
Hello :) I have a table I want to modify but after it's filtered by an ID number. I have created a query that works great and added a listbox that displays the IDs and when I select the ID it requeries the query. Because I want the users to be happy, I have created a form (where the listbox is), and a subfom object that has created a subform where my query is ran. I then have a nice form with a datasheet display underneath to tick the checkboxes I need. BUT, the problem is that the subform displays the first ID correctly - but not the other ones. I have tried to link the WHERE to ...

Help with VC6.0 Basic Concept!
Hello everyone, I am new to VC,and I don't quit understand the defferences in project types in vc6.0, especially MFC AppWizard, Win32 Application, Win32 Console Application, Win32 Dynamic-Link Libray, MFC AppWizard and Win32 Static Libary,Thanks very much! Lazytiger wrote: > Hello everyone, > I am new to VC,and I don't quit understand the defferences in project > types in vc6.0, especially MFC AppWizard, Win32 Application, Win32 Console > Application, Win32 Dynamic-Link Libray, MFC AppWizard and Win32 Static > Libary,Thanks very much! A console applic...

Task Create - Status
I'm trying to make a simple task, when a user owner is assigned a task in CRM, it receives an email letting it know it has one. I have the following below, but it is not generating one when i assign one manually in the CRM. What to do? I've tried both the following: When task is created E-Mail to:[owner];Subject test As well as: When task is created if Task activity status = Open then E-Mail to:[owner];Subject test I've checked the mailserver many times over, and I can send emails manually from the CRM so you can rule that out Tested out ok on our end. Some ideas:...

CRM 3.0 to CRM 4.0
I would like to upgrade my CRM 3.0 to CRM 4.0, could any one give me advantages and new features in CRM 4.0 in order to convence my company to upgrade my CRM3.0 to CRM4.0. Currently we are using CRM 3.0 for Service Module only. Regards, SAB The workflow capabilities in itself are enough reason. All workflow is now managed from the CRM UI which includes triggering actions via update object "field change". In 3.0 you could only fire a workflow based upon an "On Create" event in the system i.e. New Contact, Invoice etc... In additiion, the 4.0 platform has given end ...

WSS 3.0 Move DB to Different SQL Server
Can some one provide me with the steps to complete this? Thank you, I have 4 DB SharePoint_AdminContent SharePoint_Config WSS_Content_1 WSS_Search_WEBSERVER I know to stop all the services and take db offline and then copy the file to the new server. Then attach them in the new Sql Server. But how do I tell WSS were all the db are now. ------=_NextPart_0001_AA8CBDCD Content-Type: text/plain Content-Transfer-Encoding: 7bit Hello Maida, You can use a SQL alias to redirect all SQL requests to the new SQL Server. Use Cliconfg on client machine to cofigure the alias....

compile error from vc6.0 to vc7.0
I developed a project in VC6.0. After installing VC7.0, I follow the suggestion to convert the previous project into VC7.0 project. Then, I compile the solution and got one compile error: can not open "fstream.h". It seems that VC7.0 has no fstream.h header file, is there any way to encounter this?Any suggestions are greatly appreciated. -- Yan ZHANG http://www.nict.com.sg/zhang/ >I developed a project in VC6.0. After installing VC7.0, I follow the >suggestion to convert the previous project into VC7.0 project. Then, I >compile the solution and got one compile error: &g...

RMS 2.0 Credit Cart Processing
Hello, I am looking to set up a new register with RMS 2.0 at a new location. I am currently using RMS 2.0 with Citi as my credit card processor but am looking for more processing options that can be integrated with RMS 2.0 without having to install additional software with decent rates. Seems like Citi has the best Debit/Atm fees but would like to know if there are a list of other processors out there that can be integrated with RMS 2.0. -- ~grace I don't really consider it installing new software, but it's sort of what you are doing when you go with Mercury, whom...

WSS 3.0 Event 3760
Brand new installation of WSS 3.0, WFE and SQL 2008 BE, everything is working correctly with the exception of the 3760 error every 5 minutes. The account trying to access the database is the server farm account but it is a different application pool it is trying to access. I have the server farm account unique and only being used by the central administration application pool. I created a 2nd web application with a different IIS site and a unique domain user account to run as the service. I've looked everywhere on my SQL server and SharePoint and cannot understand why the serv...

File Size Limit in CRM 3.0
Hey, A client of mine wants to increase their File Upload size. Right now the limit is 5MB (as it was in 1.2). Read an old post that said I could change the registry to increase the file size limit. However, this key does not seem to exist in the 3.0 version (or so says the client's network guy). Does anyone know how to change the file upload size limit? I know, I know, not a good idea, could drag down server performance, etc., but this is really what the client wants... -- --Dodd The key does exist. From the SDK: maxuploadfilesize Currently, the size limit for files uploaded to ...

Task groups in CRM 3.0
Hi, Is it possible to create Task groups in CRM 3.0, where in users will create tasks in that group which can be shared with the users of that group. We should be able to send the reminders for those tasks. My exact requirement is below: My team use tasks to track progress of reviews we do on brokers and coverholders but this is still being done outside BRM and using a shared task folder e.g. "dubs" in normal Outlook. This means that the contents are visible to all my team. Is it possible to set up task groups in BRM in the same format and could we have a diary system so we...

CRM 3.0 Training
Where's the 3.0 training on the PartnerSource site. All that I can see is the 1.2. Can you advise me as to where to find the right resources to get trained, and certified? Thanks, Richard Charlton Richard, I spent 2 hours looking last night, there is nothing up there yet. The only webcast I could find on v3.0 was for March 14th titled: Web Seminar: Notes from the Field: Microsoft Dynamics GP & Microsoft Dynamics CRM Technical Aspects of the New Integration Seems a shame to have such a huge product with no partner training. Mike "Richard" <Richard@discussions.mi...

Installation and Deployment Certification 4.0
Hello, I am studying to certification for CRM Installation and Deployment of Microsoft CRM 4.0. I would like to know tips to achieve my goals. Any help would be appreciated. Thanks in advance. MATTBIONDI read the Implementation Guide - specifically the Planning and the Installing documents. Dave Ireland "Juliano" <mattgame@uol.com.br> wrote in message news:9F6B0D99-BAE8-4BD1-ADBC-1CADEBF1A731@microsoft.com... > Hello, > > I am studying to certification for CRM Installation and Deployment of > Microsoft CRM 4.0. I would like to know tips to achieve my...

Is integration manager for GP 10.0 availabe?
Does anyone know if integration manager for GP 10.0 is available? If yes, where can I download it from? It's on the 2nd GP 10.0 install disk. "Nik" wrote: > Does anyone know if integration manager for GP 10.0 is available? If yes, > where can I download it from? ...