+ Reply to Thread
Results 1 to 9 of 9

List the Data from another worksheet with specific value

  1. #1
    Registered User
    Join Date
    07-28-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    80

    List the Data from another worksheet with specific value

    Hi Everyone,


    I want to know the macro that list data to a "Report" worksheet from "DATATABLE" worksheet with my specified date.

    I planned to used a command button to run the macro.

    Attached is the sample worksheet.

    Thanks thanks..
    Attached Files Attached Files

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: List the Data from another worksheet with specific value

    So if the Schedule Dates on DATATABLE Worksheet, match B3 Cell of Report worksheet, you want to get the whole row?
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    07-28-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: List the Data from another worksheet with specific value

    Quote Originally Posted by NeedForExcel View Post
    So if the Schedule Dates on DATATABLE Worksheet, match B3 Cell of Report worksheet, you want to get the whole row?
    Yes! That's what i wanted to...

  4. #4
    Registered User
    Join Date
    07-28-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: List the Data from another worksheet with specific value

    anyone have some ideas about this?

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: List the Data from another worksheet with specific value

    Why do you need VBA and an button for this? You could use the ARRAY formula below to pull the data automatically...
    B6=IFERROR(INDEX(DATATABLE!A:A,SMALL(IF(DATATABLE!$F$2:$F$5=REPORT!$B$3,ROW(DATATABLE!$A$2:$A$5)),ROWS($A$1:A1))),"")
    ...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.

    Then copy down and across

    (note, the use of NOW() included the current time ad well as the date, so nothing will match exactly between B3 and column F. I changed B3 to 1 of your dates in column F to test...

    A
    B
    C
    D
    E
    F
    3
    Date :
    02-May-16
    <<-- Please verify if it is the exact date as of today…
    4
    5
    Last Name
    First Name
    MI
    ATM#
    PIN#
    6
    Sample1 Sample1 S2 With Meeting
    0.375
    7
    Sample2 Sample2 S2 With Meeting
    0.666666667
    8
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: List the Data from another worksheet with specific value

    Hello Chergian,

    If you would still prefer a macro, then the following code may help:-


    Please Login or Register  to view this content.
    Assign the code to the button in the Report sheet.

    The code basically checks that the dates in Column F of the DataTable sheet are dates and if a date matches the date in cell B3 in the Report sheet, then the relevant row of data from the DataTable sheet will be transferred to the Report sheet.

    The Report sheet is refreshed with each transfer so that there will not be any duplicates and you can keep all data in the DataTable sheet.

    Ensure that all dates are formatted the same in both sheets.

    I've attached my test work book for you to peruse at the following link (this site's file uploader does not appear to work for me):-

    https://www.dropbox.com/s/g3hido62x7...gian.xlsm?dl=0

    In the Report sheet, you will see that I have changed the formula in B3 to

    Please Login or Register  to view this content.
    I hope that this helps.

    Cheerio,
    vcoolio.
    Last edited by vcoolio; 05-01-2016 at 05:47 AM. Reason: Typo

  7. #7
    Registered User
    Join Date
    07-28-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: List the Data from another worksheet with specific value

    Thank you so much!!! it works!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: List the Data from another worksheet with specific value

    Quote Originally Posted by chergian View Post
    Thank you so much!!! it works!
    just curious, which 1 worked for you?

  9. #9
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: List the Data from another worksheet with specific value

    Good day FD,

    Being a simpler option, I would say that the OP has used your formula.


    Just an aside, FD:-

    The site's file uploader has not worked for months on my end. I've tried it on three different browsers with no luck. All my settings are fine. I've sent a message or two to Admin over the last few months but no reply. Would you be able to enlighten me or pass the query on? Thanks FD.

    Cheerio,
    vcoolio.

+ 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. Replies: 0
    Last Post: 03-29-2015, 08:11 PM
  2. List worksheet names in active worksheet By Excluding Specific Sheets
    By jagadeesh.rt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-17-2014, 10:22 AM
  3. [SOLVED] Automatically copy specific data from one spreadsheet to a different specific worksheet
    By kelcowcow in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-14-2014, 01:47 AM
  4. Copying specific data to a specific field in a worksheet macro
    By bradpeh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2013, 01:52 AM
  5. Replies: 0
    Last Post: 10-20-2011, 03:21 PM
  6. Transfer specific data on specific worksheet using userform
    By kriscar_44 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-17-2010, 04:03 AM
  7. Formula to Link to Specific List on Another Worksheet
    By jdurrmsu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-05-2006, 02:30 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