+ Reply to Thread
Results 1 to 3 of 3

How to refer to sheet in a formula by using that sheets name in a cell

  1. #1
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273

    How to refer to sheet in a formula by using that sheets name in a cell

    Hello all,

    How can I refer to a sheet in a formula without using the sheets name directly in the formula, but by having the sheets name in a cell. For example:
    Say I have 2 sheets, Sheet1 and Sheet2

    In Sheet1 in a cell, I could put this: =Sheet2!B4. That will return the value in B4 of sheet2
    I want to do the same thing, but without actually using the name of the sheet. I'll have the name of the sheet in a cell instead. So if I have the text: "Sheet2" in cell A1 of Sheet1, I want to have the formula read like this instead:
    =A1!B4 because A1's value is Sheet2. I know there's a way to do it, but I can't remember what it is.

    This formula: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
    is not what I'm looking for. That returns the name of the sheet the formula is in.

    Thank you!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to refer to sheet in a formula by using that sheets name in a cell

    You want the INDIRECT function, which you can use like this:

    =INDIRECT("'"&Sheet1!$A$1&"'!B4")

    Note that as the cell address (B4) is within quotes it is treated as a text value, so will not change if you were to copy the formula across or down. If you want to do that, then it would probably be easier to use R1C1 notation, and there is an option to do that with INDIRECT.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273

    Re: How to refer to sheet in a formula by using that sheets name in a cell

    Pete

    That's works excellent! Thank you!

+ 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. Summary sheet to refer to cell on multiple sheets (always same column, but row# can vary)
    By carlito2002wgn in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-12-2019, 10:04 PM
  2. Refer to correct sheet via other cell in formula
    By ferodo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-25-2016, 11:50 AM
  3. Refer to Sheets in Formula without INDERECT
    By gerarddevries in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-04-2015, 05:41 AM
  4. Refer to Sheets in formula VBA
    By gerarddevries in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-04-2015, 05:32 AM
  5. [SOLVED] How to refer to more than one sheet with With Sheets
    By mario274 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-03-2012, 12:03 PM
  6. [SOLVED] Drag Formula to every other cell but refer to consecutive columns in another sheet
    By mariejulia in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-02-2012, 06:44 AM
  7. Replies: 2
    Last Post: 05-05-2011, 04:59 PM

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