Using VLOOKUP and text

I am using a Vlookup function to find dollar values, dates, and names of 
vendors made within a department.  
The dollar values are appearing correctly, but the dates appears as 
"01/00/00" when the field is empty and all the text strings appear as "0" 
even though there is a name in the source sheet.
I have changed the formatting of each cell to be correct to the appropriate 
format (accountancy, date, text) and even tried 'general' to fix this text 
problem to no avail.
HELP!!
0
TaraC (1)
12/17/2005 3:17:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
510 Views

Similar Articles

[PageSpeed] 54

If A1 is empty, and you put this in B1:  =a1
You'll see it returns a 0.

Same thing happens with your =vlookup()--but 0 formatted as a date is 
January 0th, 1900 (or 01/00/00 in your format).

So you can modify the =vlookup() to check to see if the "sending" cell is empty:

=if(vlookup(a1,sheet2!a:b,2,false)="","",vlookup(a1,sheet2!a:b,2,false))



Tara C. wrote:
> 
> I am using a Vlookup function to find dollar values, dates, and names of
> vendors made within a department.
> The dollar values are appearing correctly, but the dates appears as
> "01/00/00" when the field is empty and all the text strings appear as "0"
> even though there is a name in the source sheet.
> I have changed the formatting of each cell to be correct to the appropriate
> format (accountancy, date, text) and even tried 'general' to fix this text
> problem to no avail.
> HELP!!

-- 

Dave Peterson
0
petersod (12005)
12/17/2005 4:40:40 PM
Reply:

Similar Artilces:

Cannot download attachments using OWA
Exchange Server 2003 w/SP1 on Windows Server 2003 I have one user who is cannot save or view attachments via OWA. She experiences this problem using her Windows XP SP2 laptop at work or at home as well as on her machine at home. The attachment works fine when she opens the email via Outlook 2003. The error she gets when she tries to save the attachment is: "Internet Explorer cannot download Dept Reports.doc?attach=1 from mail.mydomain.com. Internet Explorer was not able to open this Internet site. The requested site is either unavailable or cannot be found. Please try again later....

Setup Outlook Rule to use Import --will not work when sending email from Adobe
Hello, Can anyone please help me figure out why an Import box will not automatically pop up whenever we 'cc' our document management program straight from Adobe? We have set up a Rule in Outlook to automatically open the Import so we can index our document into the right category by using the cc. However.... Some of the user's computers will not work with Adobe in having this box automatically open like it does if they were sending from Outlook. I have tried in vain to find where the changed setting is but to no avail. Why will Adobe not work with Outlook (or vice versa) in...

Should I Upgrade from 2000 to 2004 so I can use Pocket Money?
I currently use MS Money 2000 standard with no problems. I also have an iPaq 1945 that I'd like to use Pocket Money with, but can't since I'm using 2000 on the desktop. I have a paid copy of 2004 standard (came with new PC). Is it worth upgrading (converting the .mny file, having my bank 'upgrade' my profile to use online banking, etc.) to 2004 just so I can use Pocket Money and sync with my iPaq, which has Windows Mobile 2003? Any opinions? Is Pocket Money + desktop Money worth the hassle? Or, is Quicken + Pocket Quicken a better combination? I'd really l...

Adding a highlight color to commented text in Word 2007
When I receive documents from other people, they have comments attached to large sections of text. Frequently, I want to be able to use the highlight feature to identify specific passages for other reference. Unfortunately, once text has been "commented" the highlight color remains invisible unless/until the comment is deleted. This is really, REALLY annoying. I've googled with as many different word combinations I can think of. But the results never address my specific inquiry. [I already know how to add a comment and I already know how to highlight, thanks. 8) ] ...

turning a report into an html page using 2007
I'm using access 2007, does anyone know how to turn a report into an html page where all my subreports and clickable buttons would be active ...

VLOOKUP error message
After verifying that the formula is correct, matching cell formats, and verifying the cell data is an exact match, I continue to get a "N/A" response in two cells of my workbook. I've even gone so far as to back out of Excel, re-launch the program, and re-open my source file and VLOOKUP file. Out of over 1300 matched items, these two refuse to cooperate! Can anyone tell me why? I'm frazzled beyond understanding at this point... (sigh) You may look for numeric items which indeed look like numbers and are formatted as such, but still are text. Format an empty cells...

