Vlookup and text format

If I have a cell formatted as TEXT and enter a Vlookup function, the cell
displays the Vlookup formula rather than the result.

If I try to reformat the cell to GENERAL the cell still displays the Vlookup
formula.

If I clear the cell, format it as GENERAL then re-enter the Vlookup it
displays the correct result.

Shouldn't I be able to change the format of the cell from TEXT to GENERAL
without having to re-enter the VLOOKUP function??



0
tgegan55 (3)
8/26/2004 4:04:56 AM
excel 39880 articles. 2 followers. Follow

1 Replies
449 Views

Similar Articles

[PageSpeed] 6

Yes you can.
You then have to "re-enter" the formula to, (for want of a better term),
register the change.
*HOWEVER* ... re-enter doesn't necessarily mean to retype the entire
formula.
Simply clicking in the formula bar and then hitting <Enter> will do the job
*after* you change the format to General.

A nice keyboard shortcut to do all this in one shot is:
Select the "bad" formula cell, then,
<Ctrl> <Shift> <~>, then
<F2> <Enter>


-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"hooper" <tgegan55@yahoo.com> wrote in message
news:xcdXc.24060$L94.392@fed1read07...
> If I have a cell formatted as TEXT and enter a Vlookup function, the cell
> displays the Vlookup formula rather than the result.
>
> If I try to reformat the cell to GENERAL the cell still displays the
Vlookup
> formula.
>
> If I clear the cell, format it as GENERAL then re-enter the Vlookup it
> displays the correct result.
>
> Shouldn't I be able to change the format of the cell from TEXT to GENERAL
> without having to re-enter the VLOOKUP function??
>
>
>

0
ragdyer1 (4060)
8/26/2004 5:42:55 AM
Reply:

Similar Artilces:

how can i apply conditional formatting by code ?
Say that there is a table T_PERSONAL [Name (text), Surname (text), Salary (integer)]. I created a Tabular Form (F_PERSONAL) which retrieves information from T_PERSONAL. I want to put a image for a specific record on the form, if the record satisfy some specific criteria. For other record which does not satisfy the criteria, image should be invisible. Say that, if [Name (text)] ~text13~ is "ALBERT", Image Object ~Image13~ should be visible. I dont suppose that Image Object should be included in T_PERSONAL. It will be inserted manualy during design stage of Tabular Form. I would not as...

text field with input mask
Hi all,I have a text field with field size of 6, input mask of &99.99. So it is ok for me to data entry data like E55.66, or 777.88. Now I need enter E123.45. If I change it to field size 7 and input mask of C999.99, I can enter E123.45, but all of my old data will be wrong. What should I do to keep all the old data right and also be able to enter my now data?Thanks,Blinda "Blinda" wrote:> Hi all,> > I have a text field with field size of 6, input mask of &99.99. So it is ok > for me to data entry data like E55.66, or 777.88. > > Now I need enter E123.45. If...

Extra text added by system when converting to PDF?
I've been using PDFMaker to convert my MS-Word styles to bookmarks for the past couple of years, but now that my office has upgraded us to Acrobat 8, I'm running into a problem. Each time a bookmark is created from a Word doc, there is a very small bit of text added to the front of it. The system seems to be numbering the bookmarks sequentially, starting with "0B" for the first bookmark, and continuing for the rest. They're very small characters, and unless you zoom in very closely, they look like periods at the front of each line. Example: .Bookma...

Importing text files into Excel
Does anyone know of a quick and easy way to bring large text files into Excel. The data in the text files is separated by spaces. The spaces that separate the data vary in number. I have tried to replace the spaces with tabs while in Notepad but it takes too long. Any help would be appreciated! Thanks Chris Open Excel and create a blank workbook. Then: Data | Get External Data | Import Data (this command might be something like Import Text File, if you are using an older version of Excel). Browse to find your file and click to Import it. Should open up the Text Import Wizard. Make...

using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

