View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jeff Jeff is offline
external usenet poster
 
Posts: 921
Default 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