D
Diana Criscione
I am trying to create a worksheet with some weekly status information. I
have two worksheets: one with the daily status information and one that
recaps it with Monday values.
The daily status worksheet (named Data) has information as follows:
(A) (B) (C) (D+...)
location category status dates, such as 10/12/07,
10/15/07, 10/16/07
The data on this sheet is being pulled from an Access database crosstab
query so it will continue to grow (more columns added) as time goes on.
The recap sheet is organized as follows:
User selects a location (in cell A3)
A B C+...
category status monday dates (10/15/07, 10/22/07, 10/29/07,
etc)
The first col with dates starts with the date of 10/15, but the value
"10/22" is based on a formula "=MIN(C5+7, TODAY())" so that the columns on
this sheet will continue with time.
I have got the following formula in the first data row on the recap under
the first date of 10/15/07:
{=INDEX(Data!$F$2:$F$2000,MATCH(1,(Data!$A$2:$A$1034=Sheet1!$A$3)*(Data!$B$2:$B$1034=Sheet1!$A6)*(Data!$C$2:$C$1034=Sheet1!$B6),0))}
This formula returns the correct value for Oct 15 and I've copied the
formula down the column so all recap values are there.
My problem is figuring out how to either modify this formula so that I can
move it to the next date(s) and retrieve the correct value since the data for
10/22/07 will not be in Data!F but rather Data!K -- OR -- modify the formula
to approach the solution in a different way.
I've tried different processes and seem to get the smaller nested formulas
to work correctly, but can't get them to work together. I believe I
understand how index and match work, but most examples are not as complex as
what I'm trying to achieve. Perhaps I'm making this more complicated than it
really is.
One other note, although I have dates here starting with Oct 15th (a
Monday), because the first date is typed, the higher ups could decide to
report on data every Wed and change the starting date in the "10/15/2007"
cell.
Any help would be appreciated! Many many thanks in advance!
Here's a link to a sample file with some data:
http://www.dcappwerx.com/sample.xls
have two worksheets: one with the daily status information and one that
recaps it with Monday values.
The daily status worksheet (named Data) has information as follows:
(A) (B) (C) (D+...)
location category status dates, such as 10/12/07,
10/15/07, 10/16/07
The data on this sheet is being pulled from an Access database crosstab
query so it will continue to grow (more columns added) as time goes on.
The recap sheet is organized as follows:
User selects a location (in cell A3)
A B C+...
category status monday dates (10/15/07, 10/22/07, 10/29/07,
etc)
The first col with dates starts with the date of 10/15, but the value
"10/22" is based on a formula "=MIN(C5+7, TODAY())" so that the columns on
this sheet will continue with time.
I have got the following formula in the first data row on the recap under
the first date of 10/15/07:
{=INDEX(Data!$F$2:$F$2000,MATCH(1,(Data!$A$2:$A$1034=Sheet1!$A$3)*(Data!$B$2:$B$1034=Sheet1!$A6)*(Data!$C$2:$C$1034=Sheet1!$B6),0))}
This formula returns the correct value for Oct 15 and I've copied the
formula down the column so all recap values are there.
My problem is figuring out how to either modify this formula so that I can
move it to the next date(s) and retrieve the correct value since the data for
10/22/07 will not be in Data!F but rather Data!K -- OR -- modify the formula
to approach the solution in a different way.
I've tried different processes and seem to get the smaller nested formulas
to work correctly, but can't get them to work together. I believe I
understand how index and match work, but most examples are not as complex as
what I'm trying to achieve. Perhaps I'm making this more complicated than it
really is.
One other note, although I have dates here starting with Oct 15th (a
Monday), because the first date is typed, the higher ups could decide to
report on data every Wed and change the starting date in the "10/15/2007"
cell.
Any help would be appreciated! Many many thanks in advance!
Here's a link to a sample file with some data:
http://www.dcappwerx.com/sample.xls