Centering text on a page
Is there a way to center text automatically in a row across the top of the page without having to pick a column that is close to center, then using the space bar to find the exact midpoint? Thnxs Hi Justin! Select the cells across your page Format > Cells > Alignment Place check in "Merge cells" Horizontal and Vertical drop downs should be set at Center OK There is a standard toolbar button for this. Use: View > Toolbars > Customize Look under Format for the Merge and Center button Click and drag it to your toolbar. -- Regards Norman Harker MVP (Excel) Sydney, A...

Drop down boxes AND free text?
All, Is it possible to create drop-down boxes within Excel AND allow user to input free text when the choice is not available? I know this seems to violate the idea of "validating". What I'm trying to do is provide my users with a fairly complete lis of manufacturers from which we can buy. However, I cannot completel predict 100% of the manufacturers we'll ever use. HELP -- Message posted from http://www.ExcelForum.com why don't you let them add to the list of manufacturers? check out http://www.mvps.org/access/forms/frm0015.htm - Add item to combo box using OnNot...

vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

Excel XP
I am trying to import a text file into an Excel XP worksheet, every time I attempt this I get the error 'The file may be read only or I may be trying to access a read only location'. I can import the same text file to an Excel 2000 worksheet with no problems! Is there a security filter running somewhere in the background in Excel XP? Whats my problem? Can someone help!!! I've never seen this and don't know what causes it. But there was just a recent post that had the same problem. But their file was on a network drive. When it was copied to a local drive, the import w...

Screen capture of text
I have built an access database to work in conjunction with a companies mainframe. The system tracks things that the mainframe system does not. What I want to do is figure out how to program something to capture text off the screen and populate certain fields in my database. This way the user doesn't need to retype the same information that is on the screen already. Has anyone done anything like this? Robby Hi Robby, I wouldn't fancy your chances of doing a direct screen capture. However, if the data being displayed from the mainframe is in a file, or can be dumped to a file...

Outlook XP HTML email is plain text in OE6...why?
When my Father sends an HTML email to me it shows as plain text in Outlook Express6. We are both using WinXP (Him with Home and me with Pro). He is set to send HTML. His HTML message is properly formatted when received on my other computer using Outlook 98. I can receive HTML mail from others. I can receive HTML from my Outlook 98 computer that look fine. Is something wrong somewhere? Thanks Scott Do you have OE set to read all messages in plain text (Tools | Options | Read tab)? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please ...

Include pre-formatted files/pdf's in report
When sending out customer orders I today include an invoice and a packing list printed from MS-Access. However, I would like to add various information to the customer, such as technical information, promotions etc. These extra informations would be defined on each order, so what I am after is how to include these text fragments in the report print-out. Should this be done as images (jpegs/tiff's) or is it possible to use pdf documents? If the customer order for example contains a reference to the text fragments P017, TD061, B741, could I then get these three files printed out li...

Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

adding to a number containing text
Hi, =(sum(A3+1) normally works great for me but now I am dealing with a number which contains text ie 'PE10000', how do I write a formula to add 1 to this to give 'PE10001'? Many thanks. Rick If the text is always 3 letters to the left of the number =IF(ISERR(--(A3)),LEFT(A3,2)&MID(A3,3,255)+1,A3+1) if the text is always to the left of the number but can differ when it comes to numbers of characters =IF(ISERR(--(A3)),LEFT(A3,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A3)),FIND({1,2,3,4,5,6,7,8,9,0},A3)))-1)&MID(A3,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A3))...

Transferring Text
I need to be able to send data from one worksheet to another. Sheet 1 is the master and only sheet where data is entered. I have found out all cells have to be set to "Text" - which is not a problem. Sheet 1 Column A is Name Column B is Date of Birth Column C is the Surgery's name Is there a way that when the Surgery name is entered in say C5 the row's Name and Date of Birth can be sent to the next vacant row in say Sheet 3. Because of the number of surgeries, it is not possible to set them up to receive data from Sheet 1 - which is much easier, of course. I thought o...

What is CV text?
Can someone tell me what CV text is? Do you mean CSV, comma separated values? -- HTH RP "Tina" <Tina@discussions.microsoft.com> wrote in message news:ED97A150-9075-4251-8739-1E73F647527C@microsoft.com... > Can someone tell me what CV text is? And if you did mean CSV (comma separated values), then the name is pretty descriptive. But if you start a new test workbook and populate a bunch of cells with dummy data, then save it as a .CSV file. Then close excel. And open that .csv file in NotePad to see what it looks like. There are lots of programs that don't tal...

