Vlookups and formats

I am using the following formula to pull in the worksheet name into cell B5

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

The worksheet name is an account number.

I then use vlookups to look at B5 as the lookup value to retreive various 
points of data linked to that specific account number.

The problem I have is the formula shown adove brings the sheet name in as 
text and the vlookup sees it as text.  I know this because when I edit the 
function the returned value for  the lookup value is in quotes "1234".  If I 
type in 1234 in a number format it works fine.

My questions is there a function I can embed the formula above into so it 
returns a number format.



0
Utf
2/24/2010 10:14:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
729 Views

Similar Articles

[PageSpeed] 57

=val(mid(....))
will convert that text number to a real number.

So will:
=--(mid(...))
(the first minus makes it a number (but opposite in sign), the second minus
changes it back to the original sign)

Gandor wrote:
> 
> I am using the following formula to pull in the worksheet name into cell B5
> 
> =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
> 
> The worksheet name is an account number.
> 
> I then use vlookups to look at B5 as the lookup value to retreive various
> points of data linked to that specific account number.
> 
> The problem I have is the formula shown adove brings the sheet name in as
> text and the vlookup sees it as text.  I know this because when I edit the
> function the returned value for  the lookup value is in quotes "1234".  If I
> type in 1234 in a number format it works fine.
> 
> My questions is there a function I can embed the formula above into so it
> returns a number format.

-- 

Dave Peterson
0
Dave
2/24/2010 10:33:52 PM
You could also use:
=1*(mid(...))
or
=0+(mid(...))

Or anything that treats that text number as a number (without changing the
value).

Gandor wrote:
> 
> I am using the following formula to pull in the worksheet name into cell B5
> 
> =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
> 
> The worksheet name is an account number.
> 
> I then use vlookups to look at B5 as the lookup value to retreive various
> points of data linked to that specific account number.
> 
> The problem I have is the formula shown adove brings the sheet name in as
> text and the vlookup sees it as text.  I know this because when I edit the
> function the returned value for  the lookup value is in quotes "1234".  If I
> type in 1234 in a number format it works fine.
> 
> My questions is there a function I can embed the formula above into so it
> returns a number format.

-- 

Dave Peterson
0
Dave
2/24/2010 10:34:41 PM
Thanks Dave that was what I was looking for. I was thinking to complex and 
just needed another look.  Thanks Again.

"Dave Peterson" wrote:

> You could also use:
> =1*(mid(...))
> or
> =0+(mid(...))
> 
> Or anything that treats that text number as a number (without changing the
> value).
> 
> Gandor wrote:
> > 
> > I am using the following formula to pull in the worksheet name into cell B5
> > 
> > =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
> > 
> > The worksheet name is an account number.
> > 
> > I then use vlookups to look at B5 as the lookup value to retreive various
> > points of data linked to that specific account number.
> > 
> > The problem I have is the formula shown adove brings the sheet name in as
> > text and the vlookup sees it as text.  I know this because when I edit the
> > function the returned value for  the lookup value is in quotes "1234".  If I
> > type in 1234 in a number format it works fine.
> > 
> > My questions is there a function I can embed the formula above into so it
> > returns a number format.
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
2/25/2010 7:19:02 PM
Reply:

Similar Artilces:

Outlook XP / html format problem
Hi, Have a user with a wierd email problem in Outlook XP/2002 (exchange server). When sending an email, it seems that only the subject is sent, and the whole message body is blank. I've narrowed it down to sending email in HTML format. He can read any email in HTML format but when he replies or forwards, there is nothing in the message, just a subject. If I set to Rich-text or Plain text and send new message, it is fine. If I get an incoming HTML message, and forward as a Richtext ot plain text, it is fine. I've created another email profile, with no luck. I've r...

