A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Reference cell in one TAB from another using two criteria



 
 
Thread Tools Display Modes
  #1  
Old August 6th 07, 07:28 PM posted to microsoft.public.excel.worksheet.functions
Jeff
external usenet poster
 
Posts: 921
Default 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
Ads
  #2  
Old August 7th 07, 02:30 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 9,221
Default 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 array-entered, 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  
Old August 7th 07, 02:40 AM posted to microsoft.public.excel.worksheet.functions
OssieMac
external usenet poster
 
Posts: 2,510
Default 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  
Old August 7th 07, 03:48 AM posted to microsoft.public.excel.worksheet.functions
Jeff
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

  #5  
Old August 8th 07, 10:27 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 9,221
Default 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
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I set the criteria in a sumif statement as a cell reference? Duncan Excel Worksheet Functions 3 July 27th 06 05:46 AM
SUMPRODUCT Criteria Via Cell Reference?? John V Excel Worksheet Functions 8 April 12th 06 07:55 PM
How do you reference another cell in the criteria of a SUMIF funct Hold the Onions Excel Worksheet Functions 2 August 28th 05 12:06 AM
Could the "Criteria" in COUNTIF function be a cell reference? JohnSheenWSN Excel Worksheet Functions 2 June 16th 05 08:07 PM
Can I use a cell reference in the criteria for the sumif function. Number Cruncher Excel Worksheet Functions 2 November 4th 04 08:52 PM


All times are GMT +1. The time now is 09:08 PM.


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