+ Reply to Thread
Results 1 to 6 of 6

Having trouble using Indirect over multiple sheets

  1. #1
    Registered User
    Join Date
    03-10-2015
    Location
    Florida
    MS-Off Ver
    2010
    Posts
    3

    Having trouble using Indirect over multiple sheets

    I have a workbook with a summary sheet and multiple additional sheets (monthly) which contain numerical data. Each monthly sheet is formatted identically, but had the ability to be sorted. I have created names for each row and column.

    What I want to do is use indirect to reference the sheet name using a dropdown on the summary sheet and pull data from the chosen sheet by referencing the row/column name.

    I have successfully added the dropdown, and used indirect to choose the sheet name.....I am also able to use indirect to pull the row/column amount if I do it on the sheet the data is in, but I have not been able to get my formula to work to both pull the sheet name from the drop down AND the referenced names on the chosen sheet within the summary sheet. I can do it if I hard code cells, but I am wanting to use the names so that it works regardless how the sheet is sorted.

    Any help?

    Thanks

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Having trouble using Indirect over multiple sheets

    Welcome to the forum.

    Can you post a sample workbook (instructions in my signature)?
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    03-10-2015
    Location
    Florida
    MS-Off Ver
    2010
    Posts
    3

    Re: Having trouble using Indirect over multiple sheets

    I reworked a quick template which will show what I did. Hope you can follow.

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Having trouble using Indirect over multiple sheets

    Hmm, I havent ever seen an indirect() indirect() used for an index match. I would change the formula to instead use Indirect([worksheet]&index(array,match(),match()))

    This works:
    Please Login or Register  to view this content.
    I just set an input cell (and named it) for Month, Stream and Product.

  5. #5
    Registered User
    Join Date
    03-10-2015
    Location
    Florida
    MS-Off Ver
    2010
    Posts
    3

    Re: Having trouble using Indirect over multiple sheets

    Thank you.

    I had originally intended to use Index and Match, but when I realized I could assign names and do indirect, I thought that would be easier. Apparently not.

    Thanks

  6. #6
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Having trouble using Indirect over multiple sheets

    Yeah I hadnt ever seen the use of indirect like that, but in all honesty I try to avoid using volatile formulas unless for a very few number of cells like your example. I typically prefer to just build a dataset off of all of your tabs and have a simple index match on THAT dataset.

+ 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] INDIRECT Function over multiple sheets
    By Cdigglesworth in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 01-14-2015, 03:17 PM
  2. [SOLVED] INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH
    By DJDRU in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-07-2014, 08:42 AM
  3. Having Trouble With PivotChart With Multiple Sheets
    By wm009 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-17-2013, 12:36 PM
  4. Using indirect within a countif on multiple sheets
    By slash_gnr3k in forum Excel General
    Replies: 2
    Last Post: 05-03-2012, 04:09 AM
  5. Indirect() between multiple sheets?
    By ctasich in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-30-2007, 10:12 AM

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