Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
edwardpestian
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
edwardpestian
 
Posts: n/a
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
edwardpestian
 
Posts: n/a
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index/Match Multiple Criteria EstherJ Excel Discussion (Misc queries) 2 March 31st 06 12:54 PM
How do I display more than one match in a Index/Match formula? Trish Excel Worksheet Functions 0 September 26th 05 10:21 PM
Index/Match Help singsunnyboy7 Excel Discussion (Misc queries) 1 July 26th 05 10:18 PM
Index/Match to look up a value in one workbook and insert it into. Jean Marie Excel Discussion (Misc queries) 2 February 22nd 05 02:53 PM
Need Function for adding rows on multiple sheets... struggling rookie ;) Thanks. Steve Excel Worksheet Functions 6 November 24th 04 01:10 AM


All times are GMT +1. The time now is 12:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"