Agnarr
New Member
- Joined
- Jan 15, 2023
- Messages
- 29
- Office Version
- 365
- Platform
- Windows
Hello everyone!
I implore your help.
I have a diet log for an office and looks like below. Now there's conditional Formatting to change the colors of the cells according to min and max values i had in column T. (calories, protein, sleep, water, steps, etc)
Now the problem is that they need it the sheet to be able to change the colors after they change the base values.
For example Starting off on 01/01/2024 they had base value for calories 1800 and protein at 170. On 14/01/2024 they changed the value of calories at 1700 and protein remained at 170. They need all cells colored with the initial values to remain like they were, and from 14/01 until they change something again, to be colored according to the new base values... Is something like that even possible to be also working in google sheets?
Is it possible only with VBA?
Please help...
I implore your help.
I have a diet log for an office and looks like below. Now there's conditional Formatting to change the colors of the cells according to min and max values i had in column T. (calories, protein, sleep, water, steps, etc)
Now the problem is that they need it the sheet to be able to change the colors after they change the base values.
For example Starting off on 01/01/2024 they had base value for calories 1800 and protein at 170. On 14/01/2024 they changed the value of calories at 1700 and protein remained at 170. They need all cells colored with the initial values to remain like they were, and from 14/01 until they change something again, to be colored according to the new base values... Is something like that even possible to be also working in google sheets?
Is it possible only with VBA?
Please help...
| Progress Tracking Sheet.xlsx | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
| 245 | ||||||||||||||||||||||
| 246 | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | |||||||||||||||
| 247 | 29/01/2024 | 30/01/2024 | 31/01/2024 | 01/02/2024 | 02/02/2024 | 03/02/2024 | 04/02/2024 | |||||||||||||||
| 248 | WEEK 0 | Work Schedule | Day | Off | 09:00 | 17:00 | 09:00 | 18:00 | 13:00 | 21:00 | 09:00 | 17:00 | Vacation | Time | Sol | Tag | ||||||
| 249 | WorkOut | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | |||||||
| 250 | Meal1 | M | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | |||||||||||||
| 251 | Tu | |||||||||||||||||||||
| 252 | W | |||||||||||||||||||||
| 253 | Th | |||||||||||||||||||||
| 254 | Fr | |||||||||||||||||||||
| 255 | Sa | |||||||||||||||||||||
| 256 | Su | |||||||||||||||||||||
| 257 | WorkOut | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | |||||||
| 258 | Meal2 | M | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | |||||||||||||
| 259 | Tu | |||||||||||||||||||||
| 260 | W | |||||||||||||||||||||
| 261 | Th | |||||||||||||||||||||
| 262 | Fr | |||||||||||||||||||||
| 263 | Sa | |||||||||||||||||||||
| 264 | Su | |||||||||||||||||||||
| 265 | WorkOut | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | |||||||
| 266 | Meal3 | M | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | |||||||||||||
| 267 | Tu | |||||||||||||||||||||
| 268 | W | |||||||||||||||||||||
| 269 | Th | |||||||||||||||||||||
| 270 | Fr | |||||||||||||||||||||
| 271 | Sa | |||||||||||||||||||||
| 272 | Su | |||||||||||||||||||||
| 273 | WorkOut | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | |||||||
| 274 | Meal4 | M | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | |||||||||||||
| 275 | Tu | |||||||||||||||||||||
| 276 | W | |||||||||||||||||||||
| 277 | Th | |||||||||||||||||||||
| 278 | Fr | |||||||||||||||||||||
| 279 | Sa | |||||||||||||||||||||
| 280 | Su | |||||||||||||||||||||
| 281 | WorkOut | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | |||||||
| 282 | Meal5 | M | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | |||||||||||||
| 283 | Tu | |||||||||||||||||||||
| 284 | W | |||||||||||||||||||||
| 285 | Th | |||||||||||||||||||||
| 286 | Fr | |||||||||||||||||||||
| 287 | Sa | |||||||||||||||||||||
| 288 | Su | |||||||||||||||||||||
| 289 | WorkOut | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | |||||||
| 290 | Meal6 | M | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | -------------- | |||||||||||||
| 291 | Tu | |||||||||||||||||||||
| 292 | W | |||||||||||||||||||||
| 293 | Th | |||||||||||||||||||||
| 294 | Fr | |||||||||||||||||||||
| 295 | Sa | |||||||||||||||||||||
| 296 | Su | AVG | TOTAL | |||||||||||||||||||
| 297 | Total calories | 2006cal | 1900cal | 1695cal | 1774cal | 1825cal | 1859cal | 1840cal | 1843cal | 12899cal | ||||||||||||
| 298 | Intermittent Fasting | Total | 14h 19m | 17h 10m | 14h 18m | 16h 15m | 15h 00m | 17h 00m | 14h 27m | 15h 29m | 12h 29m | |||||||||||
| 299 | Period | 22:00 | 12:19 | 20:00 | 13:10 | 21:30 | 11:48 | 21:00 | 13:15 | 21:30 | 12:30 | 20:30 | 13:30 | 00:00 | 14:27 | |||||||
| 300 | Min F.T. | 12:00 | 10:00 | 11:30 | 11:00 | 11:30 | 10:30 | 14:00 | ||||||||||||||
| 301 | Eating Win | 07h 41m | 08h 20m | 09h 12m | 08h 15m | 08h 00m | 10h 30m | 09h 33m | 08h 47m | |||||||||||||
| 302 | Protein | 134 | 180 | 193 | 199 | 182 | 168 | 209 | 180.714286 | 1265 | ||||||||||||
| 303 | Water | 2950ml | 3100ml | 3200ml | 3200ml | 3100ml | 2900ml | 3000ml | 3064ml | 21450ml | ||||||||||||
| 304 | Steps | 7560 | 11003 | 11294 | 10671 | 11224 | 10128 | 12520 | 10629 steps | 74400 steps | ||||||||||||
| 305 | Sleep | 06h 54m | 09h 46m | 06h 00m | 09h 15m | 06h 00m | 07h 00m | 08h 00m | 07h 45m | 52h 15m | ||||||||||||
Log | ||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E247 | E247 | =Q186+1 |
| G247,I247,K247,M247,O247,Q247 | G247 | =E247+1 |
| S301:S305,S297:S298 | S297 | =IF(SUM(E297:R297)=0,"0",AVERAGEIF(E297:R297,"<>0")) |
| T302:T305,T297:T298 | T297 | =SUM(E297:R297) |
| E298,G298,I298,K298,M298,O298,Q298 | E298 | =MOD(F299-E299,1) |
| E300,G300,I300,K300,M300,O300,Q300 | E300 | =E299+$Z$1+TIME(0,1,0) |
| E301,G301,I301,K301,M301,O301 | E301 | =MOD(G299-F299,1) |
| Q301 | Q301 | =MOD(E360-R299,1) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| calories5 | =Log!$E$297:$R$297 | S297:T297 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| E3:R3,E64:R64,E124:R125,E185:R186,E244:R244,E246:R247,E305:R305,E307:R308,E366:R366,E368:R369,E427:R427 | Dates Occurring | today | text | NO |
| E54:R54,E115:R115,E176:R176,E237:R237,E298:R298,E358:R359,E419:R420 | Expression | =IF(E54>$Z$1,"true","false") | text | NO |
| E54:R54,E115:R115,E176:R176,E237:R237,E298:R298,E358:R359,E419:R420 | Expression | =IF(AND(E54>0.01,E54<=$Z$1),"true","false") | text | NO |
| E57:R57,E118:R118,E179:R179,E240:R240,E301:R301,E362:R362,E423:R423 | Cell Value | between 0.000001 and $Z$2 | text | NO |
| E57:R57,E118:R118,E179:R179,E240:R240,E301:R301,E362:R362,E423:R423 | Cell Value | >$Z$2 | text | NO |
| E59:R59,E120:R120,E181:R181,E242:R242,E303:R303,E364:R364,E425:R425 | Cell Value | <$AA$9 | text | NO |
| E59:R59,E120:R120,E181:R181,E242:R242,E303:R303,E364:R364,E425:R425 | Cell Value | >=$AA$9 | text | NO |
| E60:R60,E121:R121,E182:R182,E243:R243,E304:R304,E365:R365,E426:R426 | Cell Value | >=$AA$10 | text | NO |
| E60:R60,E121:R121,E182:R182,E243:R243,E304:R304,E365:R365,E426:R426 | Cell Value | <$AA$10 | text | NO |
| E244:R244,E305:R305,E366:R366,E427:R427,E61:R61,E122:R122,E183:R183 | Cell Value | >=$AA$11 | text | NO |
| E61:R61,E122:R122,E183:R183,E244:R244,E305:R305,E366:R366,E427:R427 | Cell Value | <$AA$11 | text | NO |
| E358:R358,E419:R419,E53:R53,E114:R114,E175:R175,E236:R236,E297:R297 | Cell Value | between $AA$7 and $AA$7+100 | text | NO |
| E53:R53,E114:R114,E175:R175,E236:R236,E297:R297,E358:R358,E419:R419 | Cell Value | >$AA$7+101 | text | NO |
| E53:R53,E114:R114,E175:R175,E236:R236,E297:R297,E358:R358,E419:R419 | Cell Value | between $AA$7 and $AA$7+100 | text | NO |
| E53:R53,E114:R114,E175:R175,E236:R236,E297:R297,E358:R358,E419:R419 | Cell Value | <$AA$7 | text | NO |
| E58:R58,E119:R119,E180:R180,E241:R241,E302:R302,E363:R363,E424:R424 | Cell Value | >$AA$8+11 | text | NO |
| E58:R58,E119:R119,E180:R180,E241:R241,E302:R302,E363:R363,E424:R424 | Cell Value | between $V$2 and $AA$8+10 | text | NO |
| E58:R58,E119:R119,E180:R180,E241:R241,E302:R302,E363:R363,E424:R424 | Cell Value | <$AA$8 | text | NO |
