Help with a condtional formula needed!

[Aimee S.]

Hello strangers! Hope your summer's going well

Here's what I am trying to accomplish where the formula resides in cell D4 of my present worksheet.

Condition 1) If $E4 (the cell right next to the formula) = 1 (a.k.a. 100%)

Condition 2) AND If Column A of another worksheet (within this
same workbook) entitled "2 - Data Refinement" contains
value that equals exactly "OSP Complete"

Condition 3) AND If Column E of that other worksheet entitled "2 - Data
Refinement" CONTAINS the word "Butler"

Then pull the Value that appears in Column D of the worksheet entitled "2 - Data Refinement" where Conditions 2 and 3 are met in that same row.

Halp Meeeeeee!!!!

 

[Ruddles]

That sounds easy enough.

I suspect the way most people would tackle this would be to start by creating some worksheets containing data which they could use to test their proposed formula, however I think it would be reasonable for them to expect this test data to be provided by someone familiar with it rather then them having to create it themselves.

Do you have some sample data we could use for this purpose?

 

[SPACKlick]

If you want the same row applied across all formulas it is merely an IF(AND(condition 1, condition 2, condition 3)=True(), Sheet2!Drownumber,)

If you want to look up rows where condition 2 and 3 are true if condition 1 is true you will need an array formula. But test data would help.

 

[Aimee S.]

You lost me a bit. (*blonde and caffeine deprived*)

Worksheet B looks like:
Column A
<TABLE style="WIDTH: 872pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1161><COLGROUP><COL style="WIDTH: 479pt; mso-width-source: userset; mso-width-alt: 23369" width=639><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 197pt; mso-width-source: userset; mso-width-alt: 9581" width=262><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 479pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 height=17 width=639>Task Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=114>% Complete</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=69>Start</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=77>Finish</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 197pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=262>Notes</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=17>OSP</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl69>20%</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl70>2/1/11</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl70>4/19/12</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68>Camden CHR</TD></TR></TBODY></TABLE>

Worksheet A looks like:
<TABLE style="WIDTH: 571pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=762><COLGROUP><COL style="WIDTH: 293pt; mso-width-source: userset; mso-width-alt: 14299" width=391><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 116.25pt; mso-height-source: userset" height=155><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; WIDTH: 293pt; HEIGHT: 116.25pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl70 height=155 width=391>7/29/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; WIDTH: 74pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl71 width=98></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; WIDTH: 204pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl86 width=273 colSpan=3>OSP Overall Timeline</TD></TR><TR style="HEIGHT: 116.25pt; mso-height-source: userset" height=155><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: white; WIDTH: 293pt; HEIGHT: 116.25pt; BORDER-TOP: black; BORDER-RIGHT: #e0dfe3" class=xl72 height=155 width=391>Award Location</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; WIDTH: 74pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=98>Engineering Firm</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl73 width=75>Planned </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 92pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl73 width=123>Actual </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=75>% Complete</TD></TR><TR style="HEIGHT: 116.25pt; mso-height-source: userset" height=155><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: #969696 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 293pt; HEIGHT: 116.25pt; BORDER-TOP: #969696; BORDER-RIGHT: #e0dfe3" class=xl75 height=155 width=391>Camden Telephone Company, Inc.</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; WIDTH: 74pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 1pt solid" class=xl76 width=98>CHR</TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; BORDER-TOP: #969696; BORDER-RIGHT: #e0dfe3" class=xl79></TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: #969696 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP: #969696; BORDER-RIGHT: #e0dfe3" class=xl80>HERE IS WHERE I WANT THE FORMULA</TD><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: #969696 0.5pt solid; BACKGROUND-COLOR: white; BORDER-TOP: #969696; BORDER-RIGHT: black 1pt solid" class=xl84>100%


</TD></TR></TBODY></TABLE>

 

[Aimee S.]

Let's try this. The formula I need in Worksheet A (where I indicated in the above table) has to pull the date in Column D of Worksheet B where:


Task Name for that same row (Column A) = "OSP"
% Complete for that same row = 100%
Notes for that same row *contain* the word "Camden"