Within the same xls file.

I want my new sheet to check a set column in sheet 1 (column W) in this
column I have dates. I want my new sheet (4) to check that column and find
any dates within 30 days of opening the file of this new sheet (for every
time its opened). Then I would like that row that meets this condition to be
pulled over to my new sheet. Ideally Id like to be picky about the
information from that row and not pull the entire row. Am I looking at
something too hard to pull off?

Thanks for any tips

A simple formulas play can achieve this dynamically for you

Let's say your source data is in Sheet1's cols W to Z,
data from row1 down, where col W contains real dates

In another sheet,
Set up a criteria col pointing to Sheet1's col W to monitor/flag it like
this, using TODAY. I'll presume your spec "within 30 days" to mean TODAY +/-
15 days, inclusive (you can easily adapt the criteria to suit)

In A1:
=IF(Sheet1!W1="","",IF(AND(Sheet1!W1<=TODAY()+15,S heet1!W1=TODAY()-15),ROW(),""))
Copy A1 down to cover the max expected extent of Sheet1's col W, say down to
A300? Minimize/hide away col A.

Then to extract the lines over,
In B1:
=IF(ROW()COUNT($A:$A),"",INDEX(Sheet1!W:W,SMALL($ A:$A,ROW())))
Copy B1 to E1, fill down. Format col B as dates. Cols B to E will return
only the lines from Sheet1's cols W to Z where the dates in col W are "within
30 days" ie TODAY +/- 15 days, with all lines neatly bunched at the top.
Lines with duplicate dates, if any, satisfying the criteria pose no problem,
all will appear in the same relative order that they are within Sheet1

And to achieve this:
.. Ideally Id like to be picky about the information from that row
and not pull the entire row.
just amend the INDEX part of the expressions in B1 to E1 (after you copy
across, before filling down), viz amend: INDEX(Sheet1!W:W ... ,
INDEX(Sheet1!X:X ... as desired depending on which col/s you want to pull
over from Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"cjtnoobexceller" wrote:
Within the same xls file.

I want my new sheet to check a set column in sheet 1 (column W) in this
column I have dates. I want my new sheet (4) to check that column and find
any dates within 30 days of opening the file of this new sheet (for every
time its opened). Then I would like that row that meets this condition to be
pulled over to my new sheet. Ideally Id like to be picky about the
information from that row and not pull the entire row. Am I looking at
something too hard to pull off?

Thanks for any tips

First of all thank you for your help Max. I have gotten pretty far with this
but i have run into some trouble. I was able to setup the conditional data
pretty easily to pull over what columns I wanted... but I am running into a
problem. What I wanted was to run my date forward 30 days out from the date
I access the sheet. So here is the formula i used (modified from your
suggestion):

=IF(Sheet1!W2="","",IF(Sheet1!W2=TODAY()+30,ROW() ,""))

So what happens when that comes over is I get dates into 2009. Now when I
swap that to a < I do manage to pull dates back 30 days. But for some
reason that formula wont pull dates that are 30 days in advance. If you cant
tell I am trying to setup projects coming up due within 30 days to this new
sheet. I am so close I can taste it but I am hitting this hang up now. Is
the formula off? Or perhaps the dates being entered into my source sheet
arent correctly formatted? But if that was the case I shouldnt be able to
pull them over for 30 days prior... At any rate, thanks again for your help.

"Max" wrote:

A simple formulas play can achieve this dynamically for you

Let's say your source data is in Sheet1's cols W to Z,
data from row1 down, where col W contains real dates

In another sheet,
Set up a criteria col pointing to Sheet1's col W to monitor/flag it like
this, using TODAY. I'll presume your spec "within 30 days" to mean TODAY +/-
15 days, inclusive (you can easily adapt the criteria to suit)

In A1:
=IF(Sheet1!W1="","",IF(AND(Sheet1!W1<=TODAY()+15,S heet1!W1=TODAY()-15),ROW(),""))
Copy A1 down to cover the max expected extent of Sheet1's col W, say down to
A300? Minimize/hide away col A.

Then to extract the lines over,
In B1:
=IF(ROW()COUNT($A:$A),"",INDEX(Sheet1!W:W,SMALL($ A:$A,ROW())))
Copy B1 to E1, fill down. Format col B as dates. Cols B to E will return
only the lines from Sheet1's cols W to Z where the dates in col W are "within
30 days" ie TODAY +/- 15 days, with all lines neatly bunched at the top.
Lines with duplicate dates, if any, satisfying the criteria pose no problem,
all will appear in the same relative order that they are within Sheet1

And to achieve this:
.. Ideally Id like to be picky about the information from that row
and not pull the entire row.
just amend the INDEX part of the expressions in B1 to E1 (after you copy
across, before filling down), viz amend: INDEX(Sheet1!W:W ... ,
INDEX(Sheet1!X:X ... as desired depending on which col/s you want to pull
over from Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"cjtnoobexceller" wrote:
Within the same xls file.

I want my new sheet to check a set column in sheet 1 (column W) in this
column I have dates. I want my new sheet (4) to check that column and find
any dates within 30 days of opening the file of this new sheet (for every
time its opened). Then I would like that row that meets this condition to be
pulled over to my new sheet. Ideally Id like to be picky about the
information from that row and not pull the entire row. Am I looking at
something too hard to pull off?

Thanks for any tips

.. projects coming up due within 30 days ..

To get the above, think you need to revise your criteria formula in the top
cell to this:
=IF(Sheet1!W2="","",IF(AND(Sheet1!W2=TODAY(),Shee t1!W2<=TODAY()+30),ROW(),""))

And should the above be placed in say, A2 down (instead of A1 down), you'd
need to tweak the extract formula in B2 across slightly to this:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet1!W:W,SM ALL($A:$A,ROWS($1:1))))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"cjtnoobexceller" wrote:
First of all thank you for your help Max. I have gotten pretty far with this
but i have run into some trouble. I was able to setup the conditional data
pretty easily to pull over what columns I wanted... but I am running into a
problem. What I wanted was to run my date forward 30 days out from the date
I access the sheet. So here is the formula i used (modified from your
suggestion):

=IF(Sheet1!W2="","",IF(Sheet1!W2=TODAY()+30,ROW() ,""))

So what happens when that comes over is I get dates into 2009. Now when I
swap that to a < I do manage to pull dates back 30 days. But for some
reason that formula wont pull dates that are 30 days in advance. If you cant
tell I am trying to setup projects coming up due within 30 days to this new
sheet. I am so close I can taste it but I am hitting this hang up now. Is
the formula off? Or perhaps the dates being entered into my source sheet
arent correctly formatted? But if that was the case I shouldnt be able to
pull them over for 30 days prior... At any rate, thanks again for your help.