Musiha Mahfuza Mukta

Thank you so much for pointing out this issue, Debbie! I wholeheartedly express my gratitude for the valuable time you dedicated to sharing your expertise and aiding others in addressing this matter.
I have added the limitations of Excel (regarding date format) in the article too. Thanks again!
Regards,
Musiha|Exceldemy

Thanks, IFN, for your comment. Here, the entire article explains how to create a Date Picker. But to get the drop-down menu, you should use more steps. So, after the completion of Date Picker, use the following steps.
Step1: Select the cell where you want to keep the drop-down list >> from Data tab >> Data Tools group >> Data Validation >> Data Validation >> in the dialog box, go to Settings >> List >> Select range (the listed items including “Calendar” word) in the Source box >> press OK. As a result, you will get a drop-down list in that selected cell.

Step2: In the UserForm1, drag a new Command Button >> double click on Command Button >> write the following Code.

Private Sub CommandButton1_Click()
Cells(10, 2) = UserForm1.CsnDate.Caption
End Sub

Use your preferred cell reference.

Step3: In B2 cell >> use this formula >> “=B3”

Step4: In VB Editor, from Insert tab >> Module >> write the following code in Module1.

Sub running()
If Range("A1").Cells(2, 2).Value = "Calendar" Then
UserForm1.Show
End If
End Sub

Step5: Go to Worksheet >> right click on sheet name >> from Context Menu Bar >>View Code >> write the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("B2")
If Target.Value = "Calendar" Then
Call running
End If
End Sub

Step6: Now save the code >> go back to sheet >> check the output. When you choose “Calendar” from the dropdown list >> you will get the date picker visible >> choose your date >> press Command Button1 >> get the chosen date in B10 cell. If you get any error notice, then just press “End” to that notice.

When you selected the “Calendar” word, then with every change doing in your worksheet, you will get the date picker. So, if you need to write any other things, then first change the “Calendar” word from the drop down list.

Hey Anita,
Sorry for the issues you are facing. If these methods don’t work, you can break the link to disable the updates. To break links, go to the Data tab >> select Edit Links >> select the link >> click on Break Link.

I hope this will help you solve the problem. Here, you can try some more options like changing the name of the Source file. Also, you should change the location of the Source file. It will stop Excel from connecting the existing file with the Source file.
Furthermore, if you don’t want to update your file, you can use the Copy-Paste(Value Only) feature for transferring data from a Source file.
Regards
Musiha|ExcelDemy

Thank you Dave Gilblom, for your comment. Yes, Excel can do this. You should use some VBA codes for this. Below, I am attaching these codes.
–> In the Module 1 write the following code. Which will return the Sum of selected cells.

Sub CalculateSum()
    Dim selectedRange As Range
    Dim selectedArea As Range
    Dim cell As Range
    Dim sumValue As Double
    On Error Resume Next
    Set selectedRange = Application.Selection
    On Error GoTo 0
    If Not selectedRange Is Nothing Then
        sumValue = 0
        For Each selectedArea In selectedRange.Areas
            For Each cell In selectedArea
                If IsNumeric(cell.Value) Then
                    sumValue = sumValue + cell.Value
                End If
            Next cell
        Next selectedArea
       MsgBox "Sum: " & sumValue
    Else
       MsgBox "No valid range selected."
    End If
End Sub

–> In the Module 2 write the following code. Which will return the Average of selected cells.

Sub CalculateSelectedAverage()
    Dim selectedRange As Range
    Dim avgValue As Double
    ' Check if any cells are selected
    If Selection.Cells.count > 0 Then
        ' Set the selected range
        Set selectedRange = Selection
        ' Calculate the average of the selected range
        avgValue = WorksheetFunction.Average(selectedRange)
        ' Display the result in a message box
        MsgBox "The average value of selected cells is: " & avgValue
    Else
        MsgBox "No cells are currently selected."
    End If
End Sub

–> In the Module 3 write the following code. Which will return the Median of selected cells.

