Thx Max your so good. What i ended up doing was keep all seven sheets but i
added another and condenced all seven into one, then put the formula in with
just changing the sheet.
YES I HAVE YET ANOTHER QUESTION!!!!!!!!!!!!!!!!YEP....
I WAS TRYING TO SORT THE REPORT PAGE BY DATE AND FAILED. WHAT IAM TRYING TO
ACC. IS TO GET THE SHEET TO AUTO SORT BY DATE. W/ OLDEST DATE COMPARED TO
TODAY TO BE ON TOP THEN SO ON.
THX- MUCHO!, YOUR P I M P
"Max" wrot:
Ah .. looks like the specs have expanded somewhat <g
The set-up suggested earlier assumes a *single* source Sheet1 where data
rows with expired dates are to be extracted into another Sheet2 (the # of
cols to be extracted over is not a prob)
If you could re-design your set-up to be into a single source
instead of 7 separate sheets, or consider
performing a daily manual copy paste data operation
from all 7 source sheets into a single source "Sheet1"
(just copy *all* data rows from each of the 7 source sheets and "stack" them
up block by block below each other in any order in Sheet1), then this'll
work ..
In Sheet1 (< the sheet with consolidated data)
-------------
Dates are in col J, J2 down
Put in say, P2: =IF(J2="","",IF(J2<TODAY(),ROW(),""))
Copy P2 down by a safe "max" number of rows
to cover the consolidated data expected in cols A to O,
say down to P2000
(think you got this part working earlier ..)
In Sheet2
-------------
CopyPaste the headers for cols A to O
from Sheet1 into A1:O1
Put in A2:
=IF(ISERROR(MATCH(SMALL(Sheet1!$P:$P,ROW(A1)),Shee t1!$P:$P,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$P:$P,ROW(A1)),Sheet1! $P:$P,0)-1,COLUMN(A1)-1))
Copy across to O2, then fill down to O2000
The formulas in Sheet2 will extract only rows
where the dates in col J have expired in Sheet1
The "-1" in ..COLUMN(A1)-1..
(this is the cols param in the OFFSET formula)
is just an arithmetic adjustment made to
ensure that the OFFSET formula returns the correct results
in the start cell A2, and as we copy A2 across to O2
"COLUMN(A1)-1" in A2 will return zero
as we copy A2 across to B2, it'll change to
"COLUMN(B1)-1" in B2 which returns 1, and so on
(we're using COLUMN() as the incrementer as we copy across)
In A2, the zero in the cols param means extract from col zero
i.e. col A, the same column as the OFFSET's base reference
cell: $A$1.
In B2, "1" in the cols param will mean extract from one col to the right of
col A, i.e. from col B. And so on.
--
If you'd like to have a sample file via private email,
just post a "readable" email add in response here
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"Markitos" wrote in message
...
THX VERY MUCH MAX!
I tried to put this into my wookbook I MANAGED TO GET THE FIRST FORMULA TO
WORK,BUT THE SECOND IS CONFUSING ME.
IN MY WOOKBOOK I HAVE 8 SHEETS TOTAL . 7 are sheets with dates and one is
to
be the report sheet.
on the sheets the dates are entered on column J, and I have the formula on
column P. (THIS FORMULA IS WORKING FINE).
one the reports sheet it is formatted the same as the other sheets. I
tried
to insert the formula with cell/ # changes but it wouldnt work and im
confused with the -1's in the formula.
Also Im unsure if it would be different if I have 7 sheets which I want to
pull from. Also I was trying to have the the info from cells A THROUGH O
COPIED ONTO THE REPORTS PAGE.
I KNOWN THIS QUITE A CHALLENAGE, BUT ANY HELP OR ADVICE WOULD BE HELPFUL.
THANKS-AGAIN
|