Conditionals

Posted by mAT on September 27, 2001 3:34 AM

I have two worksheets.

In the first worksheet, column A contains consecutive daily dates (01/01/2001 to 31/12/2001), column B is blank but entitled "BOB" at B1, column C is also blank but entitled "CHARLES" at C1.

In the second worksheet there are also three columns:
Column A contains dates that are non-consecutive, column B has text string either "BOB" or "CHARLES", and column C has a number (in this case an expense).

For each row in the second worksheet I need to copy the number (col C) into the first worksheet where the dates and the people match.

For example: row1 of wksheet2 could be: 5-Jan-01, CHARLES, 72

72 needs to go to wksheet1, column C (CHARLES), in the row which starts the date 5-Jan-01.

I understand the logic but cant do the programming. Any ideas?

Best regards
Mat

Posted by Aladin Akyurek on September 27, 2001 12:52 PM

mAT,

In Sheet1,

in B2 enter: =IF(ISNUMBER(SUMPRODUCT(( MATCH($A2&C$1, Sheet2!$A$1:$A$100 & Sheet2!$B$1:$B$100, 0)))), INDEX(Sheet2!$C$1:$C$100, SUMPRODUCT((MATCH($A2&C$1, Sheet2!$A$1:$A$100 & Sheet2!$B$1:$B$100, 0)))),"")

Copy this first to C2 then down as far as needed.

Caveat. This formula expects that Sheet2 does not contain duplicate entries such as

5-Jan-01 BOB 10
5-Jan-01 BOB 20

Aladin

========

Posted by m@ on September 28, 2001 1:01 AM

THanks Aladin

Thanks Aladin,

Works great now.

Best regards
Mat