Sub CalculateSelectedMedian()
    Dim selectedRange As Range
    Dim cell As Range
    Dim valuesArray() As Double
    Dim medianValue As Double
    Dim i As Long, j As Long
    Dim temp As Double
    ' Check if any cells are selected
    If Selection.Cells.count > 0 Then
        ' Set the selected range
        Set selectedRange = Selection
        ' Copy selected cell values to an array
        ReDim valuesArray(1 To selectedRange.Cells.count)
        i = 1
        For Each cell In selectedRange
            valuesArray(i) = cell.Value
            i = i + 1
        Next cell
        ' Sort the values array using bubble sort (simple and not efficient)
        For i = LBound(valuesArray) To UBound(valuesArray) - 1
            For j = i + 1 To UBound(valuesArray)
                If valuesArray(j) < valuesArray(i) Then
                    temp = valuesArray(i)
                    valuesArray(i) = valuesArray(j)
                    valuesArray(j) = temp
                End If
            Next j
        Next i
        ' Calculate the median based on sorted values array
        If UBound(valuesArray) Mod 2 = 0 Then
            medianValue = (valuesArray(UBound(valuesArray) \ 2) + valuesArray(UBound(valuesArray) \ 2 + 1)) / 2
        Else
            medianValue = valuesArray(UBound(valuesArray) \ 2 + 1)
        End If
        ' Display the result in a message box
        MsgBox "The median value of selected cells is: " & medianValue
    Else
        MsgBox "No cells are currently selected."
    End If
End Sub

–> In the Module 4 write the following code. Which will return the standard deviation of selected cells.

Sub CalculateSelectedStdDeviation()
    Dim selectedRange As Range
    Dim cell As Range
    Dim valuesArray() As Double
    Dim sum As Double
    Dim mean As Double
    Dim varianceSum As Double
    Dim stdDeviation As Double
    Dim count As Long
    ' Check if any cells are selected
    If Selection.Cells.count > 0 Then
        ' Set the selected range
        Set selectedRange = Selection
        ' Copy selected cell values to an array
        ReDim valuesArray(1 To selectedRange.Cells.count)
        count = 0
        For Each cell In selectedRange
            valuesArray(count + 1) = cell.Value
            sum = sum + cell.Value
            count = count + 1
        Next cell
        ' Calculate the mean
        mean = sum / count
        ' Calculate the sum of squared differences for variance
        For i = 1 To count
            varianceSum = varianceSum + (valuesArray(i) - mean) ^ 2
        Next i
        ' Calculate the variance and standard deviation
        If count > 1 Then
            variance = varianceSum / (count - 1)
            stdDeviation = Sqr(variance)
        Else
            variance = 0
            stdDeviation = 0
        End If
        ' Display the result in a message box
        MsgBox "The standard deviation of selected cells is: " & stdDeviation
    Else
        MsgBox "No cells are currently selected."
    End If
End Sub

–> Finally, in the ThisWorkbook >> write the following code.

Private Sub Workbook_Open()
Dim cBar As CommandBar
 Set cBar = Application.CommandBars("Cell")
 With cBar.Controls.Add(Type:=msoControlButton, Before:=1)
 .OnAction = "'" & ThisWorkbook.Name & "'!CalculateSum"
 .Caption = "Sum"
  .FaceId = 213
 .Tag = "My_Cell_Control_Tag"
 End With
  With cBar.Controls.Add(Type:=msoControlButton, Before:=1)
 .OnAction = "'" & ThisWorkbook.Name & "'!CalculateSelectedAverage"
 .Caption = "Average"
  .FaceId = 17
 .Tag = "My_Cell_Control_Tag"
 End With
  With cBar.Controls.Add(Type:=msoControlButton, Before:=1)
 .OnAction = "'" & ThisWorkbook.Name & "'!CalculateSelectedMedian"
 .Caption = "Median"
  .FaceId = 50
 .Tag = "My_Cell_Control_Tag"
 End With
 With cBar.Controls.Add(Type:=msoControlButton, Before:=1)
 .OnAction = "'" & ThisWorkbook.Name & "'!CalculateSelectedStdDeviation"
 .Caption = "StdDeviation"
  .FaceId = 2130
 .Tag = "My_Cell_Control_Tag"
 End With
End Sub

–> Now, save the code >> press on Run button >> close the Excel file >> re open the file >> select some cells >> right click >> from the Context Menu Bar.

