+ Reply to Thread
Results 1 to 3 of 3

Trouble Using an Indirect within an Indirect

  1. #1
    Registered User
    Join Date
    06-14-2013
    Location
    Orange, Ca
    MS-Off Ver
    Excel 2011
    Posts
    7

    Trouble Using an Indirect within an Indirect

    Question: I am having trouble referencing an indirect within an indirect.

    Background:
    I have additional Sheets (tabs) that will be modified on the fly and i need to be able to change 1 cell's value and have it update all cells referencing that tabs name or be able to add a new tab and have it now reference that new tabs name.

    I have the following formula correctly working in order to reference a cell with a dynamic sheet/tab name.

    =INDIRECT("'"&b5&"'!F141")

    In addition i need to be able to consistenly grab the last row in a column on the fly for the newly added sheets/tabs. When the new sheets are added they may have different length columns. The formula i have to grab the last row in a sheet is:

    =INDIRECT("'Sheet4'!F"&COUNTA('Sheet4'!F:F))

    Problem: I need to be able to dynamically reference the sheet/tab at the same time I am dynamically referencing the last row in the new sheet/tab. Basically I need to combine my first two solutions together.

    Failed Solutions: i have the following attempted combination that is not working.
    =INDIRECT("INDIRECT("'"&b5&"'!b2")!F"&COUNTA(INDIRECT("'"&b5&"'!b2")!F:F))

    Any thoughts on combining the two would be much appreciated.

    Screen Shot 2016-10-23 at 4.00.08 PM.png

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Trouble Using an Indirect within an Indirect

    Hi,
    Assuming you have
    B2 = F
    B5 = Sheet4

    Try this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Registered User
    Join Date
    06-14-2013
    Location
    Orange, Ca
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: Trouble Using an Indirect within an Indirect

    Your amazing! thank you so much! it worked perfectly.

+ 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] Having trouble using Indirect over multiple sheets
    By shall176 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-10-2015, 02:49 PM
  2. Trouble with formula Indirect Match
    By KellyAustralia in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-07-2014, 10:51 PM
  3. Trouble with INDIRECT formula
    By stblu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-16-2013, 06:12 AM
  4. Trouble referencing another sheet with INDIRECT function
    By No Surprises in forum Excel General
    Replies: 2
    Last Post: 05-24-2012, 01:07 AM
  5. Trouble joining a formula to be direct and indirect
    By curtwphillips in forum Excel General
    Replies: 1
    Last Post: 04-27-2011, 08:27 PM
  6. indirect trouble
    By marttali in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2008, 07:33 AM
  7. Trouble with Average & Indirect Function
    By CENorth7272 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-02-2005, 10:36 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