Formula help

GRB

New Member
Joined
Apr 7, 2009
Messages
20
I need to write a formula that looks at these columns on one worksheet:
<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=133 border=0><COLGROUP><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 694" span=7 width=19><TBODY><TR style="HEIGHT: 18.75pt; mso-height-source: userset" height=25><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18.75pt; BACKGROUND-COLOR: #f2dddc" width=133 colSpan=7 height=25>TREATMENTS NEEDED</TD></TR><TR style="HEIGHT: 49.5pt; mso-height-source: userset" height=66><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 49.5pt; BACKGROUND-COLOR: #f2dddc" width=19 height=66>NONE</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc" width=19>TUMBLE</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc" width=19>ANODIZE</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc" width=19>HEAT TREAT</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc" width=19>TEFLON</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc" width=19>PLATING</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc" width=19>OTHER</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">x</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">x</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">x</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>


and enters the appropriate treatment on another worksheet (in order):
Treatment 1 Treatment 2 Treatment 3
TUMBLE HEAT TREAT TEFLON


I want the formula to look in all the boxes between "Tumble" and "Other" to see which ones are marked and then put the appropriate treatments on Worksheet 2 under the headings "Treatment 1", "Treatment 2", "Treatment 3", etc.
For the example above, I chose 3 treatments - tumble, heat treat, and teflon. Because there is an "x" in the box on Worksheet 1, then the word "tumble" should appear under "Treatment 1" on Worksheet 2, and so on.

This is the formula I have so far, but it doesn't work:

=IF(HLOOKUP('QC Check'!$J4,'QC Check'!$J4:$O4,1,FALSE)="X","TUMBLE",IF(HLOOKUP('QC Check'!$K4,'QC Check'!$J4:$O4,1,FALSE)="X","ANODIZE",IF(HLOOKUP('QC Check'!$L4,'QC Check'!$J4:$O4,1,FALSE)="X","HEAT TREAT",IF(HLOOKUP('QC Check'!$M4,'QC Check'!$J4:$O4,1,FALSE)="X","TEFLON",IF(HLOOKUP('QC Check'!$N4,'QC Check'!$J4:$O4,1,FALSE)="X","PLATING",IF(HLOOKUP('QC Check'!$O4,'QC Check'!$J4:$O4,1,FALSE)="X","OTHER", "NOTREAT"))))))
 
Is the formula working for you?

1. Assuming your headings are in Row 3.
Assuming your F3 etc have consistent wording like Treatment1. The 1 will yield the criteria for the formula with
the formula Right(F3,1). The formula in F4 of

=INDEX('QC Check'!$I$3:$O$3,,SMALL(IF('QC Check'!$I4:$O4="x",{1,2,3,4,5,6,7}),1)) can be changed to

=INDEX('QC Check'!$I$3:$O$3,,SMALL(IF('QC Check'!$I4:$O4="x",{1,2,3,4,5,6,7}),RIGHT(F$3)))

This is an Array Formula;consequently, it must be entered with Ctrl+Shift+Enter (CSE) not just Enter. CSE is also required if you edit the formula.


2. Your sheet where you put the "x", could include a formula like the following for the Treatment "None".
This example is in I8 and it says If there is anything in J8:O8, put "" (blank) in I8, otherwise put "x" i.e. None column shows "x" until something is put in J8:O8.

=IF(COUNTA(J8:O8),"","x")

2.Do you use Excel 2007? Yes
Check help for probably the new IFERROR formula; you can suppress #Num! with such a function.
I don't have the exact syntax (see help); I don't use Excel 2007.
 
Last edited:
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Dave,
The last formula you gave me worked, THANK YOU! :biggrin:
Can you please teach me how to suppress #Num message when there is no result?:confused:
THANKS AGAIN!
 
Upvote 0
Would I add the IFERROR formula to the other formula? Where would it go in the sequence?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top