# Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel and VBA. Beyond the tech stuff, catch her lost in books, explore new places during travels, and enjoy movies and TV series in her downtime.

## Designation

Excel & VBA Content Developer at ExcelDemy in SOFTEKO.

## Education

B.Sc in Naval Architecture & Marine Engineering, BUET.

## Expertise

Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA

## Experience

• Technical Content Writing
• Team Management

## How to Calculate Loan Payment in Excel (4 Suitable Examples)

We will use the following dataset, which contains two columns with Payment Details. Method 1 - Using the PMT Function to Calculate Loan Payments in ...

## Excel Formula for Pass or Fail with Color (5 Suitable Examples)

Most of the time for educational purposes, you may need to use Excel Formula for Pass or Fail with Color. In this article, I will demonstrate how to use Excel ...

## How to Repeat Rows in Excel at Bottom: 5 Easy Methods

Method 1 - Use of Fill Handle Icon to Repeat Rows in Excel at Bottom Steps: Select the row up to which cell you want to repeat the values. We selected ...

## How to Create Table of Contents in Excel (6 Suitable Ways)

We will use a sample dataset, which has 2 Columns, Product and Sales, across 5 worksheets, Dataset, Sales of January, Sales of February, Sales of March, and ...

## How to Add Negative Numbers in Excel – 4 Methods

The sample dataset contains Categories and Expenses. Method 1 - Using the SUMIF Function to Add Negative Numbers in Excel Steps: Select a cell ...

## How to Calculate Annuity Payments in Excel (4 Methods)

Method 1 - Using the PMT Function to Calculate Annuity Payments  Steps: Select cell C9 where you want to calculate the Annual Investment. Enter the ...

## How to Calculate Present Value of Future Cash Flows: 4 Methods

Method 1 - Use of PV Function to Calculate Present Value of Future Cash Flows Steps: Select a different cell, D6, where you want to calculate the present ...

## How to Alternate Row Colors in Excel Without a Table (5 Methods)

The sample dataset below has 4 columns: Product, Sales, Profit, and Status. Method 1 - Using the Fill Color Option to Alternate Row Colors  Steps: ...

## How to Remove Gridlines in Excel When Printing – 6 Methods

The sample dataset showcases Student ID, Test Score, and Status. Method 1 - Using the Page Layout Tab to Remove Gridlines in Excel When Printing If ...

## How to Put Comma After 2 Digits in Excel (9 Quick Methods)

We will use a sample dataset of products and their quantities. Method 1 - Applying LEFT and MID Functions to Put a Comma After 2 Digits in Excel ...

## How to Add Digits to a Number in Excel – 8 Easy Methods

The sample dataset contains 2 columns, Customer Name and Customer ID. Method 1 - Using the Ampersand(&) Operator to Add Digits to a Number in ...

## How to Add Vertical Line in Excel Graph (6 Suitable Examples)

In this article, we will detail 6 ways to add a vertical line in an Excel graph. To illustrate, we'll use the following sample dataset, which contains 3 ...

## [Fixed!] Missing Gridlines in Excel When Printing (5 Solutions)

We will describe 5 possible ways to fix missing Gridlines in Excel when printing. We'll use a sample data set named Missing Gridlines When Printing. It ...

## How to Stretch Excel Spreadsheet to Full Page Print (5 Easy Ways)

Download the Practice Workbook Stretch Spreadsheet to Full Page Print.xlsx 5 Methods to Stretch Excel Spreadsheet to Full Page Print We'll use a ...

## Convert 3 Letter Month to Number in Excel (8 Suitable Methods)

We'll use the following simple dataset with income over a few months. Method 1 - Using the MONTH Function to Convert a 3-Letter Month to Number in ...

Browsing All Comments By: Musiha Mahfuza Mukta
Musiha Mahfuza Mukta Nov 15, 2023 at 12:24 PM

Hello Daphne, the VBA code is perfectly working on my laptop. The code is fine. To run a VBA code you must follow:
1. Save your Excel file in .xlsm format
2. Use the Excel offline version
3. Enable macro content, to do so right click on Excel file >> from the Context Menu Bar >> go to Properties option >> General >> Security >> Unblock
Still, if you face the problem, then please go through this article [Fixed!] Macros Not Working in Excel. Hopefully, this article will help you to solve your issue.
Regards
Exceldemy Team

Musiha Mahfuza Mukta Oct 22, 2023 at 12:10 PM

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

Musiha Mahfuza Mukta Oct 8, 2023 at 12:27 PM

Hello, Sudhir, Thanks for your comment. You need to import external source data for Parish/County information. You can use the following link: u.s. census bureau’s website to get those information.
Also, this site provides the Excel file. So, you can download that file and use this as the source file.

Or, you can copy your needed data from this site and then paste these in your Excel file.
Regards
Musiha

Musiha Mahfuza Mukta Oct 3, 2023 at 12:24 PM

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.

Musiha Mahfuza Mukta Oct 3, 2023 at 11:46 AM

Hello Orly, to fill series with the middle number, you should use a formula. In A1 cell, keep the initial value. Then go to A2 cell >> use this formula–> =”Unit-305/”&TEXT(1+ROWS(\$A\$1:A1),”000″)&”/2023″ >> press Enter. After that, drag the Fill Handle icon up to the last cell.

Formula Breakdown
After Equal Sign, write the first fixed part of your value within an Inverted Comma (“Unit-305/”). Then, use Ampersand Operator to join the formula. Here, inside the TEXT function use summation for the increment of middle number. Also, the TEXT function will consider the mentioned pattern (“001”).
Now, again give the Ampersand Operator, and within another Inverted Comma keep the last part of the value (“/2023”).
Regards
Musiha|Exceldemy

Musiha Mahfuza Mukta Sep 19, 2023 at 11:38 AM

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

Musiha Mahfuza Mukta Sep 7, 2023 at 1:06 PM

Thanks, Dennis, for your comment. You can use Format function of VBA to change the format. Below you can see I have changed the format of current date. The Format function will consider the expression and wanted format. Must use inverted commas for mentioning format.

``Me.TDate.Caption = Format(Date, "dd-mm-yyyy")``

Also, use this Format function in every command button to change the chosen date format.

For your better understanding, I’m mentioning the updated VBA code for new format.

``````Private Sub CommandButton1_Click()
Cells(10, 2) = UserForm1.CsnDate.Caption
End Sub
Private Sub DBttn1_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn1.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn2_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn2.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn3_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn3.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn4_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn4.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn5_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn5.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn6_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn6.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn7_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn7.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn8_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn8.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn9_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn9.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn10_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn10.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn11_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn11.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn12_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn12.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn13_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn13.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn14_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn14.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn15_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn15.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn16_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn16.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn17_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn17.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn18_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn18.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn19_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn19.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn20_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn20.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn21_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn21.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn22_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn22.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn23_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn23.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn24_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn24.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn25_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn25.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn26_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn26.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn27_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn27.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn28_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn28.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn29_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn29.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn30_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn30.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn31_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn31.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn32_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn32.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn33_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn33.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn34_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn34.Caption), "dd-mm-yyyy")
End Sub
Private Sub DBttn35_Click()
Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn35.Caption), "dd-mm-yyyy")
End Sub
Private Sub MnthBox1_Change()
If Me.MnthBox1 <> "" And Me.YrBox2 <> "" Then
Find_my_Date
End If
End Sub
Private Sub UserForm_Initialize()
Me.TDate.Caption = Format(Date, "dd-mm-yyyy")
With Me.MnthBox1
For MnthList = 1 To 12
.AddItem Format(DateSerial(2023, MnthList, 1), "MMMM")
Next MnthList
.Value = Format(Date, "MMMM")
With Me.YrBox2
For YrList = Year(Date) - 4 To Year(Date) + 3
Next YrList
.Value = Format(Date, "YYYY")
End With
End With
Find_my_Date
End Sub
Private Sub Find_my_Date()
Initial_D = DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, 1)
Final_D = DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 2, 1) - 1
For ClearDBttn = 1 To 35
Me("DBttn" & ClearDBttn).Caption = ""
Next ClearDBttn
Me("DBttn" & Weekday(Initial_D)).Caption = 1
For DayDBttn = 1 To 31
If Me("DBttn" & DayDBttn).Caption <> "" Then
If Me("DBttn" & DayDBttn).Caption = Format(Final_D, "dd") Then Exit Sub
Me("DBttn" & DayDBttn + 1).Caption = Me("DBttn" & DayDBttn).Caption + 1
End If
For Dis_able = 1 To 35
If Me("DBttn" & Dis_able).Caption = "" Then
Me("DBttn" & Dis_able).Enabled = False
Else
Me("DBttn" & Dis_able).Enabled = True
End If
Next Dis_able
Next DayDBttn
End Sub``````

