Home 
Search 
Today's Posts 
#1




Reference cell in one TAB from another using two criteria
I would like to reference a cell in TAB A based on two criteria,
Where Named_Range_Date = A1 and Named_Range_Hour = B3 I need to be able to copy this formula so that I can populate a new table, any ideas?  Jeff 
#2




Reference cell in one TAB from another using two criteria
Think your intents were not specified,
ie to do what?? where the dual criteria is met Try something along these lines, depending on your intents .. To Count # of instances where the criteria satisfies: =sumproduct((Date=A1)*(Hour=B3)) Press ENTER will do To Sum another corresp named range: ReturnCol, where the criteria satisfies: =sumproduct((Date=A1)*(Hour=B3),ReturnCol) Press ENTER will do To return values from corresp named range: ReturnCol where the criteria satisfies: =index(ReturnCol,match(1,(Date=A1)*(Hour=B3),0)) Above must be arrayentered, ie press CTRL+SHIFT+ENTER Notes: ReturnCol, Date, Hour are presumed identically sized named ranges Lookup values in A1, B3 are presumed real dates & times  Max Singapore http://savefile.com/projects/236895 xdemechanik  "Jeff" wrote: I would like to reference a cell in TAB A based on two criteria, Where Named_Range_Date = A1 and Named_Range_Hour = B3 I need to be able to copy this formula so that I can populate a new table, any ideas?  Jeff 
#3




Reference cell in one TAB from another using two criteria
Example of nested And function with If function to make 2 comparisons.
=IF(AND(Name_Range_Date=A1,Named_Range_Hour=B3),Sh eet2!A3,"No Match") If the match is true then the reference in another tab is inserted. If not true then it inserts 'No Match'. this can be replaced with double quotes without a space between them ("""") if you do not want to see anything if there is no match. However, by initially using 'No Match', it helps in testing if the formula is doing what you want. Any named range is always absolute. In the above formula, A1, B3 and Sheet2!A3 are relative. The following makes all cell references absolute: =IF(AND(Name_Range_Date=$A$1,Named_Range_Hour=$B$3 ),Sheet2!$A$3,"No Match") I included the second one because I do not know just what you need when you say you want to copy the formula. Hope it helps. Regards, OssieMac "Jeff" wrote: I would like to reference a cell in TAB A based on two criteria, Where Named_Range_Date = A1 and Named_Range_Hour = B3 I need to be able to copy this formula so that I can populate a new table, any ideas?  Jeff 
#4




Reference cell in one TAB from another using two criteria
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 "OssieMac" wrote: Example of nested And function with If function to make 2 comparisons. =IF(AND(Name_Range_Date=A1,Named_Range_Hour=B3),Sh eet2!A3,"No Match") If the match is true then the reference in another tab is inserted. If not true then it inserts 'No Match'. this can be replaced with double quotes without a space between them ("""") if you do not want to see anything if there is no match. However, by initially using 'No Match', it helps in testing if the formula is doing what you want. Any named range is always absolute. In the above formula, A1, B3 and Sheet2!A3 are relative. The following makes all cell references absolute: =IF(AND(Name_Range_Date=$A$1,Named_Range_Hour=$B$3 ),Sheet2!$A$3,"No Match") I included the second one because I do not know just what you need when you say you want to copy the formula. Hope it helps. Regards, OssieMac "Jeff" wrote: I would like to reference a cell in TAB A based on two criteria, Where Named_Range_Date = A1 and Named_Range_Hour = B3 I need to be able to copy this formula so that I can populate a new table, any ideas?  Jeff 
#5




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 arrayentered, ie press CTRL+SHIFT+ENTER Here's an illustrative sample specific to your setup: 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, arrayenter, 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 
Reply 
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 