A fun puzzle...
 

I was trying to build a formula to return the sheet name in a cell --

=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

works for a given sheet, but it breaks when trying to use on more than
one sheet in a workbook as it is always trying to reference the first sheet.

Is there any way to get a numeric reference to the ordinal sheet
position in the CELL() reference position (other than using the text
sheet name expressly which defeats the whole ease of use thing for
wanting such)?

--

Answer: A fun puzzle...
 

1. To get the numeric reference to the ordinal sheet position in the CELL() reference position:
   
   Code:

   ---

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

   ---

2. To get the sheet name of a different sheet:
   
   Code:

   ---

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

   ---

A fun puzzle...
 

Hi Duane,

Am Fri, 28 May 2021 14:21:14 -0500 schrieb dpb:

I was trying to build a formula to return the sheet name in a cell --

=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

works for a given sheet, but it breaks when trying to use on more than
one sheet in a workbook as it is always trying to reference the first sheet.

Is there any way to get a numeric reference to the ordinal sheet
position in the CELL() reference position (other than using the text
sheet name expressly which defeats the whole ease of use thing for
wanting such)?

CELL needs a second argument, a reference.
Try:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)


Regards
Claus B.
--
Windows10
Microsoft 365 for business

A fun puzzle...
 

On 5/28/2021 3:44 PM, Claus Busch wrote:
Hi Duane,

Am Fri, 28 May 2021 14:21:14 -0500 schrieb dpb:

I was trying to build a formula to return the sheet name in a cell --

=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

works for a given sheet, but it breaks when trying to use on more than
one sheet in a workbook as it is always trying to reference the first sheet.

Is there any way to get a numeric reference to the ordinal sheet
position in the CELL() reference position (other than using the text
sheet name expressly which defeats the whole ease of use thing for
wanting such)?

CELL needs a second argument, a reference.
Try:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)


Regards
Claus B.


Aha! I interpreted the optional reference help description incorrectly
as being the same thing...then the examples I found used a sheet text
name, not a cell.

Thanks, that does fix the problem. Why there isn't a direct function is
mindboggling.

--