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.
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.