Forum Discussion
Solved
Change the value in one cell when a date changes occurs in another cell
Hi,
I've seen a few similar posts but can't quite find what I'm looking for, hopefully someone can help.
I'm looking for a macro that will change a value from 'Y' to 'N' when a date change occurs in the previous column, for example as below. If a date change occurs in column C, the value in column D changes to 'N' (column C pulls the date from another location using an XLOOKUP, and I want to be alerted if there is a change date).
Any help would be much appreciated, thanks in advance
It sounds like you want to create a combined chart in your Excel dashboard that shows the growth rate of the disease over the years, with the number of samples and test results expressed as percentages. To achieve this, you can follow these steps:
- Data Preparation: Ensure you have the following data:
- Year (e.g., 2019, 2020, 2021)
- Total Number of Samples for each year
- Number of Positive Results for each year
- Calculate Disease Prevalence Percentage: Calculate the disease prevalence percentage for each year using the formula mentioned earlier:
\text{Prevalence (%) = (Number of Positive Results / Total Number of Samples) * 100}
You should have these prevalence percentages for each year: 2019, 2020, and 2021.
- Create a Combined Chart: To create a combined chart showing both the total number of samples and disease prevalence percentage over the years, follow these steps:
- Select your data, including the years, total number of samples, and prevalence percentages.
- Go to the "Insert" tab in Excel.
- Choose the type of chart you want to create. You can select a clustered column chart for this purpose.
- Excel will create a chart with both bars representing the total number of samples and lines representing the prevalence percentages.
- Format the Chart: You can format the chart as needed, including adding data labels to the bars and lines to display the actual values or percentages, depending on your preference.
- Label Axes and Add Titles: Make sure to label the axes (X and Y) and add titles to the chart to make it clear and informative.
- Finalize Your Dashboard: Place the chart in your Excel dashboard along with any other relevant information or charts you want to include. Ensure that your data is correctly represented, and the chart accurately reflects the growth rate of the disease over the years.
By following these steps, you should be able to create a combined chart in your Excel dashboard that effectively presents the growth rate of the disease using the number of samples and prevalence percentages. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
1 Reply
Got what I needed as below, can't take credit as found this elsewhere and tweaked to my own needs,but seems to do the job...
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rw As Long
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("B:C"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 4 - ActiveCell.Column)
.Value = "N"
End With
Else
rCell.Offset(0, 4 - ActiveCell.Column).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
