I'm trying to create an email lookup worksheet that essentially lists the results of a search throughout multiple worksheets (all in the same Sheet document).
As an example, I have "Sheet 1", "Sheet 2" and "Sheet 3". Each of these hold a log of our emails that staff send - helping us keep track of who sent what and to who. Sheet 1 belongs to one staff member, Sheet 2 belongs to another, etc.
On the master email lookup worksheet; you can put in the recipient's email address to find what emails had been sent to that person, and by who (from our staff). In each sheet the columns are:
- Column A: Email Date Sent
- Column B: Email Date Sent (different formatting)
- Column C: Email To (Recipient)
- Column D: Email Subject
- Column E: Web Link (URL)
- Column F: Internal notes
Is there a way to do this via a formula that looks over all of these worksheets, and also potentially adds in another column as a unique identifier for our staff members (e.g. if from "Sheet 1", add a column saying "Person 1", etc.)?
The dashboard lets us put in the recipient email, and it gives data back of Column 1: Date Sent, Column 2: Staff Member (unique identifier / name), and Column 3: Email Subject.
Example workbook:
docs(dot)google(dot)com/spreadsheets/d/1mNJRXqAynJaQKHDeyFqvlV1ExUXbnTZFv0YYu7dKhaU/edit?usp=sharing
Bookmarks