+ Reply to Thread
Results 1 to 7 of 7

Formula To Populate Filtered Data From Another Tab

  1. #1
    Forum Contributor
    Join Date
    02-09-2004
    Location
    Cardiff - Wales - UK
    MS-Off Ver
    2013
    Posts
    474

    Question Formula To Populate Filtered Data From Another Tab

    Hi,

    I have a workbook with three worksheets i.e. ‘Summary’, ‘Achievements Data’ and ‘Overdue Data’.

    What I require are formulas (vlookup?) on the ‘Summary’ tab which automatically pulls through the filtered data from the Achievements Data’ and ‘Overdue Data’ tabs regardless of what columns I have filtered on (and for whatever criteria) and enters this data in the ‘Achievements’ and Milestones sections on the Summary’ tab

    I have attached ‘the Example Data.xlsx’ file and filtered on random data for this exercise on the ‘Achievements Data’ and ‘Overdue Data’ tabs and manually entered it into the ‘Achievements’ and Milestones section on the Summary’ tab as an example

    Any assistance in this matter would be greatly appreciated


    Regards


    Bob

    N.B. Excel version 2010
    Attached Files Attached Files
    Rob

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Formula To Populate Filtered Data From Another Tab

    Hello Bob
    I've amended your data sheets as Excel Tables and added helper columns with the SUBTOTAL function to allow the formulas on the Summary sheet to return only the filtered data. Perhaps this would do what you require.

    DBY
    Attached Files Attached Files
    Last edited by DBY; 12-10-2015 at 12:50 PM. Reason: Amended attachment

  3. #3
    Forum Contributor
    Join Date
    02-09-2004
    Location
    Cardiff - Wales - UK
    MS-Off Ver
    2013
    Posts
    474

    Re: Formula To Populate Filtered Data From Another Tab

    DBY

    FANTASTIC !!! Many many thanks for your reply it is exactly what I wanted.

    If you're ever passing Cardiff I'd gladly buy you a Pint !

    Many thanks


    Rob

  4. #4
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Formula To Populate Filtered Data From Another Tab

    Hello Bob
    You may have to belay that offer of a pint. I've tested the example I supplied a little more and I'm not sure it's behaving correctly. I've noticed that when adding new records to the tables the SUBTOTAL function references are skipping cells and not returning the correct rows, I can't correct this behaviour at the moment, it would be interesting if any one else has had this problem with Tables.

    I've changed the formulas and I think it's behaving better but test it out. See new amended example.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    02-09-2004
    Location
    Cardiff - Wales - UK
    MS-Off Ver
    2013
    Posts
    474

    Re: Formula To Populate Filtered Data From Another Tab

    DBY,

    well spotted and thanks for the update, I've changed the status of this post back to unsolved until I can test and hopefully get some more feedback from other contributors

    Thanks Again

    Regards


    Rob

  6. #6
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: Formula To Populate Filtered Data From Another Tab

    I've used this method harvested from the web with success, but don't have the time right now to mess with your data. Thought I'd dump in my notes and maybe you could adjust ranges and apply as best you can.

    1. Re: Lookup only visible cells
    Assuming that A1:C10 contains your data, the first row contains your headers/labels, A15 contains your lookup value, and you want to return the corresponding value in Column C, try...

    =VLOOKUP(A15,IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1)),A2:C10),3,0)

    ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

    Pete

  7. #7
    Forum Contributor
    Join Date
    02-09-2004
    Location
    Cardiff - Wales - UK
    MS-Off Ver
    2013
    Posts
    474

    Re: Formula To Populate Filtered Data From Another Tab

    DBY & Pete,

    many thanks for your replies they are very much appreciated. I will run both your solutions in parallel to test and tweak to match my data

    Thanks again regards


    Rob

+ 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. Populate Worksheet # 2 based on Filtered Worksheet # 1 Data
    By parkinson5 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2014, 07:49 PM
  2. Replies: 11
    Last Post: 05-13-2014, 12:39 PM
  3. Populate a textbox on Userform with data from a filtered range using VBA
    By newbi004 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2014, 04:20 AM
  4. [SOLVED] populate userform with filtered data
    By crossy5575 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-24-2012, 11:47 AM
  5. Replies: 3
    Last Post: 03-23-2010, 06:44 PM
  6. Need help with formula or macro to populate one sheet with data filtered from another
    By leveleyed in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2010, 12:59 PM
  7. Populate combo box from filtered data
    By brodybear in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-06-2007, 04:52 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