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

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?

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

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

If this post helps click Yes
---------------
Jacob Skaria


"Pennyc" wrote:

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?

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

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" wrote:

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?

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

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pennyc" wrote:

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?

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

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

On Sep 21, 4:40*pm, Pennyc wrote:
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?

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

This did the trick. Thanks so much!

"Jim Thomlinson" wrote:

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" wrote:

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?