+ Reply to Thread
Results 1 to 8 of 8

Alternative to INDIRECT for dynamic worksheet references

  1. #1
    Registered User
    Join Date
    12-18-2014
    Location
    California
    MS-Off Ver
    Mac 2011
    Posts
    12

    Alternative to INDIRECT for dynamic worksheet references

    I am trying to dynamically reference different worksheet names using the INDIRECT function, like this:

    Column A: contains target sheet names
    Column B: =INDEX(INDIRECT(A1&"!A:B"),MATCH("Data",INDIRECT(A1&"!B:B"),0),1)
    Column A of target sheet: contains values I want formula to return
    Column B of target sheet: contains the word "Data", used to indicate which row to pull data from

    I would like to replace INDIRECT with a non-volatile function if possible. I have read that the CHOOSE function might be able to do this, but I'm unsure how to apply it to my scenario. Also note that my actual workbook contains 40+ sheets (and I need the flexibility to add or change sheet names easily) so the =CHOOSE(index_num,value1,value2,...) format doesn't really seem feasible.
    Last edited by Joe26; 05-22-2019 at 11:00 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,430

    Re: Alternative to INDIRECT for dynamic worksheet references

    One alternative would be to use VBA to detect a change to A1 and then rewrite the formula for you. So if you change A1 to "Sheet1" VBA would write:

    Please Login or Register  to view this content.
    I could write the code for your specific file if you share it here. You would only have to include the sheet with the formula above, and one sample sheet (not 40).

    The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,906

    Re: Alternative to INDIRECT for dynamic worksheet references

    Other than VBA - as suggested by Jeff - INDIRECT is teh only function I know of that will allow you to reference another cell and use that as part of a reference in a formula.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    12-18-2014
    Location
    California
    MS-Off Ver
    Mac 2011
    Posts
    12

    Re: Alternative to INDIRECT for dynamic worksheet references

    6SrtingJazzer,

    Thanks for the reply. I'm not sure we're on the same page. My formula needs to pasted down column B, with multiple sheet names listed down column A (not just A1). Would your VBA code do that? I've attached an example workbook.

    Thanks!
    Attached Files Attached Files
    Last edited by Joe26; 05-23-2019 at 12:18 AM.

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,430

    Re: Alternative to INDIRECT for dynamic worksheet references

    VBA can definitely do that, I'll have something to you a little later this morning (ET) if someone doesn't beat me to it.

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,430

    Re: Alternative to INDIRECT for dynamic worksheet references

    When you make a change in column A, a formula will be inserted/updated in that row of column B.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-18-2014
    Location
    California
    MS-Off Ver
    Mac 2011
    Posts
    12

    Re: Alternative to INDIRECT for dynamic worksheet references

    Wow, this is like magic!

    Thanks for the help.

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,430

    Re: Alternative to INDIRECT for dynamic worksheet references

    Glad it helped! If you really want to get fancy, I think it could automatically add a row if you add a new sheet. I haven't tried it.

+ 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. Dynamic References Based on Two Criteria (INDIRECT OR VLOOKUP?)
    By hotelguy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-08-2015, 09:52 PM
  2. [SOLVED] Using Indirect() to get dynamic references in an array formula
    By jlb333333 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-18-2015, 10:37 AM
  3. [SOLVED] SUMPRODUCT(SUMIF(INDIRECT with Dynamic Worksheet Range
    By decipher in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2014, 05:40 AM
  4. [VBA] Indirect copy to dynamic worksheet
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2014, 12:15 AM
  5. Replies: 1
    Last Post: 02-18-2012, 10:31 AM
  6. [SOLVED] Dynamic worksheet references
    By David Boothe in forum Excel General
    Replies: 3
    Last Post: 05-28-2006, 07:15 AM
  7. Dynamic formulas including worksheet references
    By lars22222 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-06-2005, 06:05 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