+ Reply to Thread
Results 1 to 5 of 5

Formulas to pull data from one worksheet to another

  1. #1
    Registered User
    Join Date
    03-29-2014
    Location
    Manassas, Va
    MS-Off Ver
    Excel 2013
    Posts
    3

    Formulas to pull data from one worksheet to another

    Ive melted my brain.

    I know it can be done but I'm just not getting the Macro right.

    Problem: I have the main worksheet with all the data. I want to be able to type a number in as a ID and have it transfer the data from worksheet 1 to worksheet 2...

    Help..

  2. #2
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Formulas to pull data from one worksheet to another

    One way using non-array formulae (faster, easier to understand)
    Assume Sheet1 is the main/source worksheet, with IDs running in A2 down, other cols of data to the right

    In Sheet2,
    Assume the ID of interest will be input in A2
    In B2: =IF(Sheet1!A2="","",IF(Sheet1!A2=$A$2,ROW(),""))
    In C2: =IF(ROWS($1:1)>COUNT($B:$B),"",INDEX(Sheet1!A:A,SMALL($B:$B,ROWS($1:1))))
    Copy C2 across by as many cols desired, say to E2. Then select B2:E2, fill down to the last row of source data expected in Sheet1, eg down to row 100?. Hide/Minimize col B. Cols C to E will auto-return the results for the ID in A2, all neatly packed at the top
    --------------------------------------------------
    Success? Wave it, whack the little star at the bottom left of my responses

  3. #3
    Registered User
    Join Date
    03-29-2014
    Location
    Manassas, Va
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Formulas to pull data from one worksheet to another

    Max,

    What I am looking to do is on the worksheet Baker's Double in Colume A I want to be able to type in a bowlers ID and have it go to worksheet Qualifying Round find the Bowl ID I typed and have it transfer that data over. I only need it to transfer the bowlers information's and their average. Which is Column B and C

    Bowler # Name Average
    1 169

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formulas to pull data from one worksheet to another

    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Formulas to pull data from one worksheet to another

    Earlier solution caters for multiple matches of ID in source colA.
    For single, unique ID match n return, you could try something like this
    =Index(QRcolB,match(ID,QR_ID col,0))

    Sent from my RM-941_apac_sea_275 using Tapatalk
    Max
    Singapore

  6. #6
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Formulas to pull data from one worksheet to another

    The 2 expressions to extract cols B and C from the worksheet Qualifying Round should look like this:
    =INDEX('Qualifying Round'!B:B,MATCH(A2,'Qualifying Round'!A:A,0))
    =INDEX('Qualifying Round'!C:C,MATCH(A2,'Qualifying Round'!A:A,0))
    In the above it is assumed that the IDs are in col A in "Qualifying Round" and in the sheet: Baker's Double
    -------------------------
    Success at last? Wave it, hit the little star at the bottom left of my responses

+ 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. Using Array formulas to pull data from one tab to another
    By mobie86 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2013, 11:19 AM
  2. setting up formulas to pull data from another sheet.
    By Excel is Confusing in forum Excel General
    Replies: 6
    Last Post: 05-22-2011, 11:43 PM
  3. Excel Tables - Formulas to pull data from
    By Hans of CO in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-21-2009, 07:30 PM
  4. Replies: 1
    Last Post: 03-09-2009, 07:50 AM
  5. formulas pull the data:Links to other workbooks
    By catalyst in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-06-2005, 03:34 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