+ Reply to Thread
Results 1 to 4 of 4

Dynamic sheet reference?

  1. #1
    Registered User
    Join Date
    10-02-2017
    Location
    New Bedford
    MS-Off Ver
    2007
    Posts
    1

    Question Dynamic sheet reference?

    Hello all! This is my first post, and I'm still a bit of a beginner with Excel, so I thank you ahead of time for your patience.

    I have a workbook that has many sheets, and one sheet has formulas hyperlinking to these sheets.
    However, there is a chance the sheet names may change.

    So my question is, is there a way to have a formula reference a specific sheet even if the name of the sheet changes, without breaking the formula? Like a dynamic reference sort of deal?

    Thanks in advanced for your help!

  2. #2
    Forum Contributor shivya's Avatar
    Join Date
    08-19-2017
    Location
    Delhi, India
    MS-Off Ver
    2013
    Posts
    240

    Re: Dynamic sheet reference?

    Use OFFSET with COUNTA

    this is hint. For proper answer, attach your excel sheet
    Last edited by shivya; 10-02-2017 at 07:40 AM.

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Dynamic sheet reference?

    Hi,

    If a formula directly references cells on a sheet, the formula will update if you change the sheet name. If, however, you are using a HYPERLINK formula and passing a sheet name as a string, it will not update since that part is plain text. What you could do is use the cell function to retrieve the sheet name with a formula like this
    =MID(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1))+1,31)
    which will return "Sheet2" and will update if Sheet2 is renamed.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  4. #4
    Forum Contributor shivya's Avatar
    Join Date
    08-19-2017
    Location
    Delhi, India
    MS-Off Ver
    2013
    Posts
    240

    Re: Dynamic sheet reference?

    Quote Originally Posted by xlnitwit View Post
    Hi,

    If a formula directly references cells on a sheet, the formula will update if you change the sheet name. If, however, you are using a HYPERLINK formula and passing a sheet name as a string, it will not update since that part is plain text. What you could do is use the cell function to retrieve the sheet name with a formula like this
    =MID(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1))+1,31)
    which will return "Sheet2" and will update if Sheet2 is renamed.
    Great.... ..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Dynamic reference to another sheet?
    By Mitch in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-16-2006, 11:40 AM
  2. Dynamic reference to a sheet
    By Biff in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-06-2005, 07:05 AM
  3. Dynamic reference to a sheet
    By Biff in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 06:05 AM
  4. Dynamic reference to a sheet
    By Biff in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 04:05 AM
  5. Dynamic reference to a sheet
    By xisque in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  6. Dynamic reference to a sheet
    By xisque in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  7. [SOLVED] Dynamic reference to a sheet
    By xisque in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-20-2005, 04:05 PM
  8. Dynamic reference to sheet name
    By Darien0104 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-08-2005, 05:08 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1