struggling with index/match

[edwardpestian]

Not sure how to explain. I want to look up data in a row/cell based on multiple criteria: Date and label. Three consecutive columns contain related data with a label above that data and a three column merged cell above that containing the date. I would like to look up data in a particular row/cell based on both the date and column label. Each time I change the date in the reference cell, the data needs to change to the corresponding data in the three columns below. Across the entire worksheet is each date of the month. I have attached the workbook for review. I would like to be able to change the date in the daily tab and have the pink cells in the data table change to the appropriate date.

Thanks a million!

[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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"edwardpestian" <[email protected]>
wrote in message
news:[email protected]...
>
> Not sure how to explain. I want to look up data in a row/cell based on
> multiple criteria: Date and label. Three consecutive columns contain
> related data with a label above that data and a three column merged
> cell above that containing the date. I would like to look up data in a
> particular row/cell based on both the date and column label. Each time
> I change the date in the reference cell, the data needs to change to
> the corresponding data in the three columns below. Across the entire
> worksheet is each date of the month. I have attached the workbook for
> review. I would like to be able to change the date in the daily tab
> and have the pink cells in the data table change to the appropriate
> date.
>
> Thanks a million!
>
>
> +-------------------------------------------------------------------+
> |Filename: NewDailyCall.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4691 |
> +-------------------------------------------------------------------+
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=535815
>

[edwardpestian]

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

Thanks again, your saving me a lot of hair.

EP

[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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"edwardpestian" <[email protected]>
wrote in message
news:[email protected]...
>
> It only works for the four rows that were in pink. I need it to
> continue down without having to replicate the column headers for each
> game. Ex 109, 110, 111 in data.
>
> Thanks again, your saving me a lot of hair.
>
> EP
>
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=535815
>

[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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"edwardpestian" <[email protected]>
wrote in message
news:[email protected]...
>
> It only works for the four rows that were in pink. I need it to
> continue down without having to replicate the column headers for each
> game. Ex 109, 110, 111 in data.
>
> Thanks again, your saving me a lot of hair.
>
> EP
>
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=535815
>

[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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"edwardpestian" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=535815
>