Excel amount field formatting error
Excel 2000 version 9.0.6926 sp-3. My amount is 18.00. I select format-number-currency and my amount becomes $1800.00. Always adding 2 zeroes! It formats correctly if I enter just 18. If my amount is 18.25 it formats it as $1825.00. I've tried several different formats with no success. My computer is an hp pavilion xt926. Can someone please help me?!?! Thank you. Lynn Hi, Lynn. Thanks for providing all the info---most people don't. Go to Tools-->Options, and click the Edit tab. Untick "Fixed Decimal" thingee. :) ******************* ~Anne Troy www.OfficeArticles.com &q...

basic format question...I think !!
I have multiple pages on one sheet running left to right. Is there a way to adjust the height of one row on one page with out effecting the row across the whole sheets on either side of it. I know I can merge or insert rows but I would like to just click and drag the selected cells until I get the correct height. Thanks -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =----- JG, The entire row must have the same height. Avoid merging cells -- it cause...

Conditional Formating (If statements)
In the conditional formatting box how would I make B4 turn yellow if F12 is between 10 and 15? Right-click Sheet, click 'View Code', and paste this into the window that opens: Private Sub Worksheet_Change(ByVal Target As Range) Dim rnArea As Range Dim rnCell As Range With rnCell If Range("F12").Value >= 10 And Range("F12").Value <= 15 Then Range("B4").Interior.ColorIndex = 6 Else: Range("B4").Interior.ColorIndex = 0 End If End With End Sub -- Ryan--- If this in...

2003 Form fields losing formatting in 2007
Hi there, I have some forms, created in Word 2003, which when opened as a .docx, all form fields revert to Times New Roman font but if they are opened as a .doc, they are in the correct font of Arial. Anyone got any ideas why and how I can fix this? Thanks very much. This doesn't make any sense. Documents always open in the format in which they are saved. What *exactly* are you doing? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com ...

EXCEL
Is it possible to put more than 3 conditional formats? I want to put around 8... If 0-5 blue, If 6-10 red, If 11 - 15 brown etc.... Thanks.... No, I believe 3 is the maximum. >-----Original Message----- >Is it possible to put more than 3 conditional formats? >I want to put around 8... If 0-5 blue, If 6-10 red, If >11 - 15 brown etc.... >Thanks.... >. > You'll find that you can't add more than 3 conditions. i.e. the answer to the OP's question is No. You would have to use an event macro (maybe On_Calculate) to scan the cells and set the colors if ...

Icon sets - conditional formatting
How do I get Excel 2003 to do this? I want to be able to add increase and decrease arrow icons to a column of numbers . Thx Upgrade to Excel 2007 or see these products; http://www.ozgrid.com/excel-add-ins/mini-charts-excel.htm http://www.ozgrid.com/excel-add-ins/spark-maker.htm -- Regards Dave Hawley www.ozgrid.com "Lou" <Lou@discussions.microsoft.com> wrote in message news:37AE35EE-B3C4-46B2-98F2-8A0B9627E12D@microsoft.com... > How do I get Excel 2003 to do this? > > I want to be able to add increase and decrease arrow icons to a colum...

Using possibly If and Vlookup
I have a spreadsheet where I want to first of all look up hours worked in cell N2 (within this cell is the number 35.00 or the number 37.00). I then want to use this cell to do a lookup bringing back one salary if N2 is 35 or another salary if N2 is 37. In my main spreadsheet I have a spinal column point in cell J2. I have created two lookup tables on another spreadsheet named salary35 and salary 37. These tables show the spinal point in one column and the appropriate salary in anoter column. eg salary35 1 11187 3 11534 5 11899 7 12246 9 12629 salary 37 1 11827 3 12193 5 12579 7 12...

hyperlink with vlookup
=HYPERLINK[=vlookup(a2;CARATULAS!$A$4:$D$146;4;0)] I´ve got to create an hyperlink that (according to a value in the same row) takes me to the same value in another worksheet. Please help me!- Thanks One play which could do this .. Link to a sample file at: http://www.savefile.com/files/6398176 File: AutoHyperlink_Mariano922_wksht.xls Assume you have this set-up In Sheet1, cols A & B, data from row2 down ------------ Prod# UnitPrice 1111 10 1112 20 1113 30 1114 40 1115 50 etc In CARATULAS -------- Prod# are listed in col A & BulkPrice in col D (col D is the lookup col), wi...

