Forum Discussion

MattRaw's avatar
MattRaw
Copper Contributor
Dec 16, 2024

Wanting to link multiple IF/AND/OR functions into 1 formular

Hi All, 

I have a tracker spreadsheet that upon entering dates into cells, there is a calendar section at the end where the dates populate into there in the forms of numbers - which are then conditional formatted to different colours based on stages of the project.

As there are 5 main stages to our projects, currently I have 5 rows - one for each stage.

But, I don't want to have 5 rows, so was hoping there would be some way to combine these IF functions (each of which contain OR and AND functions) into 1 overall formular, so I can have 1 row that contains all.

These screenshots hopefully make this clearer than I may have explained:

Formulars:

T2 - =IF($B2="","",IF(OR(AND($B2<T$1,$H2>T$1),AND($B2>T$1,$B2<U$1)),1,0))

T3 - =IF($I2="","",IF(OR(AND($I2<T$1,$M2>T$1),AND($I2>T$1,$I2<U$1)),2,0))

T4 - =IF($N2="","",IF(OR(AND($N2<T$1,$O2>T$1),AND($N2>T$1,$N2<U$1)),3,0))

T5 - =IF($P2="","",IF(OR(AND($P2<T$1,$Q2>T$1),AND($P2>T$1,$P2<U$1)),4,0))

T6 - =IF($R2="","",IF(OR(AND($R2<T$1,($S2+28)>T$1),AND($R2>T$1,$R2<U$1)),5,0))

 

Does anyone know if the formulars in T2: T6 can be combined so the cell will fill and 1 to 5 value based on the associated date, which then formats to the colour I want?

Or even if there is another way to achieve this?

 

Thanks in advance, Matt

  • Duplicatio of https://techcommunity.microsoft.com/discussions/excelgeneral/help-needed-to-link-if-functions-into-1-cell/4357838

  • Duplicatio of https://techcommunity.microsoft.com/discussions/excelgeneral/help-needed-to-link-if-functions-into-1-cell/4357838

Resources