You can see the result below.

Musiha Mahfuza Mukta Aug 28, 2023 at 4:21 PM

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")
.OnAction = "'" & ThisWorkbook.Name & "'!CalculateSum"
.Caption = "Sum"
.FaceId = 213
.Tag = "My_Cell_Control_Tag"
End With
.OnAction = "'" & ThisWorkbook.Name & "'!CalculateSelectedAverage"
.Caption = "Average"
.FaceId = 17
.Tag = "My_Cell_Control_Tag"
End With
.OnAction = "'" & ThisWorkbook.Name & "'!CalculateSelectedMedian"
.Caption = "Median"
.FaceId = 50
.Tag = "My_Cell_Control_Tag"
End With
.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.

Musiha Mahfuza Mukta Aug 8, 2023 at 1:23 PM

Thanks, KEVIN, for your suggestion. I have updated the article according to your concern. You may check it now.
Regards
Musiha | Team Exceldemy

Musiha Mahfuza Mukta Jul 23, 2023 at 5:50 PM

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

Musiha Mahfuza Mukta Jul 11, 2023 at 11:17 AM

Thank you, Salome for your comment. I’m very glad that you like the examples.
Now, come to your question. As my understanding, you need to create a custom list for sorting. In that list MARY should be kept at first. I have explained this in 1st method. You can check this. If you want anything else, please let us know in detail.
Thank you.

Musiha Mahfuza Mukta Jul 2, 2023 at 8:23 PM

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.

Musiha Mahfuza Mukta Jun 25, 2023 at 5:20 PM

Thank you, Pankaj for your comment. Yes, there is no built-in process in Excel, but you can manually change the alignment of data labels. I have updated the article according to your comment. You can check the process.

Musiha Mahfuza Mukta Jun 21, 2023 at 6:02 PM

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.

Musiha Mahfuza Mukta Jun 21, 2023 at 3:22 PM

Hello Daniel. You can do this by using the Find & Replace feature of Excel. From the Home tab >> under Editing group >> go to Find & Select option >> choose Replace. Then you will see the Find and Replace dialog box. Write 0 in Find what box >> write =NA() in Replace with box >> press in Find Next button >> if the cell has 0 value then press Replace button >> otherwise press Find Next.
In this way change all the 0 values into =NA(). Don’t press Replace All as there may have numeric 0 with the numbers.
Now insert your chart. You will get both the axis have no zero values. Below, I have attached an image where I used two axis and remove zero value from both axis.

If you still face any problem then please provide us your worksheet in Exceldemy Forum.

Musiha Mahfuza Mukta Jun 13, 2023 at 1:22 PM

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

Musiha Mahfuza Mukta May 3, 2023 at 11:28 AM

Thank you, EAC for your comment. The possible solution is given below.
• Select all the cells by clicking the triangle where row and column headers coincide.
• Next, open the Format Cells by pressing Ctrl+1 >> Select the Protection option >> Uncheck the Locked option to unlock cells >> Click on OK.

