Forum Discussion
MattRaw
Dec 16, 2024Copper Contributor
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