I know, I know - I shouldn't even be using Excel!
September 26, 2008 11:52 AM Subscribe
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?
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?
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
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
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
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
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
Best answer: 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
posted by junesix at 2:21 PM on September 26, 2008 [1 favorite]
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]
Response by poster: It looks like a pivot table is exactly what I needed! Thanks junesix!!
posted by CXImran at 9:01 PM on September 27, 2008
posted by CXImran at 9:01 PM on September 27, 2008
This thread is closed to new comments.
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