Advanced Filter to New Spreadsheet

geli7

New member
Joined
Jul 29, 2011
Messages
4
Reaction score
0
Points
0
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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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...
 

geli7

New member
Joined
Jul 29, 2011
Messages
4
Reaction score
0
Points
0
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!
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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)
 

geli7

New member
Joined
Jul 29, 2011
Messages
4
Reaction score
0
Points
0
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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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. :)
 
Top