Tags: cell, excel, microsoft, msdn, reference, reverse, software, tab, vice-versa, worksheet

Reference worksheet tab in cell, vice-versa

On Microsoft » Microsoft Excel

22,408 words with 10 Comments; publish: Fri, 23 May 2008 20:03:00 GMT; (3063,156.25, « »)

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

you do the reverse?

Thanks!

All Comments

Leave a comment...

  • 10 Comments
    • 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

      "JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in message

      news:057401c3a3fe$0c979ff0$a401280a.excel.questionfor.info.phx.gbl...

      > Can you reference the name of the worksheet in a cell? Can

      > you do the reverse?

      > Thanks!

      #1; Fri, 23 May 2008 20:04:00 GMT
    • 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

      On Wed, 5 Nov 2003 20:55:25 -0500, "Peo Sjoblom" <terre08.excel.questionfor.info.mvps.org> wrote:

      >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in message

      >news:057401c3a3fe$0c979ff0$a401280a.excel.questionfor.info.phx.gbl...

      >> Can you reference the name of the worksheet in a cell? Can

      >> you do the reverse?

      >> Thanks!

      #2; Fri, 23 May 2008 20:05:00 GMT
    • 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

      >On Wed, 5 Nov 2003 20:55:25 -0500, "Peo Sjoblom"

      <terre08.excel.questionfor.info.mvps.org> wrote:

      >>"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in

      message

      >>news:057401c3a3fe$0c979ff0$a401280a.excel.questionfor.info.phx.gbl...

      >> Can you reference the name of the worksheet in a cell?

      Can

      >> you do the reverse?

      >> Thanks!

      >.

      >

      #3; Fri, 23 May 2008 20:06:00 GMT
    • 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

      >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in

      message

      >news:057401c3a3fe$0c979ff0$a401280a.excel.questionfor.info.phx.gbl...

      >> Can you reference the name of the worksheet in a cell?

      Can

      >> you do the reverse?

      >> Thanks!

      >

      >.

      >

      #4; Fri, 23 May 2008 20:08:00 GMT
    • 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

      "JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in message

      news:0d7a01c3a46a$87eb4fa0$a101280a.excel.questionfor.info.phx.gbl...

      > 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

      > >

      > >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in

      > message

      > >news:057401c3a3fe$0c979ff0$a401280a.excel.questionfor.info.phx.gbl...

      > >> Can you reference the name of the worksheet in a cell?

      > Can

      > >> you do the reverse?

      > >>

      > >> Thanks!

      > >

      > >

      > >.

      > >

      #5; Fri, 23 May 2008 20:08:00 GMT
    • 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

      >

      >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in

      message

      >news:0d7a01c3a46a$87eb4fa0$a101280a.excel.questionfor.info.phx.gbl...

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

      >> >

      >> >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in

      >> message

      >> >news:057401c3a3fe$0c979ff0$a401280a.excel.questionfor.info.phx.gbl...

      >> >> Can you reference the name of the worksheet in a

      cell?

      >> Can

      >> >> you do the reverse?

      >> >>

      >> >> Thanks!

      >> >

      >> >

      >> >.

      >> >

      >

      >.

      >

      #6; Fri, 23 May 2008 20:10:00 GMT
    • 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

      >

      >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in

      message

      >news:0d7a01c3a46a$87eb4fa0$a101280a.excel.questionfor.info.phx.gbl...

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

      >> >

      >> >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in

      >> message

      >> >news:057401c3a3fe$0c979ff0$a401280a.excel.questionfor.info.phx.gbl...

      >> >> Can you reference the name of the worksheet in a

      cell?

      >> Can

      >> >> you do the reverse?

      >> >>

      >> >> Thanks!

      >> >

      >> >

      >> >.

      >> >

      >

      >.

      >

      #7; Fri, 23 May 2008 20:10:00 GMT
    • I am not sure I understand what you mean? Could you give an example?

      --

      Regards,

      Peo Sjoblom

      "JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in message

      news:0be801c3a472$ee6ddbf0$a401280a.excel.questionfor.info.phx.gbl...

      > 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

      > >

      > >

      > >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in

      > message

      > >news:0d7a01c3a46a$87eb4fa0$a101280a.excel.questionfor.info.phx.gbl...

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

      > >> >

      > >> >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in

      > >> message

      > >> >news:057401c3a3fe$0c979ff0$a401280a.excel.questionfor.info.phx.gbl...

      > >> >> Can you reference the name of the worksheet in a

      > cell?

      > >> Can

      > >> >> you do the reverse?

      > >> >>

      > >> >> Thanks!

      > >> >

      > >> >

      > >> >.

      > >> >

      > >

      > >

      > >.

      > >

      #8; Fri, 23 May 2008 20:12:00 GMT
    • 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

      >

      >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in

      message

      >news:0be801c3a472$ee6ddbf0$a401280a.excel.questionfor.info.phx.gbl...

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

      >> >

      >> >

      >> >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in

      >> message

      >> >news:0d7a01c3a46a$87eb4fa0$a101280a.excel.questionfor.info.phx.gbl...

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

      >> >> >

      >> >> >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote

      in

      >> >> message

      >> >> >news:057401c3a3fe$0c979ff0$a401280a.excel.questionfor.info.phx.gbl...

      >> >> >> Can you reference the name of the worksheet in a

      >> cell?

      >> >> Can

      >> >> >> you do the reverse?

      >> >> >>

      >> >> >> Thanks!

      >> >> >

      >> >> >

      >> >> >.

      >> >> >

      >> >

      >> >

      >> >.

      >> >

      >

      >.

      >

      #9; Fri, 23 May 2008 20:13:00 GMT
    • 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

      "JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in message

      news:0d3601c3a480$c9c9a370$a401280a.excel.questionfor.info.phx.gbl...

      > 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

      > >

      > >

      > >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in

      > message

      > >news:0be801c3a472$ee6ddbf0$a401280a.excel.questionfor.info.phx.gbl...

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

      > >> >

      > >> >

      > >> >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote in

      > >> message

      > >> >news:0d7a01c3a46a$87eb4fa0$a101280a.excel.questionfor.info.phx.gbl...

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

      > >> >> >

      > >> >> >"JACK" <anonymous.excel.questionfor.info.discussions.microsoft.com> wrote

      > in

      > >> >> message

      > >> >> >news:057401c3a3fe$0c979ff0$a401280a.excel.questionfor.info.phx.gbl...

      > >> >> >> Can you reference the name of the worksheet in a

      > >> cell?

      > >> >> Can

      > >> >> >> you do the reverse?

      > >> >> >>

      > >> >> >> Thanks!

      > >> >> >

      > >> >> >

      > >> >> >.

      > >> >> >

      > >> >

      > >> >

      > >> >.

      > >> >

      > >

      > >

      > >.

      > >

      #10; Fri, 23 May 2008 20:14:00 GMT