Pulling values from another sheet if a rows text matches another 1

[kidnos]

Sorry for the somewhat confusing title. I am looking to pull information into one excel sheet from another excel sheet. I have one sheet that has the date at the top (9/10/2006) and one cell (A10) says estimated payables with the cell next to it blank(B10). The other sheet has the date in column A, title in B (so B could say estimated payables, as well as other things), amount in column C (say $5,000).
What I would like to do is pull the estimated payable number in sheet 2 (column C) if the date in sheet 2 column A is greater than the date at the top of sheet 1 and only if the title in sheet 2 column B = estimated payables.
Thanks

 

[SkipVought]

Hi,

A picture is worth a thousand words! Sure would have been easier on you and me if you had just posted (copy 'n' paste) an example from your sheets' data.

But take a look at the Datbase functions -- DGET in particular.

Skip,

_[red][/red]

 

[kidnos]

Sheet 1:
A1: Blank
A2: 9/10/2006
A3: Estimated Payables
B1: Company name
B2: Blank
B3: Blank (but want it to = the payable numbers from sheet 2)

Sheet 2 (this is the sheet I want to pull info from):
A5: Date
A6:9/13/2006
A7:9/13/2006
B5: Description
B6:Rents
B7:Estimated Payables
Column C is blank (hidden)
D5: Disbursements
D6: 15,000
D7: 25,000

So I would like sheet 1 cell B3 to pull the "estimated payables" value on date > Sheet 1: A2

Thanks

 

[kidnos]

This is what I figured out so far- in worksheet1:

=IF('[Worksheet2.xls]Sheet1'!$A:$A>A2,VLOOKUP(A3,'[Worksheet2.xls]Sheet1'!$B:$D,3,FALSE),0)

But if the date of estimated payable is less than what sheet 1 has it still returns that value.... Checking it I now see that if I change the date in sheet 1 it has no impact on the result. Strange.

 

[SkipVought]

BTW, your "example" was no picture.

[tt]
IF('[Worksheet2.xls]Sheet1'!$A:$A>A2,....)
[/tt]
is not a valid syntax.

What you are trying to say?



Skip,

_[red][/red]

 

[kidnos]

Sorry for the lack of the picture.
I guess thats the problem. I wanted to look through column A on sheet 2 to see if there were dates greater than the date on sheet 1 (cell A2). If there is a date that is greater AND the corresponding cell in column B contained the term Estimated Payables then I wanted to pull the value in column D back to sheet 1 (cell B3).

 

[SkipVought]

[tt]
=sumproduct((sheet2!A2:A65536>A2)*(sheet2!B2:B65536="Estimated Payables")*(sheet2!D265536))
[/tt]




Skip,

_[red][/red]

 

[kidnos]

Thanks Skip! Big help. I had no idea how versatile Sum Product is. Its description in MS Excel help definitely does not give it justice.