Using IF Function combined with Conditional Formatting to Indicate data entries

[Enigma909]

Hello everyone, I have a problem that I am struggeling with. Due to the confidetiality of the data, I have created a copy of the original sheet.

To explain what I am trying to do.

Inteded purpose:
In sheet 1 there is the possibility of entering Yes or No. If someone enters No, nothing happens. If they Enter Yes, C2 in Sheet 2 changes to Green (through conditional formatting). B2, then changes color to red, meaning, that you need to enter data, in order to make it go green. The way you enter data, is by typing in 1's in the area E2 - N2. For each 1 you have entered, the same amount of cells P2 - Y2 and AA - AJ will turn yellow, implying you need to enter data in those. One then fills out "A"'s in the yellow cells in P2 - Y2. In AA2 - AJ2, you can enter entries through a list. If you chose more than 10 years, nothing will happen. You will then need to fill out AW + AY with "A" and the B2 cell should turn green, since all yellow cells have data in them. If you choose anything between under 2 years - 10 years, the cells in AL2 - AU2 will become yellow. And you need to fill in this data for B2 to turn green.

Current problem:
I have tried to set up the color change, based on empty cells, but it does not really work for me. Once you have entered an entry in the yellow cells in AA2-AJ2, the B2 cell will turn Green, eventhough there are other yellow cells that has not been filled.

Is there a way to fix this? I hope this does not seem too complex.

Thank you very much in advance for your help and time.

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)