Consulting

Results 1 to 3 of 3

Thread: Index/Match over multiple tabs

  1. #1
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    2
    Location

    Index/Match over multiple tabs

    Currently I am working on a document in google sheets with several tabs.

    "SS", "Math", "Sci", ...

    and I have a formula I am working on in a separate tab....

    =iferror(if(A2="","",index(SS!B:B,match(A2&$B$1,SS!E:E&SS!G:G,0))))
    ^
    I am wondering if the "SS!B:B" piece can be modified to include column D from ALL the tabs, not just SS.

    I hope that makes sense.

  2. #2
    VBAX Newbie
    Joined
    Jun 2018
    Posts
    2
    Location
    Maybe I can clarify better
    So I am working in Google Sheets. I have a document with like 20 sheets ("SS", "Math", "Sci", etc) i have 3 important columns. Column D has a name, Column E has Room Number, and Column G has Period (period in the form of 1ABCD or 2AC or 8B, for a 4 day rotation).

    I want to transform this onto a separate tab that that shows Room Usage.

    On that sheet I would have room numbers down the left had side. On the top row i would have 1A 1B 1C 1D 2A 2B etc. I want the teachers names to appear in the columns that match room w period.

    ie. on the tab "SS"
    D E F G
    Joe 300 Math 1AC

    On the room usage tab i'd like to see, next to "300", the columns with 1A and 1C to have the name Joe in them. The formula, in my mind would search for a match on ALL sheets with Room number and Period and return teacher name if there is a match.

    THE BIG ISSUE is i would like the formula to search columns D, E, and G on alllll the tabs. Also, not sure how I would have it associate "1AC" with "1A" AND "1C".
    Hope that explains it well. I can't share the sheet as there is some confidential information.
    Last edited by traggs25; 06-13-2018 at 07:30 PM.

  3. #3
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    welcome to the forum.

    i have no experience in google sheets and i hove no idea how to do it.

    in ms excel, what you are after is done by 3-D references.
    https://support.office.com/en-us/art...3-65d38d03370f

    index and match are not in the list of functions that can be used in 3-D references.

    click Create a 3-D reference in the above page to see the list of the functions that can be used in 3-D references
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •