Printable View
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.
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.
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