struggling with index/match
 

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

struggling with index/match
 

One way ..

In Data,

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

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

Select CX5:CY5, copy down to CY8
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"edwardpestian"
wrote in message
news:edwardpestian.26t0yy_1145942101.5059@excelfor um-nospam.com...

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

struggling with index/match
 

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

struggling with index/match
 

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"
wrote in message
news:edwardpestian.26t3ze_1145946020.0911@excelfor um-nospam.com...

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

struggling with index/match
 

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

struggling with index/match
 

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"
wrote in message
news:edwardpestian.26t6an_1145949001.9322@excelfor um-nospam.com...

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