Reference worksheet tab in cell, vice-versa

[JACK]

Can you reference the name of the worksheet in a cell? Can
you do the reverse?

Thanks!

 

[Peo Sjoblom]

You cannot reverse it but you can get the name of the sheet

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

workbook has to be saved first..

 

[Gord Dibben]

Jack

You can reverse if you use code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
On Error Resume Next
ActiveSheet.Name = Range("A1").Text
On Error GoTo 0
End If
End Sub

Copy/paste to the worksheet module.

Right-click on sheet tab and "View Code". Paste code in there.

There may be a couple of extra lines visible when you open up "View Code".
Delete those.

Gord Dibben XL2002

 

[JACK]

This is great... one thing: How can I have this work for
any new / all worksheets (rather than pasting the code into
each new one, or copying one that works)?

Thanks again!

-----Original Message-----
Jack

You can reverse if you use code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
On Error Resume Next
ActiveSheet.Name = Range("A1").Text
On Error GoTo 0
End If
End Sub

Copy/paste to the worksheet module.

Right-click on sheet tab and "View Code". Paste code in there.

There may be a couple of extra lines visible when you open up "View Code".
Delete those.

Gord Dibben XL2002

 

[JACK]

Beautiful! Any chance you could briefly explain each piece
of the formula ?????

Thanks again.

-----Original Message-----
You cannot reverse it but you can get the name of the sheet

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

2)

workbook has to be saved first..

--

Regards,

Peo Sjoblom

Can you reference the name of the worksheet in a cell? Can
you do the reverse?

Thanks!

.

 

[Peo Sjoblom]

Sure,

=CELL("filename",A1)

returns a text string with path, file and active sheet name, e.g.

C:\Documents and Settings\PSjoblom\Desktop\[test2.xls]Sheet1

mid is a text formula that can be used to extract text string from other
text strings

=mid(string,extract_from_this_number,number_of_characters_to_extract)

or for example

=MID(A1,5,10)

will extract from A1 the fifth character and 10 characters long

Now find("]",string) will return the position of the bracket "]", since we
don't want that to be included
we add +1 so it will start with in this case the s in sheet1, since a sheet
name cannot have more than 32 characters
I use 32 characters long.

so

=MID(C:\Documents and
Settings\PSjoblom\Desktop\[test2.xls]Sheet1,FIND("]",C:\Documents and
Settings\PSjoblom\Desktop\[test2.xls]Sheet1)+1,32)

find the bracket, go one step to the right and return the characters to the
right of the bracket..

HTH

--

Regards,

Peo Sjoblom

Beautiful! Any chance you could briefly explain each piece
of the formula ?????

Thanks again.

-----Original Message-----
You cannot reverse it but you can get the name of the sheet

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

2)

workbook has to be saved first..

--

Regards,

Peo Sjoblom

Can you reference the name of the worksheet in a cell? Can
you do the reverse?

Thanks!

.

 

[JACK]

You're the best, thanks!

-----Original Message-----
Sure,

=CELL("filename",A1)

returns a text string with path, file and active sheet name, e.g.

C:\Documents and Settings\PSjoblom\Desktop\[test2.xls]Sheet1

mid is a text formula that can be used to extract text string from other
text strings

=mid(string,extract_from_this_number,number_of_characters_ to_extract)

or for example

=MID(A1,5,10)

will extract from A1 the fifth character and 10 characters long

Now find("]",string) will return the position of the bracket "]", since we
don't want that to be included
we add +1 so it will start with in this case the s in sheet1, since a sheet
name cannot have more than 32 characters
I use 32 characters long.

so

=MID(C:\Documents and
Settings\PSjoblom\Desktop\[test2.xls]Sheet1,FIND("]",C:\Do cuments and
Settings\PSjoblom\Desktop\[test2.xls]Sheet1)+1,32)

find the bracket, go one step to the right and return the characters to the
right of the bracket..

HTH

--

Regards,

Peo Sjoblom

Beautiful! Any chance you could briefly explain each piece
of the formula ?????

Thanks again.

-----Original Message-----
You cannot reverse it but you can get the name of the sheet

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

2)

workbook has to be saved first..

--

Regards,

Peo Sjoblom

Can you reference the name of the worksheet in a

cell?
Can

you do the reverse?

Thanks!


.

.

 

[JACK]

Sorry to be a pest, but would this same approach help a guy
find the location of an external link?

-----Original Message-----
Sure,

=CELL("filename",A1)

returns a text string with path, file and active sheet name, e.g.

C:\Documents and Settings\PSjoblom\Desktop\[test2.xls]Sheet1

mid is a text formula that can be used to extract text string from other
text strings

=mid(string,extract_from_this_number,number_of_characters_ to_extract)

or for example

=MID(A1,5,10)

will extract from A1 the fifth character and 10 characters long

Now find("]",string) will return the position of the bracket "]", since we
don't want that to be included
we add +1 so it will start with in this case the s in sheet1, since a sheet
name cannot have more than 32 characters
I use 32 characters long.

so

=MID(C:\Documents and
Settings\PSjoblom\Desktop\[test2.xls]Sheet1,FIND("]",C:\Do cuments and
Settings\PSjoblom\Desktop\[test2.xls]Sheet1)+1,32)

find the bracket, go one step to the right and return the characters to the
right of the bracket..

HTH

--

Regards,

Peo Sjoblom

Beautiful! Any chance you could briefly explain each piece
of the formula ?????

Thanks again.

-----Original Message-----
You cannot reverse it but you can get the name of the sheet

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

2)

workbook has to be saved first..

--

Regards,

