Home |
Search |
Today's Posts |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference cell in one TAB from another using two criteria
Think option 3 from my earlier response would apply, viz:
To return values from corresp named range: ReturnCol where the criteria satisfies: =index(ReturnCol,match(1,(Date=A1)*(Hour=B3),0)) Above must be array-entered, ie press CTRL+SHIFT+ENTER Here's an illustrative sample specific to your set-up: http://www.flypicture.com/download/MTIxMTk= Extracting based on dual criteria.xls Note: Do not click on the link direct if you're reading this in microsoft's webpage. Do a copy n paste of the link (inclusive the "=" at the end) into your browser. In the sample: Source data is assumed in tab: A, cols A to C, from row1 down. Dates in col A, Times in col B, desired return values in col C. Each date repeats 24 times, with a total assumed of up to 210 days (say), ie till row 5040. In tab: B, The 24 hr times are listed in A2:A25, with dates listed in B1 across Place in B2, array-enter, ie confirm the formula by pressing CTRL+SHIFT+ENTER: =IF(B$1="","",INDEX(A!$C$1:$C$5040,MATCH(1,(A!$A$1 :$A$5040=B$1)*(A!$B$1:$B$5040=$A2),0))) Copy B2 across as far as required & fill down to populate. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jeff" wrote: Both you and Max are correct I could have been a little more specific. Tab A Column A contains Dates -- 01/01/2007 Column A Repeats each date 24 times Column B Contains Hours -- 00:00 -- 23:00 Column B cycles 00:00 -- 23:00 and repeats Column A & Column B have 5000 Rows. Column C contains the data of interest. In TAB B I would like a formula that can pick out the value in Column C where Column A = 02/03/2007 Column B = 03:00 I would like to set up Tab B like This. Column A Column B Column C Column D Column E Column F Column G Row 1 1/1/2007 1/2/2007 1/3/2007 1/4/2007 1/5/2007 1/6/2007 Row 2 00:00 1 2 3 4 5 6 Row 3 01:00 2 2 4 5 5 7 Row 4 02:00 3 2 5 6 5 8 Row 5 03:00 4 2 6 7 5 9 Row 6 04:00 5 2 7 8 5 10 Row 7 05:00 6 2 8 9 5 11 Row 8 06:00 7 2 9 10 5 12 Row 9 07:00 8 2 10 11 5 13 Row 10 08:00 9 2 11 12 5 14 Row 11 09:00 10 2 12 13 5 15 Row 12 10:00 11 2 13 14 5 16 Row 13 11:00 12 2 14 15 5 17 Row 14 12:00 13 2 15 16 5 18 Row 15 13:00 14 2 16 17 5 19 Row 16 14:00 15 2 17 18 5 20 Row 17 15:00 16 2 18 19 5 21 Row 18 16:00 17 2 19 20 5 22 Row 19 17:00 18 2 20 21 5 23 Row 20 18:00 19 2 21 22 5 24 Row 21 19:00 20 2 22 23 5 25 Row 22 20:00 21 2 23 24 5 26 Row 23 21:00 22 2 24 25 5 27 Row 24 22:00 23 2 25 26 5 28 Row 25 23:00 24 2 26 27 5 29 -- Jeff |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I set the criteria in a sumif statement as a cell reference? | Excel Worksheet Functions | |||
SUMPRODUCT Criteria Via Cell Reference?? | Excel Worksheet Functions | |||
How do you reference another cell in the criteria of a SUMIF funct | Excel Worksheet Functions | |||
Could the "Criteria" in COUNTIF function be a cell reference? | Excel Worksheet Functions | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |