+ Reply to Thread
Results 1 to 18 of 18

Sequential Lookup in multiple sheets

  1. #1
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Sequential Lookup in multiple sheets

    I have an excel file with multiple sheets, each of which is unique to a specific sales rep and contain a list of their client contact info including company name, address, City, State and zip. There are 15 reps, hence 15 worksheets. I am trying to see if there is a way to consolidate all the contacts into one sheet called Summary. I know I can manually copy and past from each worksheet, but was hoping there was a better way, especially in light of the fact the reps add new contacts on a regular basis.

    Attached is a sample that I cobbled together. I realize my current formula in the summary field is primitive and will not satisfy what I am looking to do.

    Thanks!
    Attached Files Attached Files
    Last edited by Perk1961; 06-30-2015 at 09:21 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sequential Lookup in multiple sheets

    Hi,

    You could do it with a macro that loops through each sheet and copies the range of contacts and pastes them to the next blank row on the Summary.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sequential Lookup in multiple sheets

    With the code below, see the result in the attached file.

    Please Login or Register  to view this content.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Sequential Lookup in multiple sheets

    see reply in quotes.
    Last edited by Perk1961; 07-01-2015 at 11:13 AM. Reason: I wanted to reply specifically to the moderator

  5. #5
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Sequential Lookup in multiple sheets

    See 2nd reply to oldere in quotes
    Last edited by Perk1961; 07-01-2015 at 11:15 AM. Reason: I wanted this reply to be to Older to meant to reply with quote

  6. #6
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Sequential Lookup in multiple sheets

    Quote Originally Posted by oeldere View Post
    With the code below, see the result in the attached file.

    Please Login or Register  to view this content.
    Also, with this macro, is there a way to have the "Consolidated" worksheet automatically update when a new address is added in one of the reps worksheets?

  7. #7
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Sequential Lookup in multiple sheets

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    You could do it with a macro that loops through each sheet and copies the range of contacts and pastes them to the next blank row on the Summary.
    Thank you for your feedback. I will have to get up to speed on how to create such a macro.

  8. #8
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Sequential Lookup in multiple sheets

    Quote Originally Posted by oeldere View Post
    With the code below, see the result in the attached file.

    Please Login or Register  to view this content.
    Thank you for your feedback. The good news is I can see from the file you included for me to download that the macro worked perfected. The bad news is I need to find a site that shows me how to replicate it, as I am not too familiar with how to create or view macros like I am with formulas. If you could point me in the right direction that would be great as I need to replicate this macro on my primary excel file.

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sequential Lookup in multiple sheets

    Just try to explain.

    If it is not clear just ask and we try again.

    In this file you find the code if you press together: ALT + F11

    You see on the left side module1 (if you click on that text) the code, which also is added on the forum, will show)

    You will find the code on the right side of the window.

    You can copy => Paste such code.

    If you click on the red cross in the right corner, you go back to your excel file.


    To run the macro:

    Excel 2013 => view => macro => show macro (or ALT + F8)

    in this file you find the name => integratie_Oeldere_revisted_vs3

    If you click on run, the macro will execute.

    Try this on a dummy of your files.

    Next step will be, how to add the macro in another file.

    Please reply.

  10. #10
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Sequential Lookup in multiple sheets

    Quote Originally Posted by oeldere View Post
    Just try to explain.

    If it is not clear just ask and we try again.

    In this file you find the code if you press together: ALT + F11

    You see on the left side module1 (if you click on that text) the code, which also is added on the forum, will show)

    You will find the code on the right side of the window.

    You can copy => Paste such code.

    If you click on the red cross in the right corner, you go back to your excel file.


    To run the macro:

    Excel 2013 => view => macro => show macro (or ALT + F8)

    in this file you find the name => integratie_Oeldere_revisted_vs3

    If you click on run, the macro will execute.

    Try this on a dummy of your files.

    Next step will be, how to add the macro in another file.

    Please reply.
    Thank you. I was able to find the macro and file name, then run the macro, and it worked! The "Consolidated" worksheet automatically populated with the new records I added. I am using a Mac computer with "Microsoft Excel for Mac 2011, version 14.5.1" so my access to view the macro was through Excel for Mac 2011 => Developer => macros=> Run. I did not see a shortcut (i.e. Alt +F8) to access this. FYI

    My next task will be to try and replicate the macro in the main excel file. I may have to use my wife's PC as my Mac does not seem to translate the shortcuts the same as you described. For example, Alt + F11 does not reveal the code. I will see if I can noodle around on the Mac for a few more minutes before going to her computer.

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sequential Lookup in multiple sheets

    I don't know the sortcut for the Mac.

    With Alt + F11 you can go to the VBA.

    Choose Insert => module (now you get the same module as showed in your file).

    You can copy paste the code, in this module.

    After that you can run the macro, as explained before.

    Notice => you have to save the file as macro enabled => file => save as => excel workbook with macro's.

    If it is not clear just ask and we try again.

  12. #12
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Sequential Lookup in multiple sheets

    Quote Originally Posted by oeldere View Post
    I don't know the sortcut for the Mac.

    With Alt + F11 you can go to the VBA.

    Choose Insert => module (now you get the same module as showed in your file).

    You can copy paste the code, in this module.

    After that you can run the macro, as explained before.

    Notice => you have to save the file as macro enabled => file => save as => excel workbook with macro's.

    If it is not clear just ask and we try again.
    Thank you. This worked. My only other question is, can the macro be programed to eliminate certain worksheets while keeping others? For example, if I wanted to include the records from Denny, Rob and Michael T, but wanted to exclude Gabe.

    Ultimately I am downloading a file that the company uses from Google sheets as an Excel file, then copying all the data to the VBA enabled worksheet, then running the macro. I tested this and it works fine, however I have to eliminate all the tabs (worksheets) that are not pertinent. Of course I could just delete each tab that is not pertinent, but that is extra work.

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sequential Lookup in multiple sheets

    change this in line in de code

    Please Login or Register  to view this content.
    by

    Please Login or Register  to view this content.

    For the second question you have to explain more.

    Which sheets do you want to delete, and when do you want to delete them.

  14. #14
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Sequential Lookup in multiple sheets

    Quote Originally Posted by oeldere View Post
    change this in line in de code

    Please Login or Register  to view this content.
    by

    Please Login or Register  to view this content.

    For the second question you have to explain more.

    Which sheets do you want to delete, and when do you want to delete them.

    That worked great. What is I want to eliminate more than one rep. For example, if I want to eliminate Gabe and Denny? The actual worksheet I will be using will have about 15 reps that I need to include and 12 reps I need to exclude.

  15. #15
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sequential Lookup in multiple sheets

    Please don't quote the whole post if it is not necesary.

    You could use 12 if statements like above to exclude these sheets.

    Maybe there is another (better) way, but therefore you need the help of other forummembers.

  16. #16
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Sequential Lookup in multiple sheets

    Super. Thanks for clarifying. I was able to change the line from

    If .Name <> "Consolidated" And .Name <> "Summary" And .Name <> "Output" And .Name <> "Gabe" Then

    to:

    If .Name <> "Consolidated" And .Name <> "Summary" And .Name <> "Output" And .Name <> "Gabe" And .Name <> "Denny" Then

    Do you know if there is a limited number of statements you can use? I may have up to 20.

  17. #17
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Sequential Lookup in multiple sheets

    I don't think there is a limit on the if statement, but in such a case there will be a better solution (which I can't deliver to you).

    Maybe you can start a new question on that, in the VBA / program group.

    You can refer to this topic, if you think it is usefull.

  18. #18
    Forum Contributor
    Join Date
    07-04-2013
    Location
    United States
    MS-Off Ver
    Excel for Mac -V 16.33 -- Office 365
    Posts
    279

    Re: Sequential Lookup in multiple sheets

    No problem. The solution you provided solved the issue for this particular project. Thanks again!

+ 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. How do I create sequential dates across multiple sheets (Namely, Sept 01-30)
    By dreamingscott in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-20-2014, 07:55 PM
  2. Macro For Unique Sequential Numbering on multiple sheets
    By dkannapel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-03-2014, 02:57 PM
  3. [SOLVED] Lookup and sum multiple values based on a single criteria in multiple sheets.
    By paulsanett in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2014, 12:18 PM
  4. Lookup and return multiple matches across multiple sheets
    By somnath6309 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2013, 03:45 PM
  5. Copy non-sequential columns with headers (from multiple sheets) to one consolidated sheet
    By constantmallee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-18-2012, 02:24 PM

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