Number or Text (letter)
Hi everyone. I am stuck with the delemma here (below): column1 Column2 Formula 123, 231aa, 234ff TRUE ? 123aa, 234bb, 345cc FALSE ? 123aa, 234bb, 345cc, 456dd, 567ee FALSE ? 987ff, 876, 765, 654ee, 432pp TRUE ? Basically, how do I get the results in Column2? In Column1 i have alpha-numeric data and Column2 should have a formula to tell me whether it is TRUE (if the entire data in the c...

Use Outlook With SQL Server 7!
I have recently installed MS-Outloook 2000 in my Win 2000 Professional system. I want Outlook to be the default mail client so that I can send mails from SQL Server 7 but when I start Outlook for the 1st time after installing it, I am facing problems. As soon as I start Outlook, the 1st step gives 2 options to select from -Microsoft Exchange Server & Internet Email.Since I want to use Outlook solely for sending mails from SQL Server, I select the former. The next step prompts me to enter the MS-Exchange Server & the Mailbox. This is where I am getting stuck. What do I enter as the MS-...

Node(s) for Loding Credit Card info using eConnect?
We use the "Payables Transaction Entry" & "Payables Transaction Entry Distribution" screens to load Credit Card data. We can get the data in a spreadsheet and wish to load them directly using eConnect. Can someone point me to the node or nodes for loading this Credit Card data into GP 9.0 using eConnect? DavidM: If you are looking to populate credit card type of information on a payment right on the invoice - you would look at these fields in the taPMTransactionInsert procedure: @I_vCRCRDAMT - the credit card amount @I_vCCAMPYNM - the credit card payment number...

Copy and Paste a formula as Values in a fixed Range as a loop statement using VBA
I have 3 work sheets i.e. a "Summary" sheet, a "Phase1" and a "Phase2" sheet The Phase1 and Phase2 sheets are a result of certain parameters that are entered separately for each of the Phases that produces the results in them. All the 3 sheets have fixed ranges and they are A2:AC151, the dates are within the range C3:AC150 for each Phase. All the 3 sheets have the same structure something like this A B C D E SAP NO CostCenter Dec-03 Jan-04 Feb-04 422100 DTF-Office 60,000 186,000 174,000 422140 DTF-Office 40,000 124,000 116,000...

Upgrading OutlookXP to Outlook 2003 Using GPO
I'm trying to get every client on my network on the same version of Outlook and I'd like to know the most efficient method for upgrading to 2003. I have a logon script setup and although it works, I don't think it is the most effective way. Tips? ...

I want to change the series shown in a chart by using a dropdown b
I have 4 series on a graph and I would like to be able to change which series is displayed (i.e. 1 of 4, or 2 of 4, 3 of 4, or all 4). I would like to do it by drop down menu if possible. Is this possible? Send me a sample file and I will show how. Remove TRUENORTH -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jamie" <Jamie@discussions.microsoft.com> wrote in message news:04DDBC1C-0558-4A89-83B7-517F7F6F404C@microsoft.com... >I have 4 series on a graph and I would like to be able to change which >series > is displayed (i.e. 1 of 4, or ...

searching in a text file with a specified string and deleting the string(URGENT)
Hi I have 2 text file which is having its content as follows... ---------------------text file 1----------------------------- atlanta moris hangaroo -------------------- -----------upto random number of lines ------------------end of text file 1------------------------ ---------------------text file 2----------------------------- www.abc.com www.sea.com www.random.com -------------------- -----------upto random number of lines ------------------end of text file 2------------------------ Now my requirement is that -> User will input a string value which may or may not exist in ...

Gen Question: when to use a Child Workflow?
HI, I am new to workflows, about 10 under my belt. I am setting up a few new ones; 3 different things I want to happen when an Opportunity is Won. Should I set these up as Parent/chilren, or just set up 3 different Workflows based on the Opportunity status changing? What is the advantage to using Parent/Child workflows and can you give me an example of when I should do that? Thanks! -- Laura Barber <If I have helped you in some way, please click ''Yes'' on the post. Thanks!> Just wanted to add details: When opportunity is won I want: 1. New case added to syste...

Re-direct without Exchange using Outlook XP?
Is there a way to have Outlook XP redirect email without owning Exchange? I want to buy a Blackberry, and I want to keep my existing email account. thanks in advancee. Please respond here to the group. My email address is not listed so as to escape at least some spam. :) What do you mean "re-direct?" You can use Rules Wizard to forward mail if that is what you mean. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Having searched the archives and finding no answer, News <blarg@blarg.com> asked: | Is there a way to have Outlo...

