formatting cell in vlookup

HELP

I have an excel spreadsheet that is e:mailed to me by my home office.  I use the vlookup feature to find the value in cell b from the information in a

Proble

At times when the answer is N/A and I go to the e:mailed spreadsheet to see if that number is really not there, it is in the e;mailed spreadsheet.  If I type over that number in the cell on the e;mailed spreadsheet, then the formula will work in the target spreadsheet, sometimes

This e;mailed spreadsheet is at a shared location on a servier, and the items I fix in the e;mailed spreadsheet on my desk top, are not available in any of the other desktops when using the same corrected e;mailed spreadsheet.  They have the same problem with N/A but with non-corrected items, sometimes

The e;mailed spreadsheet is an entered information spreadsheet, not imported, exported or copied from any other sheet.  The lady enters the information line-by-line.  The cells are all formatted as General

We have tried copy, paste special, values only.  No help

Any suggestions

Thanks a bunch
0
4/8/2004 11:16:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
212 Views

Similar Articles

[PageSpeed] 26

Hi Kathy!

Have you checked whether the entries are numbers rather than text?

Use =ISTEXT(A1)
Returns TRUE if they are text.

To convert from text to numbers:
Copy an empty cell
Select the range of text numbers
Edit > Paste Special > Add > OK


-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments) 
available free to good homes. 


0
njharker (1646)
4/8/2004 11:28:35 PM
Reply:

Similar Artilces:

get the active color of a cell
I got cells that had there color chaging automatically using a format condition (cond#1:red if B4<10 cond#2:green if C5>100 for example...). Now, I would like to automatically change the color to blue if the active color is red (condition #1 true in my example). I can get the ColorIndex of the cell: -when no condition applied = .Font.ColorIndex -when condition #1 applied = .FormatConditions(1).Font.ColorIndex -when condition #2 applied = .FormatConditions(2).Font.ColorIndex But impossible to retrieve the actual cell color... any idea? Another way to solve my problem would be to be able ...

cell does not auto-stretch when too much text in one cell
I have text of multiple lines in a cell. It seems after it reaching to an amount, it stops auto-stretching so I can see all text in the cell. I have manually drag it down so I can see the complete content. Is there a way to have it auto-stretched to show all text again? Thanks for the help, Use Format/Row/Autofit -- you'll only have to do it once. From then on it should "auto stretch". Bob Umlas "cpliu" <spamfreeliu@yahoo.com> wrote in message news:6be268e8-601e-4ac5-9046-c31e152fbc58@f63g2000hsf.googlegroups.com... >I have text of multiple lines in a cel...

format to see bottom of cell
I want to find out if there is a way to format a cell so I can view the bottom half of the cell because there are more text then the height of the cell. I don't want to increase the height of the cell. Thanks in advance Maybe an indirect way would be to write the text in the cells into their own comment boxes ? Then, hovering the mouse over the cell will display the desired full text in the cell w/o having to increase the row height. You might wish to check out Dave M's page at: http://www.mvps.org/dmcritchie/excel/ccomment.htm Look for: Sub AddComments() The sub will popula...

Increment a number based on four adjoing cells
I am using an xlsx spreadsheet for indexing metadata for file folders. Based on the metadata selected from dropdowns in columns H, I & J, the spreadsheet builds a file number in the format AA-AA-NN-nnnnn (A=alpha; N=predetermined number; nnnnn= sequential number). I'm using the following formulas: Col B =VLOOKUP(H577,FUNCTIONLU,2) Col C =VLOOKUP(I577,CATEGORYLU,2) Col D =VLOOKUP(J577,TYPELU,2) Col E =IF(D576="","",IF(COUNTIF($D$2:$D576,$D576)=1,TEXT(0,"00001")+0,IF(COUNTIF($D$2:$D576,D576)>1,INDEX($E$2:$E576,MATCH($D576,$D$2:$...

VLOOKUP formula displays in cell, will not return data
I had an older version of Excel (97 maybe?) and our office updated to 2007. Now my vlookup will not return data. The vlookup formula appears in the cell or I get the NA#. I've formatted my lookup cells and table to General but still get the same result. After formatting the cell as General, re-enter it (F2/Enter) - if you're still seeing the formula, press Ctrl/~ -- you're looking at the formula layer instead of the value layer. "Chaps" wrote: > I had an older version of Excel (97 maybe?) and our office updated to 2007. > > Now my vloo...

I want to format a number cell without the decimal and without ro.
I need to format the number cell so that it will still show the numbers after the decimal, but not show the decimal point at all. It must also display leading zeros, which is not a problem. I just need a way to strip the decimal from the number. If if needs to stay a number with the same value, I don't think you can do it. If you can change the value Just multiple the number by ten to the number of decimal places. If it can be text set up a new column and use =substitute(a1,".',"") "LAM" wrote: > I need to format the number cell so that it will sti...