Format Records alternating colours.
Hi I would like to have aternating rows differnet colours. eg. customer no 123 red 123 red 456 green 456 green 456 green 789 red 789 red Can you tell excel to do this? mike Try this: Insert a blank (helper) column before your data. (I'll assume it will be Col_A. Col_B will list customers) A2: =IF(B2=B1,A1,NOT(A1)) Copy A2 down as far as you need Select from B2 down and to the right as far as you have data ...with B2 as the active cell. From the Excel Main Menu: <format><conditional formatting> Condition_1: Formula is: =$A2 Click the [Format] button ....select the Re...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

splitting text to array,is it possible?
the text is seperated by "_", like("abc_xyz_spq") and is not set how many "_"s there("abc_xyz_spq","xyz_spq","abc_xyz_spq_pqr" etc). I want to do something to each text(abc,xyz,spq, etc). How can I split them without VBA. StringTokenizer-like function. Data>Text to columns, choose Delimited, Next, check Other and fill in the underscore -- Kind regards, Niek Otten Microsoft MVP - Excel "kang" <kang@hao.com> wrote in message news:eM9BE0W6HHA.4880@TK2MSFTNGP03.phx.gbl... | the text is seperated by "_", l...

Data Text to Columns
I have a large column and would like to split it for the literal that says "Company Name" and then have the company name right next to it which is enterable. I tried using Data/Text to Columns, Fixed Width, sliding the arrow after Company Name: and it did not seem to split the column. I also tried putting an arrow out a ways so that there looked as though there were two defined fields and that didn't seem to work either. Am I approaching this the right way??? HELP!!! Thanks! Hi this is not possible in Excel. You have to use two columns for this -- Regards Frank Kabel Fr...

NAICS Code
Hello, I have a list of NAICS codes formatted as numbers that I would like to format as hierarchical text. Current List 10 12 101 112 Desired List 10 101 112 12 Does anyone know how to change the cell format so that the "Current List" becomes the "Desired List"? Thank you in advance for your help. It looks like you could preformat that column as text and enter your values as text. Then you could sort the data as text. Some versions of excel will ask if you want to sort values that look like numbers as numbers. You should say no to that prompt. And just changing t...

Converting TOC to Text
I cut and paste text from a table of contents into a Word 2003 document. It is very lengthy and I'd rather not re-type the whole long list, since it would take a very long time. However, the TOC formatting is still in the document and I can't get rid of it. I can't include hyperlinks or change font color as a result. I've tried clearing all formatting without effect. Anyone know how to convert a TOC into text? Thanks! Ctrl+Shift+F9 will unlink it. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org &q...

Column width and pasting sections in excel? Formatting questions
I am trying to make a spreadsheet in which I need to have varied column widths in different sections, one under another. (they don't need to relate directly, and no major equations going on) for example: xxxxxxxxx|xxxxxxxxxx xxxxxxxxx|xxxxxxxxxx xxxxxxxxx|xxxxxxxxxx xxxxxxx|xxxxx|xxxxxx xxxxxxx|xxxxx|xxxxxx How do I go about splitting the sheet or whatever I need to do so I can manipulate columns differently based on the row I'm in? Thanks! Rich Column widths apply to the whole column. You may be able to use merged cells to give the appearance that you want, but I try to stay...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

Average a text file issue Excel 2003
I am assisting a customer who was recently upgraded from Office 2000 to Office 2003. The customer claims this formula worked fine in Excel 2000. He is averaging data files which are text with a *.dat extention. The formulas look like this: =AVERAGE('C:\Path_to_data_file\[R0131.dat]R0131'!$G$2:$G$120) When the file is opened he is prompted as he should be to update links. If the *.dat file has data in the g2:g120 range, the average formula works fine and updates when the links update. If the *.dat file has any null values towards the end of the file or if data is in the g2:g110 r...