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.)?
Picture below of the simple dashboard so far.