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

P

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?
 
J

Jacob Skaria

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

If this post helps click Yes
 
J

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))
 
L

Luke M

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

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
 
P

Pennyc

This did the trick. Thanks so much!

Jim Thomlinson said:
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


Pennyc said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top