CRM 3.0 Tracking Token formatting
Version 3.0 offers significant improvement in the tracking token (no more 32 char guids!). However, is there a way to move the tracking token a bit more to the right of the subject line. The tracking token is appended right next to the subject message and is confusing .. especially to the recipient. Version 1.2 right justified (to a certain extent) the token on CRM promoted emails. Thanks in advance for your assistance, Rob K IT Director Tax Credit Company At this time there is not control to that extent over the tracking token ======================= John O'Donnell Microsoft CRM M...

How can setup the receipt format automatical reprint for a seriali
How can edit the (.xml) receipt format to automatic reprint for a serialize item in the transation? This would be great for rebates with serialized items. ...

Can Excel autosize merged cells yet?
Begging everyone's indulgence with an old Q: I'm using a somewhat dated version of Excel. (Where it says "Tools" on the menu, it means "polished stones".) In the past, Excel couldn't autosize merged cells--thereby preventing, oh, that small segment of Earth's population that is actually literate from writing prose in Excel, without having to eyeball and manually adjust the height of every miserable row thruout your entire workbook containing prose wrapped across merged cells. A quick search of microsoft.public.excel.* shows that many ancestors of Homo fen...

Formating text in colums to copy to other applications
Hi, any help would be greatly received. What I am trying to achieve is to format text in order for me to cu and paste into other applications. The text contains the item description in one column and the price i another column, however when I highlight and copy then paste into th application in work the columns are not formatted correctly and th price column is all over the place. e..g how I want it to look price of item code 1234 (spanner) �63.00 price of item code 23 (hammer) �29.00 price of item code 9 (o/s) �26.13 e.g how it actually looks price of item code 1234 (spanner) �63.00...

date in wrong format
My mo/da/yr format somehow changes to a 5 digit entry - 393xx. I have tied the normal procedure of format cells, date, etc. but that won't fix it. What can I do? Mike this is a little long winded but I found it helpful in resolving many a format problem: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q264372 "Mike" wrote: > My mo/da/yr format somehow changes to a 5 digit entry - 393xx. I have tied > the normal procedure of format cells, date, etc. but that won't fix it. What > can I do? You should also be able to type 1 into a cell, copy it an...

2 Conditional formats and insert comment
1. I want to format the cell by 2 conditions. e.g. in range a1:z1, fill the cell with red colour(Fill colour) only if it is blank and if it contains "Sunday", show it with red colour (Font Colour). HOw can I do this? 2. I want to insert the comment conditionally e.g. if the cell contains "IT" insert the comment "India Today", "TOI" insert comment as "Times of India" etc. How Can I perform this operation? Ajit -- Knowldege is Power > 1. I want to format the cell by 2 conditions. e.g. in range a1:z1, fill the > cell with > re...

Vlookup from different sheets
hi, I have the below data...like this i have huge data in different sheets(which are named date wise) and i can not consolidate them in a single sheet as the lines are more than 65000.The account numbers are repeting many a times in different sheets. In a consolidated sheets i have the unique account numbers where i want the name of the account holder... for this i need to use vlookup formula from alomost 20-22 sheets.is there any formula whereby i can use the vlookup from multiple sheets. Account Name 1245485 Stewart 4654546 Alex 2598545 Dean 1548777 Robert 4541112 ...

Cell Formatting with a macro...
Related to a previous topic. I have a worksheet that uses conditional formatting. However, I need to add a 4th condition. I need to code a macro that checks the content of a cell, and then changes the formating (color) of a range of cells if the condition is met. For example if cell M1 is populated with an 'X', then I want excel to change the color of the cells (pattern) for the range A1:M1. Basically, the macro will check for values in the M column and highlight all the cells in the intersecting row. I used conditional formating to perform other tasks, but since Excel only supp...

Different formatting in a cell with a concatenated formula? #2
Yes, if a1 = blue, and b1 = red, and the formula in cell c1 is =a1&b1, want the result to look like this: bluere -- andy281 ----------------------------------------------------------------------- andy2812's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1596 View this thread: http://www.excelforum.com/showthread.php?threadid=27440 Formulas don't support this character by character formatting. If you can convert the results of the formula to values, you can do it though--but that destroys the formula. andy2812 wrote: > > Yes, if a1 = blue, and...

Writing an expression using fields with different formats
I need to subtract a field sum from another, but they are in different formats. How can I convert one of themor get one to recognize the other? For example: Total Shift Time (hh:nn:ss) Wrap Up Time (standard) Need to do the following expression: [Total Shift Time]-[Wrap Up Time] The problem is I'm pulling the data from two different sources, one provdies it in hh:nn:ss format, and the other provides the data in standard number format. Format does not matter. What matters is the datatype of the field it is stored in. Are they the same or compatible? A datetime fields can be subtrac...

