Prev: Sum formular using vlookup
Next: how do I copy an entire worksheet to another with text & format?
From: Tony on 4 Jul 2008 16:02
I have a worksheet pulls values from a source worksheet ('Jurisdiction
Entries'!$C$2:$C$35) based on a date I fill in on the target worksheet (A5)
and a state code (B3..). It brings in a couple of fields when it matches the
date ('Jurisdiction Entries'!$A$2:$A$35,0) and state code
(Entries'!$B$2:$B$35,0) in the source worksheet. When I cuse 09/30/2002 as my
date, it works fine. When I change to another date such as 09/30/2008, I get
a reference error (#REF!).

Here is the formula:

=INDEX('Jurisdiction Entries'!$C$2:$C$35,MATCH($A5,'Jurisdiction
Entries'!$B$2:$B$35,0),MATCH($B$3,'Jurisdiction Entries'!$A$2:$A$35,0))


Target Document
PERIOD ENDING: Tuesday, September 30, 2008 MATCH (A5)
(B3)
CT #REF! #REF! 0.0000%


Source Document
(A2:A35) (B2:B35) (C2:C35)
09/30/2002 WI 100
09/30/2002 WV 200
09/30/2008 CT 300
09/30/2008 IL 400


Could someone tell what I have to to be able to just change the date and
pick up the associated data.

Thanks

Tony D.
From: T. Valko on 4 Jul 2008 16:51
Try this:

=SUMPRODUCT(--('Jurisdiction Entries'!$A$2:$A$35=$B$3),--('Jurisdiction
Entries'!$B$2:$B$35=$A5),'Jurisdiction Entries'!$C$2:$C$35)

--
Biff
Microsoft Excel MVP


"Tony" <Tony(a)discussions.microsoft.com> wrote in message
news:34F28C40-F206-42A9-9E52-E07848F224F3(a)microsoft.com...
>I have a worksheet pulls values from a source worksheet ('Jurisdiction
> Entries'!$C$2:$C$35) based on a date I fill in on the target worksheet
> (A5)
> and a state code (B3..). It brings in a couple of fields when it matches
> the
> date ('Jurisdiction Entries'!$A$2:$A$35,0) and state code
> (Entries'!$B$2:$B$35,0) in the source worksheet. When I cuse 09/30/2002 as
> my
> date, it works fine. When I change to another date such as 09/30/2008, I
> get
> a reference error (#REF!).
>
> Here is the formula:
>
> =INDEX('Jurisdiction Entries'!$C$2:$C$35,MATCH($A5,'Jurisdiction
> Entries'!$B$2:$B$35,0),MATCH($B$3,'Jurisdiction Entries'!$A$2:$A$35,0))
>
>
> Target Document
> PERIOD ENDING: Tuesday, September 30, 2008 MATCH (A5)
> (B3)
> CT #REF! #REF! 0.0000%
>
>
> Source Document
> (A2:A35) (B2:B35) (C2:C35)
> 09/30/2002 WI 100
> 09/30/2002 WV 200
> 09/30/2008 CT 300
> 09/30/2008 IL 400
>
>
> Could someone tell what I have to to be able to just change the date and
> pick up the associated data.
>
> Thanks
>
> Tony D.


From: Billy Liddel on 4 Jul 2008 16:57
Tony

This is an array function entered CSE (Ctr, Shift, Enter)

=IF(ISERROR(MATCH($A$5&$B$5,'Jurisdiction Entries'!$B$2:$B$35&'Jurisdiction
Entries'!$A$2:$A$35,0)),"",INDEX('Jurisdiction
Entries'!$C$2:$C$35,MATCH($A$5&$B$5,'Jurisdiction
Entries'!$B$2:$B$35&'Jurisdiction Entries'!$A$2:$A$35,0)))

Excel puts curly brackets in the formula when it has been entered correctly.
Be careful abount page breaks when you copy the formula.

Regards
Peter

"Tony" wrote:

> I have a worksheet pulls values from a source worksheet ('Jurisdiction
> Entries'!$C$2:$C$35) based on a date I fill in on the target worksheet (A5)
> and a state code (B3..). It brings in a couple of fields when it matches the
> date ('Jurisdiction Entries'!$A$2:$A$35,0) and state code
> (Entries'!$B$2:$B$35,0) in the source worksheet. When I cuse 09/30/2002 as my
> date, it works fine. When I change to another date such as 09/30/2008, I get
> a reference error (#REF!).
>
> Here is the formula:
>
> =INDEX('Jurisdiction Entries'!$C$2:$C$35,MATCH($A5,'Jurisdiction
> Entries'!$B$2:$B$35,0),MATCH($B$3,'Jurisdiction Entries'!$A$2:$A$35,0))
>
>
> Target Document
> PERIOD ENDING: Tuesday, September 30, 2008 MATCH (A5)
> (B3)
> CT #REF! #REF! 0.0000%
>
>
> Source Document
> (A2:A35) (B2:B35) (C2:C35)
> 09/30/2002 WI 100
> 09/30/2002 WV 200
> 09/30/2008 CT 300
> 09/30/2008 IL 400
>
>
> Could someone tell what I have to to be able to just change the date and
> pick up the associated data.
>
> Thanks
>
> Tony D.
 | 
Pages: 1
Prev: Sum formular using vlookup
Next: how do I copy an entire worksheet to another with text & format?