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