CODEWITHSUNDEEP
google-sheetsfiltermatchgoogle-sheets-formulaarray-formulas
Johnny Thomas
Johnny Thomas

Reputation: 623

Pulling all information left of a column from multiple sheets

So I have a workbook (Google Sheets) where three different sheets have three unique forms that feed into them.

The titles of each sheet is each respective employee's name and the number of columns vary within each form however the final column in each is titled "Week".

I wish to add a fourth "Week" sheet where you can pull down the employee name (same as sheet name) and the appropriate week and find that employee's entry for that week.

The sheets look a little like this:

Sheet 1: EmployeeA

J1  J2        Week
Yes Maybe     2    
Yes Definetly 3

Sheet 2: EmployeeB

Q1  Q2     Q3        Week
No Strong Maybe      3

Sheet 3: EmployeeC

L1  L2     L3        L4     Week
Yes Strong Maybe     Happy  2    
Yes Weak   Definetly Happy  3
No  Weak   Never     Sad    4

So I would like to create a fourth sheet that looks like this:

Employee:  _______
Week:      _______

**CONTENT FROM THE ROW WITH THAT WEEK

This would be an easy VLOOKUP however because there are differing amount of columns I am unsure how to approach

I have the Employee/Week options as pull-downs.

As an example, this is how Sheet4 would look

Employee:  EmployeeC
Week:      2

L1  L2     L3        L4     Week
Yes Strong Maybe     Happy  2

Note that this also should include the titles of the table it's pulling from, which again is different for each sheet.

Edit: Here is the link to the workbook; this has all the information that will be used, with edited content https://docs.google.com/spreadsheets/d/1qMTc2x1vh47KfXfBfsj70ZliwKNRTzOvjYs6N5hIKZ4/edit?usp=sharing

Upvotes: 1

Views: 64

Answers (2)

Kate
Kate

Reputation: 1533

Assuming the structure you describe in your question, entering this into your Summary sheet should work: 

={INDIRECT($B$1&"!R1:R1C"&(MATCH("Week", INDIRECT($B$1&"!1:1"), 0)-1), FALSE);
  FILTER(INDIRECT($B$1&"!R2C1:C"&(MATCH("Week", INDIRECT($B$1&"!1:1"), 0)-1), FALSE), 
         INDIRECT($B$1&"!R2C"&MATCH("Week", INDIRECT($B$1&"!1:1"), 0)&":C"&MATCH("Week", INDIRECT($B$1&"!1:1"), 0), FALSE)=$B$2)}

View Answer Demo Here.

Explanation

  • $B$1 is your selected Employee value, which refers to a sheet name.
  • $B$2 is your selected week number, which is used to filter results.
  • INDIRECT($B$1&"!"...) lets us reference a range, including which sheet it's on, dynamically.
  • MATCH("Week", INDIRECT($B$1&"!1:1"), 0) finds the index of the column in the selected sheet that contains the "Week" column.
  • INDIRECT($B$1&"!R1:R1C"&(MATCH("Week", INDIRECT($B$1&"!1:1"), 0)-1), FALSE) pulls in the headings.
  • FILTER(INDIRECT($B$1&"!R2C1:C"&(MATCH("Week", INDIRECT($B$1&"!1:1"), 0)-1), FALSE), INDIRECT($B$1&"!R2C"&MATCH("Week", INDIRECT($B$1&"!1:1"), 0)&":C"&MATCH("Week", INDIRECT($B$1&"!1:1"), 0), FALSE)=$B$2) pulls in all rows whose Week value matches the week specified.

Updated Answer for Actual Workbook's structure

  • Replace $B$2 with INT(REGEXEXTRACT($E$1, "^Week (\d+)")) which extracts the week number from your freetext week description (ex: "Week 4: Week of September 2 to September 8" => 4).
  • Replace MATCH("Week", INDIRECT($B$1&"!1:1"), 0) with MATCH("Reporting Week", INDIRECT($B$1&"!1:1"), 0), as "Reporting Week" is the heading of the target Week column in each Employee's data sheet. 
