+ Reply to Thread
Results 1 to 6 of 6

Extracting data from another sheet

  1. #1
    Registered User
    Join Date
    03-23-2017
    Location
    canada
    MS-Off Ver
    365
    Posts
    39

    Extracting data from another sheet

    How can I extract rows matching some criteria to different sheet?

    Pivot table requires summarizing data by I just want the raw data from the main sheet.
    Filter only works in the current sheet and requires user to specify filter criteria.
    vlookup requires a formula for each row but how do I know how many rows will be selected


    For example:

    The main "Data" sheet contains:

    salesperson product qty saledate
    Bob ddr3 10 12-mar-17
    Bill ddr4 2 1-apr-16
    Jane ddr 11 14-may-16
    Bob rambus 44 23-jun-16

    I would like to create a sheet for each salesperson (can be static)

    "Bob" sheet:

    ddr3 10 12-mar-17
    rambus 44 23-jun-16

    "Bill" sheet:
    ddr4 2 1-apr-16

    So, all the salesperson has to do is click on the tab corresponding to his sheet to view his sales.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Extracting data from another sheet

    Hi Martin and eel come to the forum.

    I think you need to learn Advanced Filters. They will do what you want.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    03-23-2017
    Location
    canada
    MS-Off Ver
    365
    Posts
    39

    Re: Extracting data from another sheet

    Thanks, I looked into tat but it requires user interaction. I just want the user to be able to click on his tab and his sheet will then appear populated from the main data sheet.

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Extracting data from another sheet

    Assuming you data is in A2:D6 in data name sheet.

    in Sheet name Bob there is already mention head name product qty saledate

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


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Check the attached file.

    Once you will change sheet name data will automatically update as per sheet name

    Hope This will help you
    Attached Files Attached Files
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  5. #5
    Registered User
    Join Date
    03-23-2017
    Location
    canada
    MS-Off Ver
    365
    Posts
    39

    Re: Extracting data from another sheet

    Thanks, That is a clever formula!

    I performed a 'fill down' with the formula and it works

    I did not need to enter CTRL-SHIFT-ENTER, the braces were already there

  6. #6
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Extracting data from another sheet

    Quote Originally Posted by martik777 View Post
    Thanks, That is a clever formula!

    I performed a 'fill down' with the formula and it works

    I did not need to enter CTRL-SHIFT-ENTER, the braces were already there
    Glad it help you.

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

+ 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. [SOLVED] Need help extracting data from other sheet
    By dlevisay in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-21-2014, 08:42 AM
  2. Replies: 0
    Last Post: 08-03-2014, 03:32 AM
  3. Extracting data from a sheet
    By huubrk in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-08-2013, 06:48 AM
  4. Extracting Sum in one sheet with daily data being refreshed in another sheet.
    By Paritosh negi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-03-2013, 07:47 AM
  5. Replies: 5
    Last Post: 07-10-2013, 02:33 PM
  6. Extracting data from a row on one sheet into another sheet based on a unique cell
    By roshanvarghese in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-23-2013, 03:49 AM
  7. extracting unknown data from sheet 1 to sheet 2
    By wurugu in forum Excel - New Users/Basics
    Replies: 11
    Last Post: 12-02-2011, 11:58 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