Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/Match Multiple Criteria | Excel Discussion (Misc queries) | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
Index/Match Help | Excel Discussion (Misc queries) | |||
Index/Match to look up a value in one workbook and insert it into. | Excel Discussion (Misc queries) | |||
Need Function for adding rows on multiple sheets... struggling rookie ;) Thanks. | Excel Worksheet Functions |