isna vlookup
Hi I'm using this vlookup to get values from a sheet named "sub": VLOOKUP(R1,sub!$J$1:$K$18,2,FALSE) and t works. But when "R1" is not in the lookup table sub!$J$1:$K$18 I get an #N/A as a result and I changed the vlookup formula to: =IF(ISNA(VLOOKUP(R1,sub!$J$1:$K$18,2,FALSE),"",VLOOKUP (R1,sub!$J$1:$K$18,2,FALSE)) to avoid #N/A but now I receive an error message for the formula. Is there anybody who knows how to fix this problem? Tim wrote: > Hi > > I'm using this vlookup to get values from a sheet > named "sub": VLOOKU...

I'm I the only one pissed off at BofA for changing the format they use for reporting transactions?
Instead of putting the location where the transaction took place, the transaction entry now starts with: BANK CARD PURCHASE 0980980923482348923 WTF is that? Now I'm forced to manually change the payee so that the transactions auto-classify. Am I the only one noticing this? If not, please send an email to them about it through their site. Maybe if enough people complain they'll fix this problem. Its a long shot, but worth a try. njohn said the following: > Instead of putting the location where the transaction took place, the > transaction entry now starts with: BANK CARD PURCHA...

cell outside range
Intermittently, a cell erroneously appears outside the range of cells in an active worksheet. This cell contains duplicate data from one of the other cells. I am not able to replicate the sequence of events that causes this cell outside the range of cells to magically appear. I wonder if anyone else has seen this problem. Thanks, Mrs. T Are there any macros in any open workbook? "Mrs. T." wrote: > Intermittently, a cell erroneously appears outside the range of cells in an > active worksheet. This cell contains duplicate data from one of the other > cells. I am ...

format cells #4
hi. how can i set by default the cell formatting to 'general' or 'number' or even 'text'? i mean i dont want excel to correct let's say: 1.5 to 01.may or 9/12 to 09.dec. is this possible? thanks. (i have some programs which export data to excel and instead of numbers in excel appear dates and i can't set the cell format before the data is exported) -- notrace2004 ------------------------------------------------------------------------ notrace2004's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34817 View this thread: http://www....

how do i write a sentence in a cell?
im reli confused i dont no how to write a sentence in a exel cell without it running into the cell next to it n its buggin me cos im having to ut a good paragragh in a cell n its not workin Select the column, say col A Click Format > Cells > Alignment tab Check "Wrap text" > OK Now when you type in col A, the text will wrap around w/o running into the next cell. You could of course, increase the width of the column by dragging the right edge of the column header to the right, etc And if you need to break for a new line within the same cell, press Alt + ENTER -- Rgds Ma...

Text don't fit in a cell
Is there a limit to the text count in a cell? I have wrap text on and I even increased my height and width but I can only see up to a certain point of my entry. I even tried making my font smaller but all it does is give me the extra space but still end at the same spot of my entry. If there is a limit, is there a way to override that? Please help. Hi there's a limit of 1,024 characters per cell (in the formula you can see up to 32K). You can extened this limit by manually entering linebreaks with ALT+ENTER -- Regards Frank Kabel Frankfurt, Germany "Sue K" <anonym...

How to have TAB name to appear in a Excel cell?
How do I get the Worksheet name to appear in an Excel cell? See http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH RP "TAB name to appear in a Excel cell?" <TAB name to appear in a Excel cell?@discussions.microsoft.com> wrote in message news:2DBC0B15-12FC-43FA-9D11-AFD1B4B286BF@microsoft.com... > How do I get the Worksheet name to appear in an Excel cell? Hi see: http://www.xldynamic.com/source/xld.xlFAQ0002.html -- Regards Frank Kabel Frankfurt, Germany "TAB name to appear in a Excel cell?" <TAB name to appear in a Excel cell?@discussions.micr...

Formating query
I use excel as a database for storing / manipulating info for my web site which deals with bird checklists. The bird names are entered as follows... Common Buzzard (bold) space, Buteo buteo (italicised). My problem is that I have to merge the two tables (bold name) & (latin name in italics) into one cell but I cannot get the formating to stay. That is, the italicised name always follows the original cell format. Is there a symbol or control symbol I can insert between the bold and italic names to maintain the formating? I can change the format manually but with 20,000 entries to c...

lookup value & formatting
Hi, Is it possible to undertake a vlookup function or such like which not only imports the value that is found, but also the format of the value, be it a different font or colour instance? Thanks Simon Only by using VBA. I suggest the following :- 1. Select cell with value 2. Find the value in the lookup sheet 3. Copy the cell containing the return value. 3. Go back to the original sheet & Paste (with format) Something like this (you will need to amend as necessary) :- '------------------------------------------------------------------- Sub GET_LOOKUPVALUE() Dim MyVariable ...