Any Excel Experts?

Mulder · 21 Jan 2010 at 15:57

I'd really appreciate some help with this problem.

I've got about 7 sheets in an excel file, all rows have dates and other data. I'd like to make a new sheet that auto pulls any rows that have dates in the next ten days? Basically an auto generating to do list.

I've googled around lookups, lookupifs, vlookup etc and am totally lost.

Anyone know?

TIA

tres · 21 Jan 2010 at 16:13

use IF commands?

=IF(Sheet2!B5<TODAY()+10,Sheet2!C5,)

ie. less than 10 days in the future, then pull the value from the adjacent cell, else do nothing

nestle them if you then want to then look in the next sheet if the first one doesn't have anything

Rich_L · 21 Jan 2010 at 16:14

It's pretty crude, but I'd do something that collated all the sheets into a single sheet, then just run a date filter on it.

Mulder · 1 Feb 2010 at 21:42

tres said:
use IF commands?

=IF(Sheet2!B5<TODAY()+10,Sheet2!C5,)

ie. less than 10 days in the future, then pull the value from the adjacent cell, else do nothing

nestle them if you then want to then look in the next sheet if the first one doesn't have anything

I've tried this and it's just says 0. Been working on it all day and its driving me nuts. :mad:

Participant · 1 Feb 2010 at 21:52

Why did you entirely ignore Rich L's suggestion of collating the sheets? At least help us to help you by explaining why you can't just combine all the data into one sheets and pivot / autofilter.

Makhaira · 1 Feb 2010 at 21:53

Is your source data set as Date type? Otherwise the IF statement will fail. Also not sure why C5 isn't B5, and some brackets around the TODAY()+10 wouldn't hurt.

Mulder · 1 Feb 2010 at 22:18

Robbie G said:
Why did you entirely ignore Rich L's suggestion of collating the sheets? At least help us to help you by explaining why you can't just combine all the data into one sheets and pivot / autofilter.

People will be adding to the other sheets all the time and there's a fair amount of data. How would I properly link them so it auto updates?

Antar Bolaeisk · 2 Feb 2010 at 18:06

Is this something like what you want?

It's a bit of a messy solution but I think it does what you're looking for. Firstly it compiles all the lists onto one page and then sorts through them pulling out the relevant ones. If it's what you're looking for I can probably make it sort through the different pages without having to compile them into one list first.

KnightsWhoSayNi · 2 Feb 2010 at 22:39

Its a bit crude but you could link the sheets into access and run a query on the data.

fini · 2 Feb 2010 at 22:48

Mulder said:
People will be adding to the other sheets all the time and there's a fair amount of data. How would I properly link them so it auto updates?

Using a macro to copy the data over and then apply the sort for you would seem the simplest to me.