–> Then, choose the desire operation >> get the answer in MsgBox.

Thanks for your comment, Mojtaba. You can use VBA code which should be written in the original sheet. For example, I have a sheet named “Dataset“. I have divided this sheet into 3 sheets based on row. The names of these three sheets are Sheet5, Sheet6, and Sheet7. Now, write click on “Dataset” >> from the Context Menu Bar >> select View Code.

Write the following code in VB Editor.

Private Sub Worksheet_Change(ByVal Target As Range)
x = Target.Value   
Set MyRange = Sheets("Dataset").UsedRange.Find(x)
Y = MyRange.Row
Z = MyRange.Column
If Y > 11 Then
Target.Copy Destination:=Sheets("Sheet7").Range("A1").Cells(Y - 8, Z)
ElseIf Y > 7 Then
Target.Copy Destination:=Sheets("Sheet6").Range("A1").Cells(Y - 4, Z)
ElseIf Y > 3 Then
Target.Copy Destination:=Sheets("Sheet5").Range("A1").Cells(Y, Z)
End If
End Sub

Here, you must change the sheet names according to your workbook. Then you have to modify the conditions. Here, in my dataset there was 15 used rows. In the separated sheets there was 4 rows for each of them (except column headers). So, I set the conditions as row number > 11/7/3. So, when you change any cell value that value will be updated in the corresponding sheet. Like, if I change the cell value of C8 cell, then the change will be done in C4 cell of Sheet6 (as row number was 8).
So, set all the conditions properly for all sheets, then with any change of the original sheet, you will get the updated values in other sheet too.
Still, if you don’t get my point, then please comment or email us with the workbook. We will try to solve your problem.
Regards
Musiha/Exceldemy

Thanks, Eliana Elia, for your comment. Step1: Select the cell where you want to keep the drop down list >> from Data tab >> Data Tools group >> Data Validation >> Data Validation >> in the dialog box, go to Settings >> List >> Select range (the listed items including “Calendar” word) in the Source box >> press OK. As a result, you will get a drop-down list in that selected cell.

Step2: In the UserForm1, drag a new Command Button >> double click on Command Button >> write the following Code.

Private Sub CommandButton1_Click()
Cells(10, 2) = UserForm1.CsnDate.Caption
End Sub

Use your preferred cell reference.

Step3: In B2 cell >> use this formula >> “=B3”

Step4: In VB Editor, from Insert tab >> Module >> write the following code in Module1.

Sub running()
If Range("A1").Cells(2, 2).Value = "Calendar" Then
UserForm1.Show
End If
End Sub

Step5: Go to Worksheet >> right click on sheet name >> from Context Menu Bar View Code >> write the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("B2")
If Target.Value = "Calendar" Then
Call running
End If
End Sub

Step6: Now save the code >> go back to sheet >> check the output. When you choose “Calendar” from the dropdown list >> you will get the date picker visible >> choose your date >> press Command Button1 >> get the chosen date in B10 cell. If you get any error notice, then just press “End” to that notice.

When you selected the “Calendar” word, then with every change doing in your worksheet, you will get the date picker. So, if you need to write any other things, then first change the “Calendar” word from the drop down list.

Here you must use IFERROR function to get blank cell for null values. Again, you need to apply formula like if there is no data then the date will be blank also.

You must use Date format as Horizontal axis. Then you will get the chart auto updated up to valued cells. Double-click on Horizontal axis >> from Format Axis window (right side of Excel sheet) >> Axis Options >> Axis Type >> check Date axis.

Thank you, Bibhuti Sutar, for your comment. Here, you can hide the cells which you want to deselect. So, only the wanted values will be visible. In this case, you can use the following VBA code. For example, I used the given dataset. Here, I want to deselect/remove the cities named New York, Dallas, and California.

Sub show_defined_values()
  For i = 1 To 15
    If Range("B4:D15").Cells(i, 2).Value = "New York" Then
        Range("B4:D15").Cells(i, 2).EntireRow.Hidden = True
    ElseIf Range("B4:D15").Cells(i, 2).Value = "Dallas" Then
       Range("B4:D15").Cells(i, 2).EntireRow.Hidden = True
      ElseIf Range("B4:D15").Cells(i, 2).Value = "California" Then
       Range("B4:D15").Cells(i, 2).EntireRow.Hidden = True
    End If