Link error/Warning using macro in files converted to Excel 2007 (.
Problem: Receiving the following message during macro run "This workbook contains one or more links that can not be updated" with the continue or edit buttons. This only occurs for some files, which have no distinguishing characteristic differences to the non warning message files Background: Excel files converted, thru manual converter (open file, check compatibility, and convert) from 2003 to 2007 Macros only conversion was find and replace .xls with .xlsb Normal manual refresh of links works fine All files have exactly the same settings 1. R...

Controlling Shipments/Deliveries using CRM 3.0
All, I am interested if anybody has managed to use CRM to track shipments of say, hardware, that relates to either a specific account or a specific sub-account. Is this possible? I am trying to create the relationship between, say, a new entity called "Shipment" and an Account. I will need the details of this shipment to come from an order placed under an Account. Hopefully, this is clear. Dave Dave, Are you trying to set up actually shipping information (i.e. UPS tracking #'s)? I'd be more interested in how you would do that. I was waiting for ZFirm to upda...

WMI to use or not to use
(1)What is the downside of using WMI? (2)Can I distribute the MS WMICORE or just suggest a user download WMI for Win not having WMI like Win98 etc? I would like to use some WMI calls in my VB6 App. Specifically to get Serial Port information. (3) If not WMI, then what APIs will give me Serial Port info? (4)How do I check for WMI other that error trapping in a wrapper? "Bee" <Bee@discussions.microsoft.com> wrote in message news:37F39B6C-F47C-42B5-A79E-4654D2520502@microsoft.com... | (1)What is the downside of using WMI? | (2)Can I distribute the MS WMICORE or ...

ADRSCODE field using econnect
In the taCreateEmployee class there is an ADRSCODE field which seems to be required to import addresses using econnect. Can anyone explain to me how the ADRSCODE works and what codes I should be using -- AHIhelp AHIhelp: In order to change the address (that reside in the UPR00102 table) you need to call the taCreateEmployeeAddress node this will allow you to change the address values. This node is called internally in the taCreateEmployee node when you first create the Employee, but in order to change anything you need to call the taCreateEmployeeAddress. If you just call the taCre...

Evaluate VLOOKUP in cells in column
Using Excel2003, I have copyied and modified the following code which essentially should fill the value of VLOOKUP(E2,AgeGroup,2) in cells F2:F64500 but I just Cannot seem to get it right. Would really appreciate assistance. Have no VBA knowledge. Sub AgeGroup() Dim i As Long Dim j As Long Application.Goto Reference:="R2C6" For j = 6 To 6 Step 1 For i = 2 To 64501 Step 1 Cells(i, j).Resize(1).FormulaR1C1 = _ Evaluate("VLOOKUP(RC[-1],AgeGroup,2)") Next i Next j End Sub Thank you -- Robert Try along the lines of Range(Cells(1, 1),Cel...

Use Outlook 2000, but Outlook Express Error
I never use Outlook Express. I use only Outlook 2000, but I am getting an Outlook Express error: "The Address Book failed to load. Outlook Express is incorrectly configured, please re-install." In spite of that, my address book in Outlook 2000 opens normally after I close the error message. How do I get rid of the error message? I am using XP HE, SP3 with all available updates. This has been happening since I did a restore recently from an Acronis image. Everything else has been working fine since restoring from the image. Bill Did you set Microsoft Outlook as your d...

How do i set one of the static text controls in the dialog to be bold?
Hi, I have an MFC dialog that has two static controls on it with text. I need to set one of the strings to be bold, basically have one control with bold text and second one with plain. I can't seem to figure out how to do that. Any help on this is appreciated. Thanks in advance. > I have an MFC dialog that has two static controls on it with text. I > need to set one of the strings to be bold, basically have one control > with bold text and second one with plain. I can't seem to figure out > how to do that. > > Any help on this is appreciated. Change the f...

Pasted text splits into separate text boxes.
Recently upgraded my Publisher program. When using a text box from a template, and I try to paste text, it breaks the text, line by line into separate boxes within the text box. I never had this problem with my previous Publisher program. What's up with this? A JackBird <A JackBird@discussions.microsoft.com> was very recently heard to utter: > Recently upgraded my Publisher program. When using a text box from a > template, and I try to paste text, it breaks the text, line by line > into separate boxes within the text box. I never had this problem > with my previo...

Should DCOM be running on WinXPpro\sp2 using RMS ?
We are using RMS & SQL2K in a domain environment. Should DCOM be running on the workstations to aid in performance of RMS and SQL\reporting, or doesnt matter? Thanks ...

TabCtrl's text in BOLD
Hello, I have a customized TabCtrl. My requirement is that that the text displayed in each tab page's caption should appear in BOLD. Selecting the BOLD font into the Device Context gives me the desired result but using this approach a considerably long text gets truncated. I then tried to set the font explicitly for the control using SetFont() method. In this case, Windows internally calculates the appropriate width required for the caption with Bold font and though I achieve what I want I have started to encounter some painting issues such as; the controls placed within the tab control ...