phdbergman
New Member
- Joined
- Feb 11, 2021
- Messages
- 2
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
- MacOS
Hi Forum,
I'm reach out to you to ask if someone can help suggest improvement to a function for conditional formatting.
I created a calendar where I can plan resources by entering the number of days and the following cells are colored by using conditional formatting.
This far I have only been able to solve the problem by IFS-function and have a ugly solution for up to 21 days. I would like to expend the range to whatever really.
The cells are only to be colored for integer values.
Present formula:
=IFS( AND(ISNUMBER(OFFSET(E13;0;-20));OFFSET(E13;0;-20)>20);1; AND(ISNUMBER(OFFSET(E13;0;-19));OFFSET(E13;0;-19)>19);1; AND(ISNUMBER(OFFSET(E13;0;-18));OFFSET(E13;0;-18)>18);1; AND(ISNUMBER(OFFSET(E13;0;-17));OFFSET(E13;0;-17)>17);1; AND(ISNUMBER(OFFSET(E13;0;-16));OFFSET(E13;0;-16)>16);1; AND(ISNUMBER(OFFSET(E13;0;-15));OFFSET(E13;0;-15)>15);1; AND(ISNUMBER(OFFSET(E13;0;-14));OFFSET(E13;0;-14)>14);1; AND(ISNUMBER(OFFSET(E13;0;-13));OFFSET(E13;0;-13)>13);1; AND(ISNUMBER(OFFSET(E13;0;-12));OFFSET(E13;0;-12)>12);1; AND(ISNUMBER(OFFSET(E13;0;-11));OFFSET(E13;0;-11)>11);1; AND(ISNUMBER(OFFSET(E13;0;-10));OFFSET(E13;0;-10)>10);1; AND(ISNUMBER(OFFSET(E13;0;-9));OFFSET(E13;0;-9)>9);1; AND(ISNUMBER(OFFSET(E13;0;-8));OFFSET(E13;0;-8)>8);1; AND(ISNUMBER(OFFSET(E13;0;-7));OFFSET(E13;0;-7)>7);1; AND(ISNUMBER(OFFSET(E13;0;-6));OFFSET(E13;0;-6)>6);1; AND(ISNUMBER(OFFSET(E13;0;-5));OFFSET(E13;0;-5)>5);1; AND(ISNUMBER(OFFSET(E13;0;-4));OFFSET(E13;0;-4)>4);1; AND(ISNUMBER(OFFSET(E13;0;-3));OFFSET(E13;0;-3)>3);1; AND(ISNUMBER(OFFSET(E13;0;-2));OFFSET(E13;0;-2)>2);1; AND(ISNUMBER(OFFSET(E13;0;-1));OFFSET(E13;0;-1)>1);1; AND(ISNUMBER(E13);E13>0);1)
Perhaps a recursive formula, array-formula, completely different solution?
VBA is unfortunacty a no go since it is blocked from execution.
Best regards
Björn
I'm reach out to you to ask if someone can help suggest improvement to a function for conditional formatting.
I created a calendar where I can plan resources by entering the number of days and the following cells are colored by using conditional formatting.
This far I have only been able to solve the problem by IFS-function and have a ugly solution for up to 21 days. I would like to expend the range to whatever really.
The cells are only to be colored for integer values.
Present formula:
=IFS( AND(ISNUMBER(OFFSET(E13;0;-20));OFFSET(E13;0;-20)>20);1; AND(ISNUMBER(OFFSET(E13;0;-19));OFFSET(E13;0;-19)>19);1; AND(ISNUMBER(OFFSET(E13;0;-18));OFFSET(E13;0;-18)>18);1; AND(ISNUMBER(OFFSET(E13;0;-17));OFFSET(E13;0;-17)>17);1; AND(ISNUMBER(OFFSET(E13;0;-16));OFFSET(E13;0;-16)>16);1; AND(ISNUMBER(OFFSET(E13;0;-15));OFFSET(E13;0;-15)>15);1; AND(ISNUMBER(OFFSET(E13;0;-14));OFFSET(E13;0;-14)>14);1; AND(ISNUMBER(OFFSET(E13;0;-13));OFFSET(E13;0;-13)>13);1; AND(ISNUMBER(OFFSET(E13;0;-12));OFFSET(E13;0;-12)>12);1; AND(ISNUMBER(OFFSET(E13;0;-11));OFFSET(E13;0;-11)>11);1; AND(ISNUMBER(OFFSET(E13;0;-10));OFFSET(E13;0;-10)>10);1; AND(ISNUMBER(OFFSET(E13;0;-9));OFFSET(E13;0;-9)>9);1; AND(ISNUMBER(OFFSET(E13;0;-8));OFFSET(E13;0;-8)>8);1; AND(ISNUMBER(OFFSET(E13;0;-7));OFFSET(E13;0;-7)>7);1; AND(ISNUMBER(OFFSET(E13;0;-6));OFFSET(E13;0;-6)>6);1; AND(ISNUMBER(OFFSET(E13;0;-5));OFFSET(E13;0;-5)>5);1; AND(ISNUMBER(OFFSET(E13;0;-4));OFFSET(E13;0;-4)>4);1; AND(ISNUMBER(OFFSET(E13;0;-3));OFFSET(E13;0;-3)>3);1; AND(ISNUMBER(OFFSET(E13;0;-2));OFFSET(E13;0;-2)>2);1; AND(ISNUMBER(OFFSET(E13;0;-1));OFFSET(E13;0;-1)>1);1; AND(ISNUMBER(E13);E13>0);1)
Perhaps a recursive formula, array-formula, completely different solution?
VBA is unfortunacty a no go since it is blocked from execution.
Best regards
Björn