Next i
End Sub

You must edit this according to the range. If it doesn’t work, then please inform us with more details in the reply or you can send us your workbook in Exceldemy forum.
Regards
Musiha Mahfuza|Exceldemy

Thank you, CARLOS for your comment. You have to use the correct array (Sales Rep
or B5:B14) in INDEX function and Rank Column (D5:D14) in MATCH function. Also, while using the Fill Handle icon, you have to freeze both arrays. The most important part, you must write Rank 1,2,3.. manually in General format in F column.
There may have extra space or Apostrophe (‘) in the F column where you insert Rank numbers manually. You should remove all extra spaces.
You can see our article related MATCH function error. The link is: https://www.exceldemy.com/excel-match-function-not-working/#Case_1_NA_Error
For getting basic idea of INDEX-MATCH function you can see the examples from this article https://www.exceldemy.com/excel-index-match-example/
Still, you are facing the problem then please comment with your used formula and sample dataset.
Regards
Musiha Mahfuza Mukta| Team Exceldemy.

Hi ATIF, Thanks for your comment. Here, I made a dataset keeping the Text value in A column, Date value in B column and Counter will be in C column. If you provide your Excel file, then it will be more useful. As per my understanding, I am providing the following VBA code.
From Developer tab >> go to Visual Basic >> Insert a Module >> copy the code in that >> from Macros >> Run the code.

Sub Days_Counter()
Count = 0
For i = 1 To 11
If Range("B1").Cells(i) <> Range("B1").Cells(i + 1) _
And Range("A1").Cells(i) = "Normal" Then
    Count = Count + 1
    Range("C1").Cells(i) = Count
Else: Range("C1").Cells(i) = 0
End If
Next i
End Sub

You can see the outcome below.

Where the counter counts single value for same date. I have highlighted the same date. Also, for “Abnormal” text the counter didn’t count.
In the code, you must mention total Row number of your dataset in For Next loop.
If you want only the text part, you can remove this portion

Range("B1").Cells(i) <> Range("B1").Cells(i + 1)

from code.
Hopefully this will work. If you are still facing problem, then please comment with more details or the sample dataset.
Regards
Musiha Mahfuza Mukta| Team Exceldemy

Thank you ZOYSA for your comment. Below, I have attached two formulas for your problem.
I have written the data from the A2 cell and C2 cell. According to your question, I have considered the dataset till A10 and C10.
Firstly, write the below formula in the E2 cell or the cell from where the NAME will be started.
=IF(A2=”TOM”,C2,””)

Then copy this formula up to E10 or your dataset’s end cell.
Then use another formula in the F4 cell.
=SUM(E2:E10)

Thank you ROB for your comment. Here, ROW function is working as the row index number of the INDEX function. Actually, it should be ROW(B2:D9)=> {2;3;4;5;6;7;8;9} which denotes respectively the 2nd, 3rd, 4th…. up to 9th row number of this ($B$4:$D$12) array of INDEX function. Here, we ignore the 1st row of ($B$4:$D$12) this array as 1st row contains the Club Name’s not any player Name’s.
On the other hand, you have to use B4:D4 row in the MATCH function which will search for the Club Name’s and act as the column index number in the INDEX function.
Say, when you choose Borussia Dortmund or C4 cell from the list of C14 cell then the MATCH function will return 2 and thus the column index number of INDEX function will be 2 so INDEX function will give all the rows (except 1st one) of 2nd column of the given array which means all the player name’s of the Borussia Dortmund club.
So, you can use the formula like the below one. =IFERROR(INDEX($B$4:$D$12,ROW(B2:D9),MATCH($C$14,$B$4:$D$4,0)),””)

Hello, J KUMAR.
Thank you for your comment. I have tried the code too and the code is absolutely right and perfectly working. But you are facing problem because most probably your device is running out of virtual memory. So, when you are trying this code in Excel, at that time you should close all other applications. Also, you should use an individual module for this code.