Referencing a worksheet name

  • Thread starter Thread starter DannyJ
  • Start date Start date
D

DannyJ

Dear All,

I am creating a generic workbook. Each worksheet of which relates to a
member of staff. An example of the sheets is Summary, Name 1, Name 2, Name
3. I would like to define cells in the summary worksheet so that the value
returned is the name of the sheets. So if sheet "Name 1" is renamed "Bill
Smith" Bill Smith appears in the relevant cell in the summary sheet.

I hope this is clear and if you could help i would be grateful.

Many thanks,

Danny
 
The file path and name
=CELL("filename",A1)

The file path
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

The file name
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename"
,A1),1))

http://www.bygsoftware.com/Excel/functions/cell.htm
 
Thanks guys,

I tried this
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename"
,A1),1))

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

and though both return the sheet name, it is the sheet where the formula is.
Can you tell me how I get it to return other sheets?

Many thanks, ,

Danny

Andy Wiggins said:
The file path and name
=CELL("filename",A1)

The file path
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

The file name
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename"
,A1),1))

http://www.bygsoftware.com/Excel/functions/cell.htm

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

DannyJ said:
Dear All,

I am creating a generic workbook. Each worksheet of which relates to a
member of staff. An example of the sheets is Summary, Name 1, Name 2, Name
3. I would like to define cells in the summary worksheet so that the value
returned is the name of the sheets. So if sheet "Name 1" is renamed "Bill
Smith" Bill Smith appears in the relevant cell in the summary sheet.

I hope this is clear and if you could help i would be grateful.

Many thanks,

Danny
Click to expand...
Click to expand...
 
One way:

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

When Sheet2 is renamed, XL will change the displayed sheet name in the
formula.
 
Thanks a bundle, that worked beautifully. If you have the time would you
explain to me how it works?

I looked up cell filename in help and it says that it returns the filepath.
But surely the filepath and sheet name are different so I am a bit confused.
However assuming that the sheet forms part of the file structure the Find
"]" bit means return the text up to this point, is that correct?

I looked up the Mid bit and that seems straightforward enough: It looks as
though it returns a text string (presuambly the sheet name) starting at
letter 1 and going on to letter 255 (wow long name) is this correct?

Anyway whether you have time to answer this or not, thanks a million.

Danny




JE McGimpsey said:
One way:

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

When Sheet2 is renamed, XL will change the displayed sheet name in the
formula.


DannyJ said:
I tried this
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename"
,A1),1))

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

and though both return the sheet name, it is the sheet where the formula is.
Can you tell me how I get it to return other sheets?
Click to expand...
Click to expand...
 
The cell("filename" function returns a string of the path to a saved
workbook, the mid part parse out the sheet name itself by finding the ]
(then we use +1 since we don't want to include the bracket itself, find
counts from the left and tells MID where to start) which is what separates
the sheet name from the rest of the string (the sheet name is at the end,
the 255 (number of characters) is just a large number but and since a sheet
name only can have 32 characters it is more than enough

--
Regards,

Peo Sjoblom


DannyJ said:
Thanks a bundle, that worked beautifully. If you have the time would you
explain to me how it works?

I looked up cell filename in help and it says that it returns the
filepath.
But surely the filepath and sheet name are different so I am a bit
confused.
However assuming that the sheet forms part of the file structure the Find
"]" bit means return the text up to this point, is that correct?

I looked up the Mid bit and that seems straightforward enough: It looks as
though it returns a text string (presuambly the sheet name) starting at
letter 1 and going on to letter 255 (wow long name) is this correct?

Anyway whether you have time to answer this or not, thanks a million.

Danny




JE McGimpsey said:
One way:

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

When Sheet2 is renamed, XL will change the displayed sheet name in the
formula.


DannyJ said:
I tried this
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename"
,A1),1))

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

and though both return the sheet name, it is the sheet where the
formula is.
Can you tell me how I get it to return other sheets?
Click to expand...
Click to expand...
Click to expand...
 
Thanks Peo you are a star!

Danny

Peo Sjoblom said:
The cell("filename" function returns a string of the path to a saved
workbook, the mid part parse out the sheet name itself by finding the ]
(then we use +1 since we don't want to include the bracket itself, find
counts from the left and tells MID where to start) which is what separates
the sheet name from the rest of the string (the sheet name is at the end,
the 255 (number of characters) is just a large number but and since a sheet
name only can have 32 characters it is more than enough

--
Regards,

Peo Sjoblom


DannyJ said:
Thanks a bundle, that worked beautifully. If you have the time would you
explain to me how it works?

I looked up cell filename in help and it says that it returns the
filepath.
But surely the filepath and sheet name are different so I am a bit
confused.
However assuming that the sheet forms part of the file structure the Find
"]" bit means return the text up to this point, is that correct?

I looked up the Mid bit and that seems straightforward enough: It looks as
though it returns a text string (presuambly the sheet name) starting at
letter 1 and going on to letter 255 (wow long name) is this correct?

Anyway whether you have time to answer this or not, thanks a million.

Danny




JE McGimpsey said:
One way:

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

When Sheet2 is renamed, XL will change the displayed sheet name in the
formula.


I tried this
Click to expand...
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename"
,A1),1))

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

and though both return the sheet name, it is the sheet where the
formula is.
Can you tell me how I get it to return other sheets?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top