+ Reply to Thread
Results 1 to 5 of 5

How to reference a dynamic tab in excel (maybe indirect, but not sure)

  1. #1
    Registered User
    Join Date
    07-03-2018
    Location
    amsterdam
    MS-Off Ver
    excel 2016
    Posts
    8

    How to reference a dynamic tab in excel (maybe indirect, but not sure)

    Hi Everyone,

    I have a question regarding making a template sheet in excel with formulas, with this template sheet being able to move to different workbooks and still working as in the first workbook. So, it is referencing a dynamic tab instead of a fixed tab. For example, I have a workbook titled Region 1. In this workbook I have the 'data' sheet from which the template sheet, which i have named 'data cleaned' has to extract the data. I have used different formulas to do this, some are IF formulas, such as:

    =IF(Data!AL2="";"NA";Data!AL2)

    and

    =IF(Data!AM2="No";"0";IF(Data!AM2="";"NA";Data!AN2))

    or formulas using index (in another sheet titled 'HF2', referencing this cleaned data), such as:

    =INDEX('Data cleaned'!$U$4:$RN$4;1+11*(ROWS(C$15:C15)-1))&""

    However, these formulas specifically refer to the 'data' sheet in the workbook Region 1. If I copy my 'data cleaned' sheet into for example workbook Region 2 that also contains a 'data' sheet, the template sheet will still refer to the data in the 'data' sheet from workbook Region 1 and not to the sheet in Region 2, which I want it to do. I have been looking into the INDIRECT function, but can't figure out how to combine INDIRECT with the above formulas (if that is even possible).

    Does anyone know how to reference a dynamic tab in the above formulas?

    Thank you

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to reference a dynamic tab in excel (maybe indirect, but not sure)

    If I understand your requirement I think it's just a question of changing the Links.

    i.e. Data...Edit Links and then Change Source and pick the Region 2 workbook

  3. #3
    Registered User
    Join Date
    07-03-2018
    Location
    amsterdam
    MS-Off Ver
    excel 2016
    Posts
    8

    Re: How to reference a dynamic tab in excel (maybe indirect, but not sure)

    Hi Richard,

    That does work, thanks! The only thing is that excel crashes when trying to update the fields, but oh well.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to reference a dynamic tab in excel (maybe indirect, but not sure)

    After creating the Region 2 workbook, are you perhaps deleting stuff from Region 1 before changing the link source in Region2.

    I

  5. #5
    Registered User
    Join Date
    07-03-2018
    Location
    amsterdam
    MS-Off Ver
    excel 2016
    Posts
    8

    Re: How to reference a dynamic tab in excel (maybe indirect, but not sure)

    Hi Richard, I think it was just my excel being overworked. But I got it to work now. Thanks again!

+ 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] INDEX MATCH with dynamic reference using INDIRECT
    By Avaritia in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-11-2019, 11:09 PM
  2. Using INDIRECT formula to reference a dynamic range on another sheet
    By Travisty in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-01-2018, 12:22 PM
  3. Replies: 1
    Last Post: 12-18-2017, 06:46 AM
  4. [SOLVED] Use INDIRECT to build a dynamic reference is causing #REF error
    By CWatsonJr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-27-2017, 02:37 PM
  5. VBA Dynamic Range - An Indirect Cell Reference
    By ExcelHelp2013 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2013, 11:08 AM
  6. [SOLVED] Dynamic VLOOKUP wit INDIRECT reference
    By supern0va in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-29-2012, 11:44 AM
  7. [SOLVED] How do I use an indirect reference for a chart scale in Excel?
    By Unknownprofessor in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-16-2005, 09: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