Sumproduct

[jlkirk]

Row 1, columns A-CC in worksheet 2 are various names. Row 4, columns A-CC in worksheet are one-word descriptions of the names. Row 473, columns A-CC contain formulas that return the dates of interviews with the persons named in row 1, from data in another section of worksheet 2. The formula returns a blank (i. e., "") if no interview was done.

What I would like to do is where a name that appears in row 1 on worksheet 2 is listed in column A on worksheet 1, and a certain description is present in row 4, say, a "P", look to worksheet 2 and insert in column B on worksheet 1 the date, if any, of the interview of that person that is in row 473 on worksheet 2, but only when the description is say a "P". If no interview was done, i. e., a blank is in row 473, then insert a blank on worksheet 1.

Any ideas?

Thanks in advance.

[Richard Buttrey]

Any ideas?

Thanks in advance.

Yes. I suggest you upload the actual workbook rather than try and describe it. Show a before and after position with an explanation of how you moved from one to the other.

Regards

[jlkirk]

As you wish. Sheet 1 show results I am after.

[jlkirk]

Back to the top

[Richard Buttrey]

Note: Response sent before I saw Blake7s and RomperStompers postings which weren't present when I replied.


Hi

In B1 on Sheet1 copied down

=IF(AND(INDEX(Sheet2!$2:$2,1,MATCH(A1,Sheet2!$1:$1,FALSE))="P",INDEX(Sheet2!$3:$3,1,MATCH(A1,Sheet2!$1:$1,FALSE))>0),INDEX(Sheet2!$3:$3,1,MATCH(A1,Sheet2!$1:$1,FALSE)),"")

Regards

[romperstomper]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this.

Please add links to any and all cross-posts to your original post.

[Blake 7]

Also - pretty bad etiquette not to respond when someone is offering to help you!

http://www.excelforum.com/excel-gene...ml#post2531275