#1   Report Post  
Posted to microsoft.public.excel.misc
dpb dpb is offline
external usenet poster
 
Posts: 109
Default 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)?

--
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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)
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.misc
dpb dpb is offline
external usenet poster
 
Posts: 109
Default 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.

--

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup puzzle lloyd Excel Worksheet Functions 3 December 6th 07 07:45 PM
Pivot Puzzle Wanna Learn Excel Discussion (Misc queries) 3 November 1st 07 12:11 AM
VLOOKUP puzzle ?? Anthony Excel Worksheet Functions 8 November 29th 06 05:49 PM
Number Puzzle PetaPan via OfficeKB.com Excel Programming 2 January 30th 06 04:39 PM
Can you help!!!!! New Puzzle Krefty Excel Discussion (Misc queries) 0 June 13th 05 08:13 PM


All times are GMT +1. The time now is 02:47 PM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"