A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Vlookups and formats



 
 
Thread Tools Display Modes
  #1  
Old February 24th, 2010, 11:14 PM posted to microsoft.public.excel.misc
Gandor
external usenet poster
 
Posts: 14
Default 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.



  #2  
Old February 24th, 2010, 11:33 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Vlookups and formats

=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
  #3  
Old February 24th, 2010, 11:34 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Vlookups and formats

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
  #4  
Old February 25th, 2010, 08:19 PM posted to microsoft.public.excel.misc
Gandor
external usenet poster
 
Posts: 14
Default Vlookups and formats

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
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:14 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.