Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with index and match
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$10 34=Sheet1!$A$3)*(Data!$B$2:$B$1034=Sheet1!$A6)*(Da ta!$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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions | |||
Index and Match | Excel Worksheet Functions |