Peo Sjoblom

Can you reference the name of the worksheet in a

cell?
Can

you do the reverse?

Thanks!


.

.

 

[Peo Sjoblom]

I am not sure I understand what you mean? Could you give an example?

--

Regards,

Peo Sjoblom

Sorry to be a pest, but would this same approach help a guy
find the location of an external link?

-----Original Message-----
Sure,

=CELL("filename",A1)

returns a text string with path, file and active sheet name, e.g.

C:\Documents and Settings\PSjoblom\Desktop\[test2.xls]Sheet1

mid is a text formula that can be used to extract text string from other
text strings

=mid(string,extract_from_this_number,number_of_characters_ to_extract)

or for example

=MID(A1,5,10)

will extract from A1 the fifth character and 10 characters long

Now find("]",string) will return the position of the bracket "]", since we
don't want that to be included
we add +1 so it will start with in this case the s in sheet1, since a sheet
name cannot have more than 32 characters
I use 32 characters long.

so

=MID(C:\Documents and
Settings\PSjoblom\Desktop\[test2.xls]Sheet1,FIND("]",C:\Do cuments and
Settings\PSjoblom\Desktop\[test2.xls]Sheet1)+1,32)

find the bracket, go one step to the right and return the characters to the
right of the bracket..

HTH

--

Regards,

Peo Sjoblom

Beautiful! Any chance you could briefly explain each piece
of the formula ?????

Thanks again.



-----Original Message-----
You cannot reverse it but you can get the name of the
sheet

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

workbook has to be saved first..

--

Regards,

Peo Sjoblom

message
Can you reference the name of the worksheet in a cell?
Can
you do the reverse?

Thanks!


.

.

 

[JACK]

Sure, say I have a workbook that has an external link
somewhere, but for the life of me I can find it by manually
searching each cell. I guess it is really a "Find" issue,
perhaps looking for "]"?

-----Original Message-----
I am not sure I understand what you mean? Could you give an example?

--

Regards,

Peo Sjoblom

Sorry to be a pest, but would this same approach help a guy
find the location of an external link?

-----Original Message-----
Sure,

=CELL("filename",A1)

returns a text string with path, file and active sheet name, e.g.

C:\Documents and Settings\PSjoblom\Desktop\[test2.xls]Sheet1

mid is a text formula that can be used to extract text string from other
text strings

=mid(string,extract_from_this_number,number_of_characters_

to_extract)

or for example

=MID(A1,5,10)

will extract from A1 the fifth character and 10

characters
long

Now find("]",string) will return the position of the bracket "]", since we
don't want that to be included
we add +1 so it will start with in this case the s in sheet1, since a sheet
name cannot have more than 32 characters
I use 32 characters long.

so

=MID(C:\Documents and

Settings\PSjoblom\Desktop\[test2.xls]Sheet1,FIND("]",C:\Do
cuments and

Settings\PSjoblom\Desktop\[test2.xls]Sheet1)+1,32)

find the bracket, go one step to the right and return

the
characters to the

right of the bracket..

HTH

--

Regards,

Peo Sjoblom


Beautiful! Any chance you could briefly explain each piece
of the formula ?????

Thanks again.



-----Original Message-----
You cannot reverse it but you can get the name of the
sheet

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

2)

workbook has to be saved first..

--

Regards,

Peo Sjoblom

message
Can you reference the name of the worksheet in a cell?
Can
you do the reverse?

Thanks!


.



.

.

 

[Peo Sjoblom]

If you have a link to a closed workbook or any link at all then you can just
do ctrl + F and look for "]"
--

Regards,

Peo Sjoblom

Sure, say I have a workbook that has an external link
somewhere, but for the life of me I can find it by manually
searching each cell. I guess it is really a "Find" issue,
perhaps looking for "]"?

-----Original Message-----
I am not sure I understand what you mean? Could you give an example?

--

Regards,

Peo Sjoblom

Sorry to be a pest, but would this same approach help a guy
find the location of an external link?
-----Original Message-----
Sure,

=CELL("filename",A1)

returns a text string with path, file and active sheet
name, e.g.

C:\Documents and
Settings\PSjoblom\Desktop\[test2.xls]Sheet1

mid is a text formula that can be used to extract text
string from other
text strings

=mid(string,extract_from_this_number,number_of_characters_
to_extract)

or for example

=MID(A1,5,10)

will extract from A1 the fifth character and 10 characters
long

Now find("]",string) will return the position of the
bracket "]", since we
don't want that to be included
we add +1 so it will start with in this case the s in
sheet1, since a sheet
name cannot have more than 32 characters
I use 32 characters long.

so

=MID(C:\Documents and
Settings\PSjoblom\Desktop\[test2.xls]Sheet1,FIND("]",C:\Do
cuments and
Settings\PSjoblom\Desktop\[test2.xls]Sheet1)+1,32)

find the bracket, go one step to the right and return the
characters to the
right of the bracket..

HTH

--

Regards,

Peo Sjoblom


message
Beautiful! Any chance you could briefly explain each
piece
of the formula ?????

Thanks again.



-----Original Message-----
You cannot reverse it but you can get the name of the
sheet


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

workbook has to be saved first..

--

Regards,

Peo Sjoblom

message
Can you reference the name of the worksheet in a
cell?
Can
you do the reverse?

Thanks!


.



.

.

 

[Mithveaen]

You cannot reverse it but you can get the name of the sheet

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

workbook has to be saved first..

--

Regards,

Peo Sjoblom

I need to create a list with all my worksheets names and this has just
saved my life. Thank you!!

Mithveaen.