Opening Page format
Using MS Publisher 2002, I want the opening page to show only a blank page, not a graphic showing - in a third of the screen - a selection of "scheme - options - designs" Help/advice will be appreciated. If it takes up a third of the page you must be using 640 x 480 - UNBELIEVABLE Go to menu Tools > Options tab General second item down - DE-select -- On Wed, 10 Sep 2003 17:44:07 +1000, "���MS�Publisher���" <donotknock@nobodyhome.com> wrote: >If it takes up a third of the page you must be using 640 x 480 - >UNBELIEVABLE I'm not. > >Go to me...

Work around Conditional Formatting
Dear all, There are 2 worksheets (Sheet1, Sheet2) containing similar data in my workbook. I want to underline the text in a cell in Sheet2 if the text in that cell is the same as the cell in the same place in Sheet1. (For example, if Sheet1!A1 = Sheet2!A1, then Sheet2!A1 is underlined.) Can I write a user-defined function to check whether the contents in two cells are equal? Thanks in advance. Best Regards, Andy Chips has lots of stuff on duplicates, see www.cpearson.com -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Andy Chan" <chankhan...

Text emails do not format
All of my incoming emails in Outlook 2003 are coming in as one long line of text. I've gone into my email options and turned off "Remove extra line breaks from text emails" but the problem still occurs. Does anyone know how to prevent my incoming text emails from turning the entire message into one long paragraph? Thanks! Fury Maybe the issue is with the person (or people) sending it. As a test, log into your webmail account, send yourself and email and then go into Outlook 2003 and see what it does... >-----Original Message----- >All of my incoming emails in ...

Simple Format for Customer Statement
Hello All Is there available another format for printing customer statements? I'm mostly interested in a format that shows charges and payments without all the details and allocation to invoices. In other words I need a statement is the old fashion simple (Debit, Credit, Balance) format. Thank you Thanks Morris, I'll try it out. "Morris Paint" wrote: > This is our current statement. We are having another one written that shows > current period transactions in one area and all open invoices in another. > > "Mario" <Mario@discussions.microsoft...

second() format
Is there a way to enter 3.5 seconds and display decmial point in the second field? Use a custom format like: hh:mm:ss.00 ???? Frankie wrote: > > Is there a way to enter 3.5 seconds and display decmial > point in the second field? -- Dave Peterson ec35720@msn.com ...

Formatting cells for big numbers and formulas ...without the formu
Hello, I am stuck with this: If I want cells to display like 20-digit numbers in numerical form (that is, not the xxx+EXX way), formatting them as a text is okay. But then I need to add formulas and want the results to be displayed. But as the cells are text formatted, the result in the cell is of course the text of the formula plus the number. So my question is - in a cell I want e.g. "12345678901234567890-sum(A1,B1)" and I want the cell to display the resulting number, not the formula itself. How do I achieve this? The maximum precision for a number is 15 digits according to...

MS Word Formatting
When I start a document and use font size 36 on the first page and go to the second page and change the font it changes it on the first page. Also when I highlight a few words to bold or underline it bold or underlines everything in the whole document. Does anyone know where I change this setting?? HELP PLEASE! For Word you need to go to the Word Newsgroup. If this is an issue in Publisher with an imported Word document, you need to give substantially more information. What is the version of Windows, Publisher and Word involved. Is this a Template you are using, and what type of document...

create a csv file type format for a centrally access address book
Hello, We have Outlook 2003 clients on a LAN with a Small Business Server 2003 Standard editon box. We require a single fax document sent to new or existing clients with using our shared fax service from the SBS. Is there a way to create a .CSV file type format in Microsoft Excel for our new client database and import this information into Exchange as a seperate address book for fax use? Thank you in advance for your assistance. Joe See my response to your post in the contacts newsgroup.=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart...

