Change text in cell when formula result changes

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
Hi

Cells i1 and i4 contain text-related formulas.

Cell Formulas
RangeFormula
I1I1=" NUMBER OF HOURS EXERCISED" & UPPER(TEXT(TODAY()," MMMM")& " TO DATE")
J1J1=(SUMIFS(D12:D23357,A12:A23357,"<="&EOMONTH(TODAY(),0),A12:A23357,">"&EOMONTH(TODAY(),-1)))+ExerciseBikeHrsThisMth+WalkingHrsThisMth+OutdoorBikeHrsThisMth
I2I2=" TOTAL NUMBER OF"&UPPER(" HOURS EXERCISED IN "&TEXT(EOMONTH(TODAY(),-1),"MMMM"))
J2J2=(SUMIFS(D12:D23357,A12:A23357,"<="&EOMONTH(TODAY(),-1),A12:A23357,">"&EOMONTH(TODAY(),-2)))+ExerciseBikeHrsLastMth+WalkingHrsLastMth+OutdoorBikeHrsLastMth
I4I4=" NUMBER OF" & UPPER(" MILES RUN "&TEXT(TODAY(),"MMMM")& " TO DATE")
J4J4=SUMIF(Last90Dates,">"&$K$2,Last90Miles)
I5I5=" TOTAL NUMBER OF"&UPPER(" MILES RUN IN "&TEXT(EOMONTH(TODAY(),-1),"MMMM"))
J5J5=SUMIF(Last90Dates,">"&$K$3,Last90Miles)-J4
Named Ranges
NameRefers ToCells
'Training Log'!_FilterDatabase='Training Log'!$A$11:$L$8778J4: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
CellConditionCell FormatStop If True
J4Expression=J4>J5textYES
J1Expression=J1>J2textYES


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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
=IF(J1>J2,UPPER(" **MORE HOURS EXERCISED THIS MONTH THAN "&TEXT(EOMONTH(TODAY(),-1),"MMMM"))&"**"," NUMBER OF"&UPPER(" HOURS EXERCISED "&TEXT(TODAY(),"MMMM")&" TO DATE"))

=IF(J4>J5,UPPER(" **MORE MILES RUN THIS MONTH THAN "&TEXT(EOMONTH(TODAY(),-1),"MMMM"))&"**"," NUMBER OF"&UPPER(" MILES RUN "&TEXT(TODAY(),"MMMM")&" TO DATE"))
 
Upvote 0
Solution

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top