• Select the data range which you want to lock.
• Again, press Ctrl+1 >> The Format Cells dialog box will pop up >> Select Protection >> Next check on the Locked option >> Click on OK.
• Go to the Review tab in the ribbon >> Select Protect Sheet from the Protect group.

• A Protect Sheet dialog box will appear >> Set any password in the password box >> Check on the Protect worksheet and contents of locked cells>> Check both Select locked cells, Select unlocked cells.
• A Confirm Password dialog box will appear >> Rewrite your given password >> Click on OK.

Now, try to edit the cells. Then you will get a warning from Microsoft Excel that you can’t change anything. To edit or enter any value, you have unprotected the Excel sheet with that password first.

Furthermore, you can see this article for more details How to Protect Excel Cells from Being Edited.

Musiha Mahfuza Mukta Mar 27, 2023 at 1:53 PM

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.

Musiha Mahfuza Mukta Mar 27, 2023 at 12:44 PM

Thanks, KYLE, for your query. If there are identical values, then it will give same Rank. Also, the RANK function will skip one Rank. For your better understanding, I’m changing the sales value of Janifer to \$9158. So, Zuschuss and Janifer get the same rank (2nd). Thus, the 3rd rank will be missed. Here, you must re-write the Rank of F7 cell to “2” and change the array for INDEX-MATCH function using in G7 an H7 cell. Basically, you need to set the array without Zuschuss information. Below, I have attached the whole scenario.
There is another way, if you want to get unique Rank for all. Like Zuschuss comes first than Janifer so Zuschuss will get 2nd rank and Janifer will get 3rd rank. In this case, you just need to change the formula in D column given below: =RANK(C5,\$C\$5:\$C\$14,0)+COUNTIF(\$C\$5:C5,C5)-1
You don’t need to change the array of INDEX-MATCH function.
Regards
Musiha Mahfuza Mukta| Team Exceldemy

Musiha Mahfuza Mukta Mar 27, 2023 at 11:41 AM

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

Musiha Mahfuza Mukta Mar 9, 2023 at 12:11 PM

Thank you, MICHAEL KAIR for your comment. As per my understanding, there is no duplicate code. Actually, I have written the code part by part with detail description. Also, in the end, I have attached the complete code for making the Date Picker. That’s why, it seems to you the code is used twice but actually, the last one in Step 7 (last code) is the complete one. You should use only that one in your module. And the other codes are for explanation purpose.
I hope this will solve your problem. Please let us know if you face any further problems.

Regards,
Musiha Mahfuza Mukta,
ExcelDemy

Musiha Mahfuza Mukta Feb 27, 2023 at 10:58 AM

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)

Musiha Mahfuza Mukta Feb 19, 2023 at 12:33 PM

Thank you PHUC YU for your comment. Actually, I have tried these methods too and the methods are working perfectly. Here, you can also zoom out the Excel file directly by clicking the Minus(-) sign situated right most corner of the file.

In case of, you are using an older version than 2013 of Excel then these methods may not work. Or, if you have any bugs or issues in your laptop then these would not work. I thing you were facing a different problem which is not related to this articles.

Musiha Mahfuza Mukta Feb 19, 2023 at 11:13 AM

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)),””)

25. Hello MP ROY,
Thank you for your comment. I have tried these codes and they are working perfectly, except the code in Example3. For that particular code, you can use a new workbook. While I was using a new workbook the code has been perfectly worked there. But you have to be careful about the name of worksheet. You have to use exact worksheet number and name in the code.
Regards,
Musiha
Team ExcelDemy

26. 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.

27. Hello, CHRIS.
Thank you for your comment. Actually, with the help of method 4, you are converting numbers into text. But when you re-entered any new value then the cell will hold that value excluding the apostrophe. Basically, the past value along with the apostrophe completely had gone away. So, if you want to keep the apostrophe then you should select that cell (containing new value) and run the Macros again. Then, you will see the apostrophe again with the new value.

Advanced Excel Exercises with Solutions PDF