+ Reply to Thread
Results 1 to 11 of 11

Pulling Data to separate sheets based on one column

  1. 10-04-2013, 09:19 AM #1
    joliver
    joliver is offline
    Registered User
    Join Date
    09-11-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    30

    Pulling Data to separate sheets based on one column

    I am trying to figure out how to pull data based on column B to individual tabs.

    I have the following data



    A B C D
    Jane Doe | L1 | 20% | High
    John Doe | L2 | 15% | Medium
    Mike Smith | L1 | 60% | Low
    Marie Smith | L4 | 10% | Low


    I want to have the first tab/spreadsheet only pull records that have L1 values for column B
    In the next tab/spreadsheet I want to pull records that have L2 values in column B etc.

    What formula can I write that will pull all L1 records (A.B.C.D) in to Tab 1, then all L2 records (A,B,C,D) into the next tab?
    Reply With QuoteRegister To Reply
  2. 10-04-2013, 09:44 AM #2
    dc519
    dc519 is offline
    Registered User
    Join Date
    07-25-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Pulling Data to separate sheets based on one column

    I am looking for the same thing....subscribed!
    Reply With QuoteRegister To Reply
  3. 10-04-2013, 09:45 AM #3
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,704

    Re: Pulling Data to separate sheets based on one column

    Take a look at this thread from a few days ago:

    http://www.excelforum.com/excel-form...om-2-tabs.html

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  4. 10-04-2013, 12:29 PM #4
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Pulling Data to separate sheets based on one column

    The attached sample using your data might help.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  5. 10-04-2013, 02:51 PM #5
    joliver
    joliver is offline
    Registered User
    Join Date
    09-11-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Pulling Data to separate sheets based on one column

    I am trying this but it's pulling the same record over and over. Would it be a problem if the cell has a pick list?
    Reply With QuoteRegister To Reply
  6. 10-04-2013, 03:07 PM #6
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Pulling Data to separate sheets based on one column

    In the workbook that I uploaded, row 2 has the same formula on worksheets 2,3,4. All one has to do is to copy the formula on Sheet2 row 2 to any new worksheet. The formula is linked to A1 of the active sheet.

    If you select A1 on say Sheet2, click on the Data Tab, Data Validation, List and enter the "L" values in a straight list like this L1, L2, L4. Add to this listing as required. A drop-down list will appear and you can choose what value you want and the listing on the worksheet will change accordingly.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 10-04-2013, 03:49 PM #7
    joliver
    joliver is offline
    Registered User
    Join Date
    09-11-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Pulling Data to separate sheets based on one column

    Not sure what I am doing wrong it's still only pulling one record and it's not always correct to the L value that I choose in the drop down list. This is the formula I have entered:

    =IFERROR(INDEX('2013 Talent Pool'!$D$2:$D$70,SMALL(IF('2013 Talent Pool'!$H$2:$H$70=$C$1,ROW('2013 Talent Pool'!$H$2:$H$70)-MIN(ROW('2013 Talent Pool'!$H$2:$H$70))+1),ROWS('2013 Talent Pool'!$1:1))),"")

    Column D = Name
    Column H = L values
    Reply With QuoteRegister To Reply
  8. 10-04-2013, 04:05 PM #8
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Pulling Data to separate sheets based on one column

    This is an ARRAY FORMULA entered with Ctrl + Shift + Enter. If entered not as an array formula, the results will be highly "volatile".

    Note that the formula in each column varies only in the first INDEX range and the rest of the formula remains constant.

    There are only 2 things that I can think of that will cause the formula to fail and the first one is the Array Formula not being used and the second is if this formula is on the 213 Talent Pool worksheet, it won't work. It must be on a worksheet other than 2013 Talent Pool.

    If you are still having problems, can you upload the actual workbook with confidential data removed?
    Reply With QuoteRegister To Reply
  9. 10-04-2013, 04:36 PM #9
    joliver
    joliver is offline
    Registered User
    Join Date
    09-11-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Pulling Data to separate sheets based on one column

    How do I enter the ARRAY formula ctrl+shift+enter?
    Reply With QuoteRegister To Reply
  10. 10-04-2013, 04:38 PM #10
    joliver
    joliver is offline
    Registered User
    Join Date
    09-11-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Pulling Data to separate sheets based on one column

    Ah I got it had to apply the CTRL+Shift+Enter to get it to work.

    Thanks a MILLION!!!! Happy Friday
    Reply With QuoteRegister To Reply
  11. 10-04-2013, 05:14 PM #11
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Pulling Data to separate sheets based on one column

    You're welcome. I'm glad that you got it going.

    Have a great weekend.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Pulling Data based on Column and Row names
    By Ryguy786 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-02-2013, 03:14 PM
  2. Sort data to separate sheets based on criteria
    By kmahan71 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 10-11-2012, 05:03 PM
  3. Pulling data into cells from Workbook A - B, based on the presence of data in the column.
    By thetabacon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-01-2012, 12:29 PM
  4. Copy and paste data to separate sheets based on mutiple criteria
    By Ricker090 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-21-2011, 03:48 PM
  5. Extract data to separate sheets based on separate criteria
    By ryanw327 in forum Excel General
    Replies: 4
    Last Post: 11-22-2010, 12:57 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1