+ Reply to Thread
Results 1 to 6 of 6

Advanced Lookups (May require VBA)

  1. #1
    Forum Contributor
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    656

    Advanced Lookups (May require VBA)

    This is a hard one to explain, I am working on advanced lookups from one sheet to another based on a sheet someone else made and is using and doesn't want the format changed too much.

    Basically it's a "calendar" by weeks and years of sales data on one tab, then on the second tab we want to pull specific totals from the first tab. Normally I use index match, vlookups, etc but the layout of the first tab doesn't seem to allow for this so I am seeking alternative ways.

    First tab you will see a calendar for June `22 and the green boxes are the data that is being pulled into the second tab.
    So on tab one I9 and I12 would be pulled and put into G3 and J3 on the second tab. I highlighted them in green. I could just put an =I9 and =I12 then manually do that for the whole year but that would take ages.

    Is there another way to look this up? Keep in mind it is a running calendar for the whole year and beyond (Each section has a part with the month June '22 then July `22 and so on)

    Sample attached, thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,873

    Re: Advanced Lookups (May require VBA)

    Try

    in G3

    =IFERROR(SUMPRODUCT((OFFSET('Sales Tracker'!$B$1,MATCH(EOMONTH('Other Data'!$B3,-1)+1,'Sales Tracker'!$B$1:$B$1000,0)+6,1,37,14)),--(OFFSET('Sales Tracker'!$B$1,MATCH(EOMONTH('Other Data'!$B3,-1)+1,'Sales Tracker'!$B$1:$B$1000,0)+3,0,37,14)=DAY($B3))),"")

    in J3

    =IFERROR(SUMPRODUCT((OFFSET('Sales Tracker'!$B$1,MATCH(EOMONTH('Other Data'!$B3,-1)+1,'Sales Tracker'!$B$1:$B$1000,0)+9,1,37,14)),--(OFFSET('Sales Tracker'!$B$1,MATCH(EOMONTH('Other Data'!$B3,-1)+1,'Sales Tracker'!$B$1:$B$1000,0)+3,0,37,14)=DAY($B3))),"")

    I added July data 9copy of June) to test

    Note: Data foe ach calendar month must be consistent (number of rows and row spacings)
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Forum Contributor
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    656

    Re: Advanced Lookups (May require VBA)

    Wow that is advanced lol. Thanks kindly going to test it out in the morning.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,651

    Re: Advanced Lookups (May require VBA)

    Please try to make your thread titles explicit of what you are trying to do in future, not how you want/expect it to be done. Thanks.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Contributor
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    656

    Re: Advanced Lookups (May require VBA)

    Sorry AliG, I don't follow what you are saying. Title mentions the issue and just an addendum that it may require VBA instead of formulas. Are you saying I should not put (may require VBA)?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,651

    Re: Advanced Lookups (May require VBA)

    Your thread title should say WHAT you are trying to do, not HOW you think it might be done.

+ 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. Advanced Lookups
    By Max in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-06-2005, 06:05 PM
  2. Advanced Lookups
    By Steve (inuyasha1276@yahoo.com) in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  3. Advanced Lookups
    By Max in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 11:05 AM
  4. Advanced Lookups
    By Max in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 06:05 AM
  5. [SOLVED] Advanced Lookups
    By Steve (inuyasha1276@yahoo.com) in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. [SOLVED] Advanced Lookups
    By Steve (inuyasha1276@yahoo.com) in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  7. Advanced Lookups
    By Steve (inuyasha1276@yahoo.com) in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  8. Advanced Lookups
    By Steve (inuyasha1276@yahoo.com) in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM

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