Populate call based on 4 drop down criteria???

[Marq]

Using 365

This going to be pretty elaborate so im doing my best to describe whats going on.

I have two sheets..one is called Summary and the other is called Cost.

The Summary has 4 drop downs, which in A9 drop down I am to choose my paint system. The other three drop downs, D7, D8 and D9 i am to choose my surface preps...i can have one, two or three surface preps, depending on the job.

Paint system choices in A9: I have 39 choices, ...the main one driving a majority of the formula is TSA....so id choose either TSA or one of the other 38 choices (im not going to list them all..to much to list)

Surface Prep choices for D7,D8 and D9 are as follows (they all pull from the same list):
N/A
LPWC
SP2
SP3
SP6
SP7
SP10
SP11
SP15

I can chose any combination of the above surface preps in the three drop downs.

On the cost sheet, L28 ( which is the cell the formula will be in) it should populate the existing values in cells L21 or L25, based on the 4 drop down selections on the Summary.

The criteria for L28 populating would be as follows:

If A9 on Summary equals TSA and D7,D8 and D9 contain any one or more of the following in any combination: SP3, SP6, SP7, SP10, SP11, SP10 then give me the value in L25.

So for an example, I can have this as a combo:
A9=TSA
D7=SP10
D8=SP11
D9=SP2

If I have that above combo then I should have the value of L25 show up in L28. There may be a scenario where D8 and D9 will have the choice of N/A...(that means for this particular job only one surface prep was required, not three, but i still have to make a choice in D8 and D9 so I made N/A a choice and in this case I would still get the value of L25 because SP10 and SP11 is in the combination of the three.)

On to the next criteria:

If A9 on Summary equals anything but TSA and D7,D8 and D9 contain any one or more of the following in any combination: SP3, SP6, SP7, SP10, SP11, SP10 then give me the value in L21.

For example, to get the value of L21 to show up in L28, I would have this combo:

A9= anything but TSA (I have 39 choices in this drop down...so im chosing any one of the other 38 options)
D7=LPWC
D8=SP2
D9=SP3

In that scenario above im choosing anything but TSA in A9 ..and again, if D7,D8 and D9 contain any one or more of the following, in any combination: SP3, SP6, SP7, SP10, SP11, SP10 then this time give me the value in L21....(N/A could be a choice too but since I have that SP3 in there, then L21 value should still show up in L28)


The combo to leave L28 at zero would be anything but TSA in A9 and any one or more of the following combinations in D7, D8 and D9: SP2 , LPWC

A9 = anything but TSA
D7=LPWC
D8=SP2
D9=N/A

So basically the only time L28 is left at zero is if anything but TSA is in A9 and the only surface prep is SP2 and/or LPWC.

That pretty much sums up what im looking for. I hope I was as precise and clear as possible.

Thanks for your time and reading and possibly helping me out.

 

[Marq]

I left accidentally typo'ed SP15 as a SP10 in a section of the above comment.....it should read like this (I copied/pasted below the section im referring to and highlighted in red the typo correction)

"If A9 on Summary equals TSA and D7,D8 and D9 contain any one or more of the following in any combination: SP3, SP6, SP7, SP10, SP11, SP15 then give me the value in L25.

So for an example, I can have this as a combo:
A9=TSA
D7=SP10
D8=SP11
D9=SP2

If I have that above combo then I should have the value of L25 show up in L28. There may be a scenario where D8 and D9 will have the choice of N/A...(that means for this particular job only one surface prep was required, not three, but i still have to make a choice in D8 and D9 so I made N/A a choice and in this case I would still get the value of L25 because SP10 and SP11 is in the combination of the three.)"

 

[Marq]

Im trying to build part of it ….this is the first building block of the overall formula but Im getting a "FALSE" result when I should have the value in L25...here is the formula:


=IF(B9="TSA",IF(OR(D7="SP3","SP6","SP7","SP10","SP11","SP15"),IF(OR(D8="SP3","SP6","SP7","SP10","SP11","SP15"),IF(OR(D9="SP3","SP6","SP7","SP10","SP11","SP15"),'Cost'!L25))))


The first scenario I am trying to build from my original post:

"If A9 on Summary equals TSA and D7,D8 and D9 contain any one or more of the following in any combination: SP3, SP6, SP7, SP10, SP11, SP15 then give me the value in L25.

So for an example, I can have this as a combo:
A9=TSA
D7=SP10
D8=SP11
D9=SP2

If I have that above combo then I should have the value of L25 show up in L28. There may be a scenario where D8 and D9 will have the choice of N/A...(that means for this particular job only one surface prep was required, not three, but i still have to make a choice in D8 and D9 so I made N/A a choice and in this case I would still get the value of L25 because SP10 and SP11 is in the combination of the three.)"

 

[Marq]

I now tried this but I still get a false result

(im trying to build the overall formula one criteria at a time)

=IF(AND(B9="TSA"),IF(AND(D7={"SP3","SP6","SP7","SP10","SP11","SP15"}),IF(OR(D8={"SP3","SP6","SP7","SP10","SP11","SP15"}),IF(OR(D9={"SP3","SP6","SP7","SP10","SP11","SP15"}),'Cost Worksheet'!L25))))

 

[Marq]

My original post had a few edits needed but its locked now and I cant edit...the A9 in the original post should have been B9..I apologize to anyone that tried to make the formula on their own and my wrong cell reference confused you....I was too late to edit it...again, I apologize.

ANYWAY.....

I got this from a guy on reddit excel forums and it works:

=IF(Summary!B9="TSA",IF(OR(OR(Summary!D9="SP3",Summary!D9="SP6",Summary!D9="SP7",Summary!D9="SP10",Summary!D9="SP11",Summary!D9="SP15"),OR(Summary!D8="SP3",Summary!D8="SP6",Summary!D8="SP7",Summary!D8="SP10",Summary!D8="SP11",Summary!D8="SP15"),OR(Summary!D7="SP3",Summary!D7="SP6",Summary!D7="SP7",Summary!D7="SP10",Summary!D7="SP11",Summary!D7="SP15")),L25,""),IF(OR(OR(Summary!D9="SP3",Summary!D9="SP6",Summary!D9="SP7",Summary!D9="SP10",Summary!D9="SP11",Summary!D9="SP15"),OR(Summary!D8="SP3",Summary!D8="SP6",Summary!D8="SP7",Summary!D8="SP10",Summary!D8="SP11",Summary!D8="SP15"),OR(Summary!D7="SP3",Summary!D7="SP6",Summary!D7="SP7",Summary!D7="SP10",Summary!D7="SP11",Summary!D7="SP15")),L21,0))