Function to put worksheet name in cell returns text, need number

[Pennyc]

I found the following great formula on this website to put my worksheet name
into a cell on that worksheet. (this is in Cell L2 of each worksheet)

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

However, I need to format that result as a number because I'm doing a
Vlookup using that value as the reference.

=VLOOKUP($L$2,'PO Log'!$B$4:$BW$503,4,FALSE)

The range 'PO Log'!$B$4:$BW$503 contains formulas that add 1 to the value in
the cell above to create a list (which will have a different beginning number
depending on the user), so I really can't change these values to a text
format and keep the same functionality of the workbook.

I've tried changing FALSE to TRUE, but that won't work. I also tried to
force the result of the MID fucntion into a numeric format, but no success
there either.

=TEXT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"######0")

Even if I copy & paste Values where the MID function is, I cannot reformat
that value as a number.

Is there anyway to ensure that the MID function returns a numeric value?

I should mention that I created and named each of these worksheets with a
macro (from this site) that uses the numbers in the reference range on the
'PO Log' page to create the sheet name in the first place.

I'm desperate as I am scheduled to roll out this worksheet to a group of my
peers tomorrow. Can anyone help?

 

[Jacob Skaria]

Try
=--MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

If this post helps click Yes

 

[Jim Thomlinson]

Mid returns a string. Try this to coerce the string to a number...

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

 

[Luke M]

You could try encasing your formula within either the VALUE or N functions.
(See XL help file)

 

[Pete_UK]

Another way is to change your lookup formula:

=VLOOKUP($L$2*1,'PO Log'!$B$4:$BW$503,4,FALSE)

The *1 will force the value of L2 to a number, so you could put it
here or add it to the formula in L2.

Hope this helps.

Pete

 

[Pennyc]

This did the trick. Thanks so much!

Mid returns a string. Try this to coerce the string to a number...

=value(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))
--
HTH...

Jim Thomlinson

I found the following great formula on this website to put my worksheet name
into a cell on that worksheet. (this is in Cell L2 of each worksheet)

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

However, I need to format that result as a number because I'm doing a
Vlookup using that value as the reference.

=VLOOKUP($L$2,'PO Log'!$B$4:$BW$503,4,FALSE)

The range 'PO Log'!$B$4:$BW$503 contains formulas that add 1 to the value in
the cell above to create a list (which will have a different beginning number
depending on the user), so I really can't change these values to a text
format and keep the same functionality of the workbook.

I've tried changing FALSE to TRUE, but that won't work. I also tried to
force the result of the MID fucntion into a numeric format, but no success
there either.

=TEXT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"######0")

Even if I copy & paste Values where the MID function is, I cannot reformat
that value as a number.

Is there anyway to ensure that the MID function returns a numeric value?

I should mention that I created and named each of these worksheets with a
macro (from this site) that uses the numbers in the reference range on the
'PO Log' page to create the sheet name in the first place.

I'm desperate as I am scheduled to roll out this worksheet to a group of my
peers tomorrow. Can anyone help?