+ Reply to Thread
Results 1 to 3 of 3

Formula to pull rows of data based on a column cell value (Criteria)

  1. #1
    Registered User
    Join Date
    02-19-2013
    Location
    Wisconsin, WI
    MS-Off Ver
    Excel 2007
    Posts
    37

    Formula to pull rows of data based on a column cell value (Criteria)

    Curious if it's possible to create a formula which pulls rows of data into another worksheet in the same workbook (Have formulas already in place in the desire worksheets). My intent is to auto update the worksheet when I add more data into the data source and avoid using a macro. Would like to organize based on Column "I" or PONumber. Attached is my test data. First worksheet is my datasource and the second worksheet is what I want pulled based on one (PONumber) criteria in column "I".

    Any recommendation is welcome and if this is not possible or ideal please shut me down.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Formula to pull rows of data based on a column cell value (Criteria)

    You can do this with 3 basic formulae. In the attached file I have set up a new sheet called Selected_records and put this formula in T2 of the DataSource sheet:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This is copied down beyond your data (the hyphens show where it has been copied to), and this sets up a sequence of records which match the PO number chosen in the Selected_records sheet.

    In the Selected_records sheet you can select the PO Number from the drop-down list in cell I1. This list is shown in column V - I extracted unique values using Advanced filter and then sorted the list. Then I put this formula in cell T4:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    which finds the row in the DataSource sheet where the first matching record appears - when this is copied down it will find the row of the second matching record, then the third, and so on.

    In cell A4 I put this formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    which returns the data from column A for the record whose row is given in cell T4. This formula can be copied across, and it will return data from column B, then column C etc. I made a slight amendment to the formula in M4 to this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    which will return a blank if there is no data in column M of the DataSource sheet (as most of the dates seem to be missing in the example file). The cell is formatted as a date, and then copied across to S4. Cell R4 has been formatted as General.

    Then the formulae from cells A4:T4 can be copied down as far as you think you need them - I've copied down to row 30, as can be seen in column T. In your real file you will need to ensure that the formula in column T of the DataSource sheet is copied down beyond the amount of data that you have.

    So, all you need to do is select a PO number in cell I1, and the display will change immediately to show that PO's records.

    Hope this helps.

    Pete
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-19-2013
    Location
    Wisconsin, WI
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Formula to pull rows of data based on a column cell value (Criteria)

    Thank you Pete, I appreciate the time you've spent helping me out and your knownledge if profound. Your method is very organize and I like it a lot. With your method, I'm not entirely sure if I can proceed with doing a summary PO sheet. I have attached what I would want on the summary sheet. Based on each PO number I want to sum up the values (total price, column "K") and then subtract it to an initial amount and see the remainder of the PO. Thank you in advance!
    Attached Files Attached Files
    Last edited by PaulLor89; 04-30-2013 at 11:36 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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