CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
John Spartan
John Spartan

Reputation: 75

Extract Value from Cell on Another Sheet Based on List

I have a list of data on one sheet. A second sheet is used to display who's done what, like a dashboard. I've been given an array formula to generate the data that looks up the employee's name, and displays it if they are present on the list for a course completed. If so, a "yes" is listed on the dashboard under the corresponding course number. I cannot figure out what I need to change the REGEXREPLACE with to present the date column from the data list instead of "yes". I'm aware REGEXREPLACE only works for text values, and dates aren't - even changing the date column to text seems to matter not.

Here is a working example of the current array formula:

https://docs.google.com/spreadsheets/d/1jkG515zyl4UxRHxhtFTjgWsjG0aBE4vsOxbpvqgjogU/edit#gid=536376041

Here is the formula used:

=ARRAYFORMULA(IF(A5:A="",,REGEXREPLACE(IFNA(VLOOKUP(A5:A, QUERY({TRIM('Form Responses 1'!B2:G)}, "select Col1,count(Col1) group by Col1 pivot Col6"), MATCH(F2:P2, QUERY(QUERY({TRIM('Form Responses 1'!B2:G)}, "select Col1,count(Col1) group by Col1 pivot Col6"), "limit 0", 1), 0), 0))&"", "\d+", "yes")))

In the above example, I need the Date Completed from col D on the Form Responses sheet.

Upvotes: 0

Views: 292

Answers (1)

kirkg13
kirkg13

Reputation: 3010

Here is a first attempt that might help you.

This is a straightforward query, that pulls the dates, and pivots to have the employee names on the left, and the course names on the top.

But it doesn't try to match up data with an existing list of employees - it just lists all of the employees that have submitted a form. So if you want to see all employees, with blank rows for those who haven't submitted a form, this won't work for you.

The formula, in A2,is:

=ARRAYFORMULA(QUERY({'Form Responses 1'!B2:G}, "select Col1,max(Col3) where Col1<>'' group by Col1 pivot Col6 order by Col1"))

See tab Sheet1-GK, added to your sample sheet.

Let me know if this helps, or if you need something different.

UPDATE: To limit the result to a specific list of courses, use the following modification:

=ARRAYFORMULA(QUERY({'Form Responses 1'!B2:G}, 
   "select Col1,max(Col3) where Col1<>'' 
                            and Col6 matches '" & TEXTJOIN("|",1,F2:2) & "' 
    group by Col1 pivot Col6 order by Col1 "))

Here, the list of desired courses to report on is in F2:2, where you had them originally, but this list could be kept anywhere, even on another tab. If you name the range where you place it, that can simplify this formula a bit. For now, you could just hide row 2. I've grouped it, on the left, to hide it. Use the [+] to reveal it again.

Upvotes: 2

Related Questions