Ironman
Well-known Member
- Joined
- Jan 31, 2004
- Messages
- 1,069
- Office Version
- 365
- Platform
- Windows
Hi
Cells i1 and i4 contain text-related formulas.
I would be grateful for the following:
1) The text in i1 changes to "More hours exercised than last month" when the value in cell J1 is greater than J2
2) The text in i4 changes to "More miles run than last month" when the value in cell J4 is greater than J5
Either using formulas or VBA.
Many thanks!
Cells i1 and i4 contain text-related formulas.
MrExcel (no codes).xlsx | ||||
---|---|---|---|---|
I | J | |||
1 | NUMBER OF HOURS EXERCISED NOVEMBER TO DATE | 3 | ||
2 | TOTAL NUMBER OF HOURS EXERCISED IN OCTOBER | 33 | ||
3 | ||||
4 | NUMBER OF MILES RUN NOVEMBER TO DATE | 11 | ||
5 | TOTAL NUMBER OF MILES RUN IN OCTOBER | 80 | ||
Training Log |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I1 | I1 | =" NUMBER OF HOURS EXERCISED" & UPPER(TEXT(TODAY()," MMMM")& " TO DATE") |
J1 | J1 | =(SUMIFS(D12:D23357,A12:A23357,"<="&EOMONTH(TODAY(),0),A12:A23357,">"&EOMONTH(TODAY(),-1)))+ExerciseBikeHrsThisMth+WalkingHrsThisMth+OutdoorBikeHrsThisMth |
I2 | I2 | =" TOTAL NUMBER OF"&UPPER(" HOURS EXERCISED IN "&TEXT(EOMONTH(TODAY(),-1),"MMMM")) |
J2 | J2 | =(SUMIFS(D12:D23357,A12:A23357,"<="&EOMONTH(TODAY(),-1),A12:A23357,">"&EOMONTH(TODAY(),-2)))+ExerciseBikeHrsLastMth+WalkingHrsLastMth+OutdoorBikeHrsLastMth |
I4 | I4 | =" NUMBER OF" & UPPER(" MILES RUN "&TEXT(TODAY(),"MMMM")& " TO DATE") |
J4 | J4 | =SUMIF(Last90Dates,">"&$K$2,Last90Miles) |
I5 | I5 | =" TOTAL NUMBER OF"&UPPER(" MILES RUN IN "&TEXT(EOMONTH(TODAY(),-1),"MMMM")) |
J5 | J5 | =SUMIF(Last90Dates,">"&$K$3,Last90Miles)-J4 |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
'Training Log'!_FilterDatabase | ='Training Log'!$A$11:$L$8778 | J4:J5 |
'Training Log'!All_Log_Dates | =OFFSET('Training Log'!$A$12,0,0,'Training Log'!$B$10) | J1:J2 |
'Training Log'!All_Log_Times | =OFFSET('Training Log'!$D$12,0,0,'Training Log'!$B$10) | J1:J2 |
Last90Dates | =OFFSET(LastLogDate,0,0,-90) | J1:J2 |
Last90Times | =OFFSET(Last90Dates,0,3) | J1:J2 |
LastLogDate | =OFFSET('Training Log'!$A$11,'Training Log'!$B$10,0) | J1:J2 |
LastRunDate | =OFFSET('Training Log'!$A$11,'Training Log'!$B$10,0) | J1:J2 |
Log_LastDate | =INDEX('Training Log'!All_Log_Dates,ROWS('Training Log'!All_Log_Dates)) | J1:J2 |
LogYear_Log_Dates | =INDEX('Training Log'!All_Log_Dates,MATCH(DATE(LogYear-1,12,31),'Training Log'!All_Log_Dates,1)+1):INDEX('Training Log'!All_Log_Dates,ROWS('Training Log'!All_Log_Dates)) | J1:J2 |
LogYear_Log_Times | =INDEX('Training Log'!All_Log_Times,MATCH(DATE(LogYear-1,12,31),'Training Log'!All_Log_Dates,1)+1):INDEX('Training Log'!All_Log_Times,ROWS('Training Log'!All_Log_Dates)) | J1:J2 |
x | =OFFSET(LastLogDate,0,0,-90) | J1:J2 |
'Training Log'!YTD_Dates | =OFFSET('Training Log'!$A$5491,0,0,366) | J1:J2 |
'Training Log'!YTD_Times | =OFFSET('Training Log'!YTD_Dates,0,3) | J1:J2 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
J4 | Expression | =J4>J5 | text | YES |
J1 | Expression | =J1>J2 | text | YES |
I would be grateful for the following:
1) The text in i1 changes to "More hours exercised than last month" when the value in cell J1 is greater than J2
2) The text in i4 changes to "More miles run than last month" when the value in cell J4 is greater than J5
Either using formulas or VBA.
Many thanks!