Forum Discussion

MattRaw's avatar
MattRaw
Copper Contributor
Dec 16, 2024

Help needed to link IF functions into 1 cell

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.

The attached 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

 

  • That is what I sort of thought and mentioned in my reply all you have to do is remove that last line of the equation:

    =ROUND(IFERROR(
            (--L$3>$A$4)*
            XMATCH(K$3,HSTACK($A$4,$B$4,$C$4,$D$4,$E$4,$F$4,$G$4,$H$4,$I$4,$J$4),1),0)/2,0)

    This should return the 0,1,2,3,4,5 all on the same row

  • That's duplication of https://techcommunity.microsoft.com/discussions/excelgeneral/wanting-to-link-multiple-ifandor-functions-into-1-formular/4357819 

    As variant

    =SWITCH( ROW(),
      2, IF($B2="","",IF(OR(AND($B2<T$1,$H2>T$1),AND($B2>T$1,$B2<U$1)),1,0)),
      3, IF($I2="","14",IF(OR(AND($I2<T$1,$M2>T$1),AND($I2>T$1,$I2<U$1)),2,0)),
      ...
     )
    • MattRaw's avatar
      MattRaw
      Copper Contributor

      Thank you for this (and apologies for the duplication. I couldn't find my original post so assumed that it had been removed by moderators for some unknown reason).

       

      So, to get my head around it, to merge all the formulars into one, I need the "=SWITCH(ROW()," before the 1st line, and the follow on with the remaining rows?

       

      That is what I have tried.

      Here is the overall formular I end up with (Note 1: The cells are slightly different from when I originally posted - Note 2: Each line works as desired on their own):

      =SWITCH(ROW(),IF($AW$80="","",IF(OR(AND($AW$80<=CA$2,$BD80>=CA$2),AND($AW$80>=CA$2,$AW$80<=CB$2)),1,0)),IF($BE80="","",IF(OR(AND($BE80<=CA$2,$BI80>=CA$2),AND($BE80>=CA$2,$BE80<=CB$2)),2,0)),IF($BJ80="","",IF(OR(AND($BJ80<=CA$2,$BK80>=CA$2),AND($BJ80>=CA$2,$BJ80<=CB$2)),3,0)),IF($BL80="","",IF(OR(AND($BL80<=CA$2,$BM80>=CA$2),AND($BL80>=CA$2,$BL80<=CB$2)),4,0)),IF(($BM80+31)="","",IF(OR(AND(($BM80+31)<=CA$2,($BM80+59)>=CA$2),AND(($BM80+31)>=CA$2,($BM80+31)<=CB$2)),5,0)))

       

      What I have found is that although I don't get an error in the CELL, the only cells that return numerical values as desired (other than 0s) are the ones that return the number 5.

      None of the CELLs return numbers 1, 2, 3 or 4.

       

      Any Ideas where I have gone wrong?

      Thank you.

      • For the initial sample it could be

        =SWITCH( ROW(),
          2, IF($B$2="","",  IF(OR(AND($B$2<T$1,$H$2>T$1),AND($B$2>T$1,$B$2<U$1)),1,0)),
          3, IF($I$2="","14",IF(OR(AND($I$2<T$1,$M$2>T$1),AND($I$2>T$1,$I$2<U$1)),2,0)),
          4, IF($N$2="","",  IF(OR(AND($N$2<T$1,$O$2>T$1),AND($N$2>T$1,$N$2<U$1)),3,0)),
          5, IF($P$2="","",  IF(OR(AND($P$2<T$1,$Q$2>T$1),AND($P$2>T$1,$P$2<U$1)),4,0)),
          6, IF($R$2="","",  IF(OR(AND($R$2<T$1,($S$2+28)>T$1),AND($R$2>T$1,$R$2<U$1)),5,0)),
          ""
         )

        We need to use absolute references for $B$2, etc. Please check in attached file.

Resources