Auto Populate

[OKCIrish]

I've been all over the internet looking for formulas to try and make a cell auto populate depending on two cells text. I am trying to have column X populate with RPR when certain texts are selected from column K & T (basically all the text in the attached picture except OK, OK-FR, UND, STS). Column X was supposed to have data validation attached to it with drop down of OK, OK-HB, RPR & DBR but I disabled it because when I found simple formulas to work with it, it wouldn't return the value due to the data validation. Does anyone know of a way to solve this? I could use the simple formula I came up with but it would be a long string of formulas.

 

[Automatrix]

What should the X column say, and under which circumstances?

 

[OKCIrish]

What should the X column say, and under which circumstances?

X52 should say RPR when K52 & T52 equal one of these abbreviations MRF, PIT, WT, CT, GT, WO, BV, BM, DIM, PUT, CS, GS, SD, PS, UNS, SC. I have updated the sheet to where the rows and columns are shown. Example is if K52 has OK but T52 has PIT, then I would like X52 to auto populate with RPR. But if K52 & T52 both have OK then X52 should have OK as well.

 

[Automatrix]

Can you do a few examples? The logic is hard to work out from the description.

 

[OKCIrish]

Can you do a few examples? The logic is hard to work out from the description.

So if a certain text in column K at row 52 reads gt, same thing with column T and Row 52 I was column X row 52 to input RPR. But if they both ready OK, I would like column X row 52 read OK.

 

[Automatrix]

=IF(OR(K52="",T52=""),"",IF(AND(K52="ok",T52="ok"),"ok","rpr"))

The first section makes X blank if either K or T is blank. Otherwise if checks if both are "ok" (yields "ok"), else "rpr".

 

[OKCIrish]

=IF(OR(K52="",T52=""),"",IF(AND(K52="ok",T52="ok"),"ok","rpr"))

The first section makes X blank if either K or T is blank. Otherwise if checks if both are "ok" (yields "ok"), else "rpr".

I appreciate the answer. Are you willing to take this a step further? I have other formulas I need to come up with in conjunction with what you gave me.

 

[Automatrix]

Show us what you got, or maybe do a new thread if it's not like the first question (for the benefit of the search engine).

 

[OKCIrish]

Show us what you got, or maybe do a new thread if it's not like the first question (for the benefit of the search engine).

It is like the first question, with auto population just more expanded to other cells.

The first formula I need help with is based on if the K & T column have OK in them, it would then look to column M & N to see if they have either F, D or do in either column or a combination of the two and then column X would have OK-HB in it. My examples of this start at Row 56 to Row 61

The second formula I need help with is based on the first question, however if the formula recognizes STS or UND in column K or T then column X would have REJ in it. My example for this starts off in row 62.

I appreciate the help on this!

 

[Automatrix]

I can see that this can get complicated quickly. Rather than rolling it all into a single (long!) formula, I think you have two better options.

1. Make a new column for each logical evaluation - for example one that determines OK/RPR, another one that determines OK-HB or not, one that determines REJ or not (and so on), and then maybe a final column that evaluates these for the final class. This will allow you to detect and fix errors in steps rather than have the entire formula fail.

2. Make a separate condition table and draw the class information from this via XLOOKUP. This will allow you to keep easy track of which combinations correspond to a final class.

Book1
	A	B	C
1	Box initial condition	Pin initial condition	Class
2	ok	sts	REJ
3	ok	und	REJ
4	sts	ok	REJ
5	sts	und	REJ
6	und	ok	REJ
Sheet1