In Excell-2000 where do I set format for date?
In Excell-2000 where do I set format for date? In some tables I have it in dd/mm/yy, but in the others mm/dd/yy How do I change mm/dd/yy to dd/mm/yy? On my w2k Pro, Control Paned date format is set to dd/mm/yy yout it does not seem to affect Excel Hi If you select the cells with the dates in mm/dd/yy and go Format>Cells and in the dialog that opens, go for the Number tab, you can select the Date formats and choose whichever one suits or select Custom and type dd/mm/yy into the Type box. This will change the formatting of your dates. Hope this helps! Richard On 28 Jan, 16:34, &q...

Newbie Help for Custom Number Formats
Hey All, I am working on a spreadsheet that I would like to have it append the inch symbol as part of the number format ("). I figured out that I can add custom suffixes with the use of the quotation marks, but I can't place a quotation mark inside of quotes and get what I need. Is there any way to have Excel do this for me? I know it seems like a small thing to do, but having Excel add this for me would save me a lot of time with formulas and all. Example: 24" Thanks in advance. JB -- joshb416 ------------------------------------------------------------------------ j...

Vlookups and formats
I am using the following formula to pull in the worksheet name into cell B5 =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) The worksheet name is an account number. I then use vlookups to look at B5 as the lookup value to retreive various points of data linked to that specific account number. The problem I have is the formula shown adove brings the sheet name in as text and the vlookup sees it as text. I know this because when I edit the function the returned value for the lookup value is in quotes "1234". If I type i...

Conditional Formatting in VBA in 2007
I have a series of non-contiguous rows that I want to apply conditional formatting to. Right now, my code snip looks like this: With Range(sBegRange, sEndRange) .FormatConditions.Add Type:=xlExpression, _ Formula1:="=" & sCFCell & ">40" .FormatConditions(i).Font.Color = 3 .FormatConditions(i).StopIfTrue = False End With Where sCFCell is a string value for the cell I want evaluated for the formula, and i is an integer that increments for each time I create a new rule (which I'm doing for each row I format). It...

Access 2003 file format advange?
We're upgrading our system from Access 2000 to Access 2003. I'm aware that version 2000 databases can be opened in Access 2003 without conversion, and it did, in fact open, but are there any benefits to converting the database to version 2003? Or, are there reasons why our 2000 database should NOT be converted to 2003? It's hard to beleive that it wouldn't matter one way or the other, and think there must be some advantage to converting to a file format of "2003" if we're using Access 2003. Many thanks in advance. On Tue, 17 Feb 2004 12:31:13 -0500, Si...

Conditional formatting #87
Can I do a conditional formatting on a range if the value in one cell in the range = X in other words can I format the back ground for line 5 if C5 = X Use conditional formating. Select the whole row Set the condition Formula Is =$C$5="X" Choose the Format you want for the cells in that row. -- Regards Roger Govier "ESREI" <anonymous@discussions.microsoft.com> wrote in message news:339b01c51f10$38d61860$a601280a@phx.gbl... > Can I do a conditional formatting on a range if the value > in one cell in the range = X > > in other words can I format the b...

Pasting Excel chart into Word loses formatting
How can I paste an .xls chart into Word and retain the formatting? I tried copying and pasting (with Paste Special and regular Paste) and I am losing the line spacing when the chart displays in Word. Is there any way to gain control over this? Hi, What do you mean by line spacing? what version of Excel are you using? One why is to insert as Excel Chart or Worksheet Object. Another general rule is to create a default size chart in Excel, don't resize it. Then copy and paste it. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Sandra" wrote: ...

Changing My Outlook 2003 PST from 97-2002 to Unicode Format?
What is the best way to get the data from my PST - presently in the older Outlook 97-2002 format - into a new PST in the newer Outlook 2003 Unicose format? (I am using Outlook 2003) I've had Outlook installed as part of MS Office for a few years, starting with, I believe, OL 97. Each time I upgraded to a new version, I just ran the installer and allowed it to use the default. When it sensed, or asked and I responded, the previous version's data it just "took over" the existing PST. Always seemed OK to me. But now that I have had Office/Outlook 2003 installed for over a ...