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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|