+ Reply to Thread
Results 1 to 11 of 11

Lookup In Multiple Sheets & Add Unique Identifier | Google Sheets

  1. #1
    Registered User
    Join Date
    06-22-2022
    Location
    AUs
    MS-Off Ver
    NA
    Posts
    6

    Question Lookup In Multiple Sheets & Add Unique Identifier | Google Sheets

    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
    Last edited by Dave Shopping; 06-23-2022 at 03:34 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    797

    Re: Lookup In Multiple Sheets & Add Unique Identifier | Google Sheets

    it is likely possible to do exactly what you want, but without a spreadsheet with some sample data that is representative of what you want to do, it is impossible to give a definitive answer.
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  3. #3
    Registered User
    Join Date
    06-22-2022
    Location
    AUs
    MS-Off Ver
    NA
    Posts
    6

    Re: Lookup In Multiple Sheets & Add Unique Identifier | Google Sheets

    Trying to post an example but this site doesn't allow any links lol

  4. #4
    Registered User
    Join Date
    06-22-2022
    Location
    AUs
    MS-Off Ver
    NA
    Posts
    6

    Re: Lookup In Multiple Sheets & Add Unique Identifier | Google Sheets

    "You are not allowed to post any kinds of links, images or videos until you post a few times."

  5. #5
    Registered User
    Join Date
    06-22-2022
    Location
    AUs
    MS-Off Ver
    NA
    Posts
    6

    Re: Lookup In Multiple Sheets & Add Unique Identifier | Google Sheets

    Example workbook:
    docs(dot)google(dot)com/spreadsheets/d/1mNJRXqAynJaQKHDeyFqvlV1ExUXbnTZFv0YYu7dKhaU/edit?usp=sharing[/url]

    Need to replace the (dot)'s with actual periods, I cannot directly paste it for some reason.

  6. #6
    Registered User
    Join Date
    06-22-2022
    Location
    AUs
    MS-Off Ver
    NA
    Posts
    6

    Re: Lookup In Multiple Sheets & Add Unique Identifier | Google Sheets

    Posted below

  7. #7
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    797

    Re: Lookup In Multiple Sheets & Add Unique Identifier | Google Sheets

    while i take a look at the file, here is the clickable link for others interested in helping:

    https://docs.google.com/spreadsheets...haU/edit#gid=0

    note to Dave Shopping: sorry, at the moment your post count is below 10, so the forum wont allow you to post links yet.. but hang in there.. once you pass 10 posts then you will be able to post links.... its a security measure to stop spammers from posting their junk

  8. #8
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    797

    Re: Lookup In Multiple Sheets & Add Unique Identifier | Google Sheets

    ok.. after having a look... thinking of formula solutions....

    some things to consider/clarify/discuss:

    will your sheet names always be named with a pipe symbol and a space before the name of the person?
    if not, then can you rename the sheets in the manner they would be done in your real life situation?

    can the Staff member column on the Email Lookup sheet be moved (so like switching around columns E and F) or must it be in this order/arrangement?

    will the Staff member column always only use the first name AND it will be unique AND it will always be a part (after the pipe space) of the sheet name?

    is this all that you will require to be done? or is there some chance that you will want more columns of information included in the results?

  9. #9
    Registered User
    Join Date
    06-22-2022
    Location
    AUs
    MS-Off Ver
    NA
    Posts
    6

    Re: Lookup In Multiple Sheets & Add Unique Identifier | Google Sheets

    To answer your questions:
    1. The sheet does have pipe symbols in the worksheets - this is because we have multiple sheets per staff member to identify their manual emails sent (not the automated ones from our
    2. Columns E and F can be switched. That is no problem
    3. Yes to all - except for the "after the pipe" space. I've amended the example sheet to reflect what it actually looks like on our end - the staff name is always before the pipe symbol.
    4. We will also likely be including the API URL Link in the master Email Lookup worksheet, too - to help our team get easy reference to the url of the lead. Thanks for pointing this out. I've also added this in to the worksheet.

    Please let me know if there's anything else I can provide to help solve this.

    I greatly appreciate the time you're putting into this already to understand what we want.

  10. #10
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    797

    Re: Lookup In Multiple Sheets & Add Unique Identifier | Google Sheets

    ok, with a bit of trickery, we can do this with QUERY, like this...

    change the order of your columns to be:
    Date, Email Subject, API URL Link, Staff Member

    then put this formula into cell D7:
    Please Login or Register  to view this content.
    to explain....

    we are doing a QUERY of each sheet, and Col1 is B, Col3 is D, and Col4 is E, then we slap the name into the last column..

    BUT.. what if someone doesnt send an email.. when we slap the name on the end of the array, it creates an issue were we have missing values (the first 3 cells)...
    so what we do is create some x cells to fill when theres an error (no data for the range in question)....

    then we need to strip away the blank and x rows.... leaving us with the filtered data.

    CAVEAT:
    with this method, if someone sends 2 emails to the same person, it will be an error, so the data will be blanked out just like the error when there is no result found.

    Sooooo... you can only use this if you know each person only sends 1 email to the contact.

  11. #11
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    797

    Re: Lookup In Multiple Sheets & Add Unique Identifier | Google Sheets

    after a bit of playing around.. heres a formula where you will not need to move any columns around....

    again, drop this into D7 (just like before.. but dont move any columns around.. keep it just like you have it already)
    Please Login or Register  to view this content.
    in this version, we are creating Col 5 as an array of the persons name.. and slotting it into the middle of the QUERY data.

    the benefit of this formula is that users can send more than 1 email to an email address, and it wont be errored out... so if someone send s 2 emails to the same person.. you will get 2 results for that person instead of none.
    Last edited by janmorris; 06-23-2022 at 10:11 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] [GOOGLE SHEETS] Counting Occurrence in Conjunction with Name on Multiple Sheets
    By theexcelnovice123 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 6
    Last Post: 11-04-2021, 01:38 PM
  2. Google Sheets: how do you make multiple pages/versions without more sheets
    By Vantropix in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 07-24-2021, 08:57 AM
  3. Google Sheets: how do you make multiple pages/versions without more sheets
    By Vantropix in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-13-2021, 10:26 PM
  4. Google Sheets: Lookup and Merge Data Based on Multiple Criteria
    By Manikandan Arumugam in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 01-28-2021, 04:06 AM
  5. Google Sheets: need to dynamic formula for unique value and count multiple same value
    By wlinksanju in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 11-21-2020, 10:29 PM
  6. [SOLVED] Combining 2 Sheets Merging Based on Unique Identifier
    By kirrash in forum Excel General
    Replies: 0
    Last Post: 10-13-2020, 11:49 AM
  7. Replies: 5
    Last Post: 03-12-2013, 08:23 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1