+ Reply to Thread
Results 1 to 19 of 19

Index Multiple tables on multiple sheets

  1. #1
    Registered User
    Join Date
    06-02-2017
    Location
    Rio Rancho, NM
    MS-Off Ver
    Office 2016
    Posts
    8

    Index Multiple tables on multiple sheets

    Hi, so I have a problem. I have a sheet with Cases that differ by Teams. Each team has has their own Excel Sheet. So when an entry is put into one of the sheets, I want it to show up on the main sheet "SM June". I can do a regular INDEX and Match such as =INDEX(Table9[Case Driver], MATCH([Case Number],Table9[Case Number],0)), but I want to Index two tables and match on that one sheet.
    Last edited by JesterJayJoker; 06-03-2017 at 05:42 PM.

  2. #2
    Registered User
    Join Date
    06-02-2017
    Location
    Rio Rancho, NM
    MS-Off Ver
    Office 2016
    Posts
    8
    Ive attached an example.
    Attached Files Attached Files

  3. #3
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Index Multiple tables on multiple sheets

    If you want to copy from the individual sheets to the SM sheet, why are the ONLY formulae in your sheet copying data FROM the SM sheet TO the individual sheets. Can you please explain EXACTLY what you want?

    Also, if you DO want to copy from individuals to the Master, roughly how many individual sheets can there be?
    Glenn



  4. #4
    Registered User
    Join Date
    06-02-2017
    Location
    Rio Rancho, NM
    MS-Off Ver
    Office 2016
    Posts
    8

    Re: Index Multiple tables on multiple sheets

    There is at least 4 individual sheets. I presumed with someone helping me with the two I could figure out how to transition it into a formula I needed. So what happens is I pull a report called "Open Cases". When I do that I copy and paste the open cases onto the June SM sheet. From there it filters the Teams to their respective Sheets. After that, I send the file to four other Team Leads and they fill out the Case Driver and Case Summary on their sheet. From there it will copy to their cases. Then they will send the sheet back to me with those filled out. I was given this

    =IF([@[Team Leader]]="Team MB",INDEX(Table5[Case Driver], MATCH([Case Number],Table5[Case Number],0)),INDEX(Table9[Case Driver], MATCH([Case Number],Table9[Case Number],0)))
    however, when I add the other sheets that won't work. Is there anyway to make more than one IF statement?
    Last edited by AliGW; 06-04-2017 at 12:01 PM. Reason: Unnecessary quotation removed.

  5. #5
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Index Multiple tables on multiple sheets

    In Team L, A4, an array formula, copied across and down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    For Team MB, the only change needed is to change the red L to MB
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-02-2017
    Location
    Rio Rancho, NM
    MS-Off Ver
    Office 2016
    Posts
    8

    Re: Index Multiple tables on multiple sheets

    Thank you. However, I want to insert the formula into "SM June" Case Driver and Case Summary so that when it is filled out in Team L or Team MB sheet, it shows up there.

  7. #7
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Index Multiple tables on multiple sheets

    Now I am confused. I which direction are which columns yo be copied? Remember, you know what you want better than anyone. Unless your explanation is clear, I am left guessing.

  8. #8
    Registered User
    Join Date
    06-02-2017
    Location
    Rio Rancho, NM
    MS-Off Ver
    Office 2016
    Posts
    8

    Re: Index Multiple tables on multiple sheets

    So I edited the Testing (2) to show you better. Sorry, I'm horrible with explaining things. Thank you again for your patience.
    Attached Files Attached Files
    Last edited by AliGW; 06-04-2017 at 12:02 PM. Reason: Unnecessary quotation removed.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,998

    Re: Index Multiple tables on multiple sheets

    JesterJayJoker - please don't quote whole posts - it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

    For normal conversational replies, try using the QUICK REPLY box below.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  10. #10
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Index Multiple tables on multiple sheets

    OK. So. To be clear (as you didn't really answer my Q...)

    All info except last 2 columns go FROM SM to individuals.
    Last 2 columns go FROM individuals to MASTER?

    Yes? No?

  11. #11
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Index Multiple tables on multiple sheets

    No answer. So, I am assuming that my guess at post 10 is correct.

    In SM, cell G4, copied across and then down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    06-02-2017
    Location
    Rio Rancho, NM
    MS-Off Ver
    Office 2016
    Posts
    8

    Re: Index Multiple tables on multiple sheets

    Yes you're correct! Thank you so much! That's exactly what I was looking for. Sorry for the late response. Sunday is a busy day for me.

  13. #13
    Registered User
    Join Date
    06-02-2017
    Location
    Rio Rancho, NM
    MS-Off Ver
    Office 2016
    Posts
    8

    Re: Index Multiple tables on multiple sheets

    Just so I can learn this, this part, Table6[@[Case Number]:[Case Number]], are you matching across multiple sheets?

  14. #14
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Index Multiple tables on multiple sheets

    =IFERROR(IFERROR(

    INDEX(Table9[Case Driver],MATCH(Table6[@[Case Number]:[Case Number]],Table9[[Case Number]:[Case Number]],0))&"",

    INDEX(Table5[Case Driver],MATCH(Table6[@[Case Number]:[Case Number]],Table5[[Case Number]:[Case Number]],0)))&"","")

    Red: return the information in Case Driver for Team L
    Orange: for the case number listed in SM
    Green: in the Case Number column for team L
    Cyan: if ERROR (i.e. no match)
    Dark blue: do the same again, only in Table5 (Team MB).

    You'd find it easier to follow if your renamed your tables - e.g. Team_L and Team_MB, etc.

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  15. #15
    Registered User
    Join Date
    06-02-2017
    Location
    Rio Rancho, NM
    MS-Off Ver
    Office 2016
    Posts
    8

    Re: Index Multiple tables on multiple sheets

    Thank you so much!! I learned from this post. :D Repped and Solved!

  16. #16
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Index Multiple tables on multiple sheets

    You're welcome. For next time: just remember to treat us like intelligent 6 year olds. Explain slowly and clearly....

  17. #17
    Registered User
    Join Date
    06-08-2020
    Location
    Kampala
    MS-Off Ver
    2019
    Posts
    6

    Re: Index Multiple tables on multiple sheets

    Hello, sorry i didnt deem it necessary to create an entirely new thread for my case..
    It is related to his,.. i want to index two tables from 2 different sheets, say for instance SM June as sheet1 and Team L as sheet 2. i want to index the entire tables as they are, with a drop down list controlling which tables i view. Say for instance if i name the table on SM June as Table1 and the One on Team L as Table 2, i have already created sheet 3 with headers (Case Number Media Source Status etc...), and a drop down selection, how do i link the drop down selection to the two tables, so that when i select table1 from the drop down, it returns all the contents, but while in sheet 3?
    Thankyou, and sorry for the tautology.

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,968

    Re: Index Multiple tables on multiple sheets

    Quote Originally Posted by anxious View Post
    Hello, sorry i didnt deem it necessary to create an entirely new thread for my case..
    It is related to his,.. i want to index two tables from 2 different sheets, say for instance SM June as sheet1 and Team L as sheet 2. i want to index the entire tables as they are, with a drop down list controlling which tables i view. Say for instance if i name the table on SM June as Table1 and the One on Team L as Table 2, i have already created sheet 3 with headers (Case Number Media Source Status etc...), and a drop down selection, how do i link the drop down selection to the two tables, so that when i select table1 from the drop down, it returns all the contents, but while in sheet 3?
    Thankyou, and sorry for the tautology.
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    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

  19. #19
    Registered User
    Join Date
    06-08-2020
    Location
    Kampala
    MS-Off Ver
    2019
    Posts
    6

    Re: Index Multiple tables on multiple sheets

    Ok noted, thanks for the clarification. I hope to stay in line

+ 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. Pivot multiple tables located on multiple sheets
    By JudyF in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-30-2017, 12:11 PM
  2. Replies: 1
    Last Post: 08-23-2016, 02:44 PM
  3. Two Criteria Index Match on Multiple Sheets with Multiple Values
    By MegganM in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-21-2016, 09:51 PM
  4. Copy Paste multiple tables from multiple sheets into Outlook email
    By DoodlesMama in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-25-2015, 12:21 PM
  5. Replies: 5
    Last Post: 02-06-2015, 08:44 PM
  6. [SOLVED] Two criteria Index Match on multiple sheets returning multiple values
    By Joak in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2014, 10:03 AM
  7. Vlookup (or index/match) with multiple criteria over multiple sheets
    By Groovicles in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2013, 01:56 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