IF Statement For Multiple Cells A worksheet is created to collect inspection dat
ID: 3561491 • Letter: I
Question
IF Statement For Multiple Cells
A worksheet is created to collect inspection data. Different models are assembled and within the different models are different inspection criteria. IF statement is created and when the technician selects the model from the drop down menu, E6, the 185 specifications are set for E column. Technician enters data and if the data is outside a limit, the respective cell changes color to red. When moving to the next column, F6, and selecting a different model with different specifications, the F6 column defaults to E6 (185 specifications) and does not recognize the 177 specifications. There are six different models and each model has different specifications. Any help is greatly appreciated.
Explanation / Answer
Hi,
I think I begin to see your problem - it is actually over on your [IF Data] sheet, right?
What you need to know for columns C and D is whether or not a particular Spec item is listed on the [Sep-Dec 2014] sheet or not. Let's look at a couple of rows on the [If Data] sheet and set up a formula that will pretty much do that for you.
In cell C17, put this formula:
=IF(ISNUMBER(MATCH(B17,'Sep-Dec 2014'!$E$6:$XEZ$6,0)),355,0)
Put this one into D17
=IF(ISNUMBER(MATCH(B17,'Sep-Dec 2014'!$E$6:$XEZ$6,0)),339,0)
You should see 355 and 339 in the two cells
On down to next row, 18:
In C18:
=IF(ISNUMBER(MATCH(B18,'Sep-Dec 2014'!$E$6:$XEZ$6,0)),281,0)
and in D18
=IF(ISNUMBER(MATCH(B18,'Sep-Dec 2014'!$E$6:$XEZ$6,0)),249,0)
in these cases you should see 281 and 249 in the cells
Let's see one where the number in column B is not on the [Sep-Dec...] sheet. On down to row 19,
In C19:
=IF(ISNUMBER(MATCH(B19,'Sep-Dec 2014'!$E$6:$XEZ$6,0)),355,0)
and in D19
=IF(ISNUMBER(MATCH(B19,'Sep-Dec 2014'!$E$6:$XEZ$6,0)),339,0)
These should remain zero.
The ISNUMBER(MATCH(B#,'Sep-Dec 2014'!$E$6:$XEZ$6,0)), portion of the formula simply returns a true or false indication as to whether or not the entry in column B appears in row 6 of the [Sep-Dec] sheet or not, and if it does, it puts up the designated non-zero value (or zero as in the situation at row 16).
Now, you could replace those hard coded values such as ,281, ,249, ,355, and ,339, with a VLOOKUP() formula into your table you have set up over on the [VLOOKUP Data] sheet. Lets say in the 'Case to Case' area, you need to pull the values from the columns you have labeled Spec 3 and Spec 4 from that table.
So the formulas at row 17 could become:
In C17:
=IF(ISNUMBER(MATCH($B17,'Sep-Dec 2014'!$E$6:$XEZ$6,0)),VLOOKUP($B17,'VLOOKUP Data'!$A$1:$M$8,4,FALSE),0)
and in D17
=IF(ISNUMBER(MATCH($B17,'Sep-Dec 2014'!$E$6:$XEZ$6,0)),VLOOKUP($B17,'VLOOKUP Data'!$A$1:$M$8,5,FALSE),0)
The advantage here, is that you don't have to tailor each formula on each row on that [IF Data] sheet.