I know, I know - I shouldn't even be using Excel!

September 26, 2008 11:52 AM

ExcelFilter: How can I create a spreadsheet that automatically copies a certain type of cell from one column to another?

So I've got this spreadsheet where I paste a set of values into Columns 'A' and 'B' regularly. Column 'A' has a set of names and Column 'B' has a set of dates.

What I want to do is to be able to paste new data regularly into columns 'A' and 'B', but have columns 'C' and 'D' report only data that was flagged as given in the last 14 days.

I COULD just paste it in the order that I get the data, but unfortunately more often than not the data I'm working with isn't given to me chronologically. What I'd like to do is have all the data dynamically update to the last 14 days so that when I generate a certain graph it only uses the data that I want.

Do any Excel wizards know of good ways to do this?

posted by CXImran to computers & internet (7 comments total)

I'm not 100% sure what you're trying to do, but I think this might help:

In column C (row 1 for the example), use formula =if((TODAY()-B1)<=14,A1,"")
posted by Perplexity at 12:39 PM on September 26, 2008

for A1 = name, B1 = date, C1 = recent names, D1 = recent dates

formula as follows:
in column C:
=IF(B1<>
in column D:
=IF(B1<(NOW()-14),"",B1)
posted by Chris4d at 12:42 PM on September 26, 2008

meh, use perplexity's!
posted by Chris4d at 12:43 PM on September 26, 2008

apologies for the formatting getting messed up, but perplexity's works anyway. You might also input the data and then sort it according to column B so that you can always move the most recent data to the top.
posted by Chris4d at 12:45 PM on September 26, 2008

CXImran... could you clarify? Is column B going to give the right date to test "last 14 days" against?

The way I read the question, column B will not necessarily be the date the data was added to the sheet.

If that's the case, you're going to need another column to test against which you would have to populate with the current date whenever adding data to the spreadsheet. You could probably create a macro to automatically fill in any blanks in this column with today's date to save some time...
posted by saintsguy at 12:48 PM on September 26, 2008

It sounds like columns A and B are the source columns from which the poster copies certain rows (data in the last 14 days) into columns C and D. Then the data in columns C and D are used to create a chart.

I think what you really want is a PivotChart. A PivotChart is a chart that can be dynamically updated based on the fields and conditions you specify. It's a bit hard to describe so check out the example on that page.

What you'll want to do is create a column E that checks if the dates in column B are in the last 14 days and if so, returns a value of 1 . Something like =IF((TODAY()-B1)<>. Then when you create the PivotChart, use the column E variable as a filter so that you only use rows with a column E value of 1.
posted by junesix at 2:21 PM on September 26, 2008 [1 favorite]

It looks like a pivot table is exactly what I needed! Thanks junesix!!
posted by CXImran at 9:01 PM on September 27, 2008