Results 1 to 7 of 7

Thread: Advanced Filter to New Spreadsheet

  1. #1

    Advanced Filter to New Spreadsheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to figure out how to filter data in one spreadsheet and have the results display live on another. Here's a description:

    Spreadsheet one has about 200 rows of data, columns A through L. Let's say columns C and D have dates in them. What I'd like is that for every row, if the date in column C is less than today's date (in the past) and Column D is blank, for that entire row to appear in a different spreadsheet (or worksheet within the same spreadsheet if that's not possible). Also I would like this to update live....so if I enter a date into Column D on spreadsheet 1, that row would no longer appear on the new spreadsheet.....or if a few days go by and the date in Column C is now in the past (and Column D remains blank), that row would now appear in the new spreadsheet.

    Any help would be appreciated. Thank you.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Hi Geli, and welcom to the forum!

    Curious, what version of Excel are you using? If your data is in the correct form, this sounds like an ideal application for a PivotTable...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Quote Originally Posted by Ken Puls View Post
    Hi Geli, and welcom to the forum!

    Curious, what version of Excel are you using? If your data is in the correct form, this sounds like an ideal application for a PivotTable...
    Thanks for the response. I'm using 2007. You may be right. Truth be told I have an intermediate grasp on formula writing, but haven't done much with pivot tables. Not sure how to go about it, but certainly willing to give it a shot!

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    The key is the format of your data. It needs to be in a table format before you start with your pivot table. But once done, the pivot table allows you to drill down to just the day you want very easily, and upon refresh it is always in sync with your data. (We can even automate the refresh.)

    Do you have a sample of your data that you can post? (A mock up is fine)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    So what I'd want is if the Sale Date column is prior to today, and the Action Date column is blank, for the entire row to appear in a different spreadsheet or worksheet.

    Thanks!

    #2011BLCodeSale DateAction DateCompletion Date1Scottsdale AZA323/23/20112Scottsdale, AZB643/22/20116/17/20113Tucson, AZCB343/22/20114Phoenix, AZD123/22/20114/20/20115/4/20115Miami, FLCB422/2/20116Weston, FLE761/31/20114/25/20116/22/20117Miami, FLE652/2/20113/8/20114/1/20118Miami, FLR552/1/2011

    My apologies, what's the best way to post a sample. I tried a copy and paste, obviously with poor results.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    If you double click the "+ Reply to Thread" button, (or click "Go Advanced" at the bottom right of the reply box) you'll be taken to the Advanced window. You can upload a workbook there.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Sample.xlsx

    Hope that works...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •