+ Reply to Thread
Results 1 to 10 of 10

Lookup formulas to search multiple sheets and return tabled data

  1. #1
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Lookup formulas to search multiple sheets and return tabled data

    I have an Excel workbook that's getting out of hand. I need to take some of the data stored therein and import it into Access, so that I can better manage it. Unfortunately, the data isn't in a nice tabled form, so I can either manually make it so or hopefully use some nifty formulas to get the data in a nice format to import to Access.

    In the attached workbook on the "dataimportation" sheet, I have entered some formulas that grab the data I need from the "Region 1" sheet. The only data I need is in the column titled "CO" which is the middle column for each month. In subsequent sheets, I only need the values in the middle column(s), but there may be more than one, with two at the most. I've highlighted the ones I need in the Region 2 sheet. The problem with my formulas is that they are pretty static and won't scale well to other sheets with data in different places. I'm sure there are other problems as well.

    Can someone please save me from this mess of data? Are there any other ways to get the data besides formulas? Thanks a million.

    excel forum - index.xlsm

  2. #2
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Lookup formulas to search multiple sheets and return tabled data

    Maybe we can start with just the first Sheet. Anyone know how I can get column "CO" for each "saler" by month?

  3. #3
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Lookup formulas to search multiple sheets and return tabled data

    Maybe someone can tell me if my request is ludicrous and impossible?

  4. #4
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Lookup formulas to search multiple sheets and return tabled data

    bump up ttt

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,468

    Re: Lookup formulas to search multiple sheets and return tabled data

    So you want the next "Saler" (Fred) to appear in rows 15 to 25 and for his data to appear in the relevant columns, and then the next saler, Tim, to appear in row 26 onwards, and so on?

    Pete

  6. #6
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Lookup formulas to search multiple sheets and return tabled data

    You got it. Really regretting my choice of using the "word" Saler

    I'm trying to get the ugly data in a nice table form.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,468

    Re: Lookup formulas to search multiple sheets and return tabled data

    Okay, well take a look at the attached workbook, where I've used this formula in C3:

    =IFERROR(INDEX('Region 1'!$C:$AF,MATCH($A2,'Region 1'!$A:$A,0)+MATCH(C$1,'Region 1'!$B:$B,0)-8,MATCH($B2,'Region 1'!$C$6:$AF$6,0)-1),"")

    This can be copied across and down as far as you need to. I've added the monthly dates to column B by copying the block of 12 cells, and put the next two names in column A - just extend this pattern down for the other names.

    I'm not sure what you want to do with the Region2 sheet.

    Hope this helps.

    Pete
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Lookup formulas to search multiple sheets and return tabled data

    Looks great so far! Hopefully I can adapt it for Region 2. I really appreciate your help for this. It is going to save our department a lot of time!

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,468

    Re: Lookup formulas to search multiple sheets and return tabled data

    I forgot to mention that you had the word "apples " (with a trailing space) down column B of the Region1 sheet, but only "apples" (without a space) in C1, so to ensure they matched I added the space to C1.

    Pete

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,468

    Re: Lookup formulas to search multiple sheets and return tabled data

    Hey, thanks for the rep - I like the comment you left !

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

    Pete

+ 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. LOOKUP Array & Index Function (Search data across multiple sheets)
    By dizzle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-02-2013, 09:26 AM
  2. Search value in multiple sheets and return sheet name(s)
    By evolv12 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-07-2013, 02:11 PM
  3. Lookup then Search for text string and return multiple entries
    By tnewell2 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-24-2012, 12:11 PM
  4. Search multiple sheets and return list of multiple results
    By Abuck in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 03-07-2012, 11:59 AM
  5. Multiple search and data return formulas
    By jackifizzle in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-30-2011, 06:00 AM
  6. Tabled Lookup
    By iaxgls in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-27-2009, 09:57 AM
  7. search multiple sheets for specific date, return data in cell to r
    By NonIllegitimiCarborundum in forum Excel General
    Replies: 0
    Last Post: 04-28-2006, 04:10 PM
  8. multiple sheets and lookup formulas
    By upstate_steve in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-24-2005, 03:38 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