={INDIRECT($B$1&"!R1:R1C"&(MATCH("Reporting Week", INDIRECT($B$1&"!1:1"), 0)-1), FALSE);
  FILTER(INDIRECT($B$1&"!R2C1:C"&(MATCH("Reporting Week", INDIRECT($B$1&"!1:1"), 0)-1), FALSE), 
         INDIRECT($B$1&"!R2C"&MATCH("Reporting Week", INDIRECT($B$1&"!1:1"), 0)&":C"&MATCH("Reporting Week", INDIRECT($B$1&"!1:1"), 0), FALSE)=INT(REGEXEXTRACT($E$1, "^Week (\d+)")))}

Alternative Answer for selecting columns to left and right of target column

={{INDIRECT($B$1&"!R1:R1C"&(MATCH("Week", INDIRECT($B$1&"!1:1"), 0)-1), FALSE),
   INDIRECT($B$1&"!R1C"&(MATCH("Week", INDIRECT($B$1&"!1:1"), 0)+1)&":R1C"&COLUMNS(INDIRECT($B$1&"!1:1")), FALSE)};
  FILTER({INDIRECT($B$1&"!R2C1:C"&(MATCH("Week", INDIRECT($B$1&"!1:1"), 0)-1), FALSE),
          INDIRECT($B$1&"!R2C"&(MATCH("Week", INDIRECT($B$1&"!1:1"), 0)+1)&":C"&COLUMNS(INDIRECT($B$1&"!1:1")), FALSE)},
         INDIRECT($B$1&"!R2C"&MATCH("Week", INDIRECT($B$1&"!1:1"), 0)&":C"&MATCH("Week", INDIRECT($B$1&"!1:1"), 0), FALSE)=$B$2)}

View Alternative Demo Here.

For the actual workbook, this is equivalent to:

={{INDIRECT($B$1&"!R1:R1C"&(MATCH("Reporting Week", INDIRECT($B$1&"!1:1"), 0)-1), FALSE),
   INDIRECT($B$1&"!R1C"&(MATCH("Reporting Week", INDIRECT($B$1&"!1:1"), 0)+1)&":R1C"&COLUMNS(INDIRECT($B$1&"!1:1")), FALSE)};
  FILTER({INDIRECT($B$1&"!R2C1:C"&(MATCH("Reporting Week", INDIRECT($B$1&"!1:1"), 0)-1), FALSE),
          INDIRECT($B$1&"!R2C"&(MATCH("Reporting Week", INDIRECT($B$1&"!1:1"), 0)+1)&":C"&COLUMNS(INDIRECT($B$1&"!1:1")), FALSE)},
         INDIRECT($B$1&"!R2C"&MATCH("Reporting Week", INDIRECT($B$1&"!1:1"), 0)&":C"&MATCH("Reporting Week", INDIRECT($B$1&"!1:1"), 0), FALSE)=INT(REGEXEXTRACT($E$1, "^Week (\d+)")))}

Upvotes: 2

player0
player0

Reputation: 1

alternative:

=ARRAY_CONSTRAIN(QUERY({
 INDIRECT(B1&"!"&ADDRESS(1, 1)&":"&ADDRESS(ROWS(
 INDIRECT(B1&"!A:A")), MATCH("Week", 
 INDIRECT(B1&"!1:1"), 0)-1)), 
 INDIRECT(B1&"!"&ADDRESS(1, MATCH("Week", 
 INDIRECT(B1&"!1:1"), 0))&":"&ADDRESS(ROWS(
 INDIRECT(B1&"!A:A")), MATCH("Week", 
 INDIRECT(B1&"!1:1"), 0)))}, "where Col"&MATCH("Week", 
 INDIRECT(B1&"!1:1"), 0)&" = "&B2, 1), ROWS(A:A), COUNTA(
 INDIRECT(B1&"!1:1"))-1)

0

Upvotes: 0

Related Questions