struggling with index/match

[edwardpestian]

Not sure how to explain. I want to look up data in a row/cell based o
multiple criteria: Date and label. Three consecutive columns contai
related data with a label above that data and a three column merge
cell above that containing the date. I would like to look up data in
particular row/cell based on both the date and column label. Each tim
I change the date in the reference cell, the data needs to change t
the corresponding data in the three columns below. Across the entir
worksheet is each date of the month. I have attached the workbook fo
review. I would like to be able to change the date in the daily ta
and have the pink cells in the data table change to the appropriat
date.

Thanks a million

+-------------------------------------------------------------------
|Filename: NewDailyCall.zip
|Download: http://www.excelforum.com/attachment.php?postid=4691
+-------------------------------------------------------------------

 

[Max]

One way ..

In Data,

Put in CX5:
=IF(Date=0,"",OFFSET($D$5:$D$8,,MATCH(Date,$E$3:$CW$3,0)))

Put in CY5:
=IF(Date=0,"",OFFSET($D$5:$D$8,,MATCH(Date,$E$3:$CW$3,0)+2))

Select CX5:CY5, copy down to CY8

 

[edwardpestian]

It only works for the four rows that were in pink. I need it t
continue down without having to replicate the column headers for eac
game. Ex 109, 110, 111 in data.

Thanks again, your saving me a lot of hair.

E

 

[Max]

Just change the part: OFFSET($D$5:$D$8, ...
to: OFFSET($D$5:$D$28, ...
in the 2 starting cell formulas in CX5:CY5, then fill down
(since your data is within row5 - row28)

 

[edwardpestian]

Now, in columns CT and CU, I need to sum all the numbers to the left
that correspond to the column headers.

For Example

CT
Drop
= sum of all Drops in that row. In other words, a MTD total of the drop
for that particular row.

I'm almost home..

Thanks again.

EP

 

[Max]

Drop
Put in CT5:
=SUMPRODUCT(--(MOD(COLUMN(E5:CS5),3)=2),E5:CS5)

Need
Put in CU5:
=SUMPRODUCT(--(MOD(COLUMN(E5:CS5),3)=0),E5:CS5)

Win
Put in CV5: =CT5-CU5

Select CT5:CV5, fill down