##### User Posts: Alok

There are several vacation days throughout the year. Usually, companies make a list of those vacation days and send it to the employees. But we can prepare ...

In today's world, copying anything is very common. A watermark is something that is used to protect from copy. We can add watermarks in multiple ways in Excel. ...

There are lots of chart options in Excel. We create a chart based on the data type and our requirements. In this article, we will discuss a special type of ...

There are lots of functions in Excel to perform specific tasks. One of them is this ODD function. We use this function to get odd values. In this article, we ...

Social security is the benefit given to the citizen by the US government. In this article, we will discuss this social security scheme and the break-even ...

Eigenvector is related to linear algebra. When a set of equations forms a square matrix then, we will be able to form eigenvectors of that matrix. Eigenvector ...

Volatility is very common to the investors of the share market. It's a way to get an idea to invest in a new company. It defines the risk of any stock or ...

Kernel Density Estimation is a form of probability distribution function used in statistics. This method is used for nonparametric statistical data. In this ...

Optimization of variables is needed to solve any equation in mathematics. Excel has some tricks for solving equations with less time consumption. The Goal Seek ...

There are different ways to scale data in Excel. We can present data within a range or based on a scaling factor. In this article, we will show different ...

Risk Burndown plan means to lessen the work risk from the higher level to the lower. This value is time-dependent. With time goes, risk lessens. Depending on ...

Excel Solver is used, to optimize equations based on the assumption in Excel. In this article, we will discuss how to do multi-objective optimization using the ...

The text box is one of the lucrative features of Excel. Using text boxes, there are lots of advantages. We can declare anything without writing anything in the ...

Recalculation means calculating the existing formula based on the change of data in Excel. When we input any formula in a cell of Excel, we get the result ...

Truncating a number means decreasing the number of digits from the decimal section or from the right side of that number in mathematics. In Excel, sometimes ...

- 1
- 2
- 3
- …
- 16
- Next Page »

Dear SATHI,Thank you very much for reading this article. Here, you have a query to arrange a 4-digit number in a row from the lowest to highest using a formula. We have found a solution for your mentioned problem in the below section.

● Look at the below image.

A four-digit number is inserted in the dataset.

● First, we will separate the digits of the inserted number using the following formula.

=MID($B5,COLUMN()-(COLUMN($E4)- 1),1)We use the combination of the

MIDandCOLUMNfunction.Formula Explanation:●COLUMN($E4)This will return the column number of the reference.

Result: 5● COLUMN($E4)-1We will subtract 1 from the column number.

Result: 4● COLUMN()It returns the column number of the inserted cell.

Result: 5● COLUMN()-(COLUMN($E4)-1)A subtraction operation will apply here.

Result: 1● MID($B5,COLUMN()-(COLUMN($E4)- 1),1)The

MIDfunction will operate here.Result: 6● Now, we will sort the separated numbers using the formula based on the

SORTfunction.=SORT($E$4:$H$4, ,1,TRUE)You can also download the following Excel file for practice.

Solution Excel Sort Numbers.xlsxDear CHARLES DEL MAR,Thank you very much for reading our articles. You want to add the page number in the cell following the method

Inserting Page Number in Excel Cell. You mentioned that every time you run the code and get “Page 1 of 9”, even in the last cell where it is supposed to show “Page 9 of 9”. Based on your complaint, we checked all the steps and run the VBA code again. We get an accurate result. See the image below.I think you need to check the

Status Barat the bottom of the sheet, where the page number shows. We get the same result showing in theStatus Barafter running the VBA code.Hi HASNAIN AHMAD,

Thank you very much for following our article. You mentioned that even after changing the format of the Date_Time column, it did not work properly. To understand your problem, we follow the full process again and found everything is working properly. We suggest you read the steps again and apply them carefully. Hope you will get the desired result.

Dear PRATIK,

Thanks very much for reading our articles. You have mentioned that you have different lists in a single sheet. Here, we will show how to declare data validation in different cells with different lists in a single sheet.

Steps:● Here, we have three different lists of mobile, laptop, and routers. Here, we will introduce different drop-down lists with multiple selections.

● Now, apply data validation as shown in the article already.

● We will choose

Range B5:B11atCell G4,Range C5:C11atCell G5, andRange D5:D11atCell G6for the data-validation source.● Choose any item from the drop-down list of

Cell G4.● Similarly, choose items for

Cell G5andG6from the drop-down list.● Now, put the following VBA code in the VBA module. After that, save the VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String

Dim Newvalue As String

On Error GoTo Exitsub

If Target.Column = 7 Then

If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then

GoTo Exitsub

Else: If Target.Value = “” Then GoTo Exitsub Else

Application.EnableEvents = False

Newvalue = Target.Value

Application.Undo

Oldvalue = Target.Value

If Oldvalue = “” Then

Target.Value = Newvalue

Else

Target.Value = Oldvalue & “, ” & Newvalue

End If

End If

End If

Application.EnableEvents = True

Exitsub:

Application.EnableEvents = True

End Sub

● Now, choose multiple options from the drop-down list.

Here, we changed the

Target.AddresstoTarget.Columnand put the column number. Also, need to mention the whole column will save the previous result.Dear JEREMIAH,

Thanks for following our article. Here, you have mentioned that after an update you are not getting the default Zoom feature of Excel Online. But we are not facing such kind of problem. Also, one thing needs to add that, when we zoom any Excel sheet that will zoom in or out throughout the sheet, not in specific rows or cells. If we want to apply zoom in or out of a specific row or cell, we have to customize the length and width of that row or cell.

Are you still facing this problem?

Dear RENEE,

Thanks for following our articles. You mentioned a problem regarding the sheet name. Your sheet name will depend on the value of

vcolvariable. And one thing the sheets will split depending on the value of the1stcolumn which is the1stcolumn of the dataset. And, your provided code is not working due to some mistakes. You have made a mistake by setting the ofvcol 7. Set the value ofvcolas1. Furthermore, use the below VBA that will solve your problem correctly.Sub parse_data()

Dim lr As Long

Dim ws As Worksheet

Dim vcol, i As Integer

Dim icol As Long

Dim myarr As Variant

Dim title As String

Dim titlerow As Integer

‘This macro splits data into multiple worksheets based on the variables on a column found in Excel.

‘An InputBox asks you which columns you’d like to filter by, and it just creates these worksheets.

Application.ScreenUpdating = False

vcol = 1

Set ws = ActiveSheet

lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row

title = “A1”

titlerow = ws.Range(title).Cells(1).Row

icol = ws.Columns.Count

ws.Cells(1, icol) = “Unique”

For i = 2 To lr

On Error Resume Next

If ws.Cells(i, vcol) <> “” And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then

ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)

End If

Next

myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))

ws.Columns(icol).Clear

For i = 2 To UBound(myarr)

ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & “”

If Not Evaluate(“=ISREF(‘” & myarr(i) & “‘!1)”) Then

Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & “”

Else

Sheets(myarr(i) & “”).Move after:=Worksheets(Worksheets.Count)

End If

ws.Range(“A” & titlerow & “:A” & lr).EntireRow.Copy Sheets(myarr(i) & “”).Range(“A1”)

‘Sheets(myarr(i) & “”).Columns.AutoFit

Next

ws.AutoFilterMode = False

ws.Activate

Application.ScreenUpdating = True

End Sub

Mr. Bhavnesh

Please read the following article and the 1stg comment. Hopefully, you will get your answer.

Creation of a Mortgage Calculator with Taxes and Insurance in ExcelFor further queries, please email us at

[email protected].Hi BHAVNESH,

Thank you very much for reading our article. For different clients, you can create separate sheets using our Excel template. Yes, it is possible to change the interest rate. Also, that will change all the values accordingly. You can see in the below image, we changed the interest rate from

5%to6.5%. Due to auto-update, all the values changed automatically.You can also change other input values marked by

1. You can change the yearly insurance and taxes rate if changes. There is also the option to change the extra payment in Excel. You can use the IPMT function to calculate the interest part only. For penalty, please describe the situation or share your wordbook. After that, we try to solve that problem. You can mail us at[email protected].Hello KO,Thank you very much for your comment. The formula you mentioned in the comment is working smoothly from our side. If you share your workbook, we will be able to find out the solution to your problem. You can mail us at

[email protected].Dear

RAZAN,Thank you very much for reading our articles. Here, you mentioned that you do not want to get the answer in the form of Yes or No. You want to get the result in numbers. To solve your problem we formed a new formula based on the combination of the

SUMPRODUCT,SUBSTITUTE, andLENfunctions. Here is the formula:`=SUMPRODUCT((LEN($B$5:$B$19)-LEN(SUBSTITUTE($B$5:$B$19,$D5,"")))/LEN($D5))`

This formula will find out the specific word from the

Range B5:B19and return the sum in number.If you want to get the number of a specific word from each cell you can follow this article.

Excel Formula to Count Specific Words in a Cell (3 Examples)Hi MIRA,

It’s a great pleasure that you are watching our articles. You have a query regarding this article. You want to make an Excel sheet, where the dates of the Excel sheet will change according to the sheet name. Yes, you can do this. Read the below steps to get your solution.

Steps:● First, take an Excel sheet with the

DateandAttendancecolumns.We will insert dates in the

Datecolumn based on theSheet Name.● Before that, we will form a table that consists of the month’s name and serial number.

● Now, go to

Cell E5and put in the following formula.`=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)`

It will extract the name of the Sheet which is a month name.

Formula Explanation:●

CELL(“filename”,A1)This finds the location of the Excel file with the Sheet name.

Result:D:\Alok\[Excel-Sheet-Name-From-Cell-Value.xlsx]January

●

FIND(“]”,CELL(“filename”,A1))This will find out the location of the symbol mentioned in the formula from the location.

Result: 47

●

FIND(“]”,CELL(“filename”,A1))+1Add 1 with the previous result.

Result: 48●

MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256)Separate the sheet name from the location.

Result: January● After that, put the following formula on

Cell F5.=INDEX(Month,MATCH(E5,Month[Month],0),2)This returns the serial number of the month comparing the values of the table.

● Now, use the

DATEfunction onCell B5.=DATE(2022,$F$5,1)● Fill up the

DateandAttendancecolumns.● Now, change the sheet name from

JanuarytoFebruaryand look at the changes that take place in the Date column.Download File:Solution.xlsxRegards.

– Alok Paul

Author at ExcelDemy

Hi JORGE,

It’s a bit difficult to answer this, not seeing your spreadsheet. Can you please send a sample to

[email protected]?Prior to that, check the following Excel file. We have added a 12-month example for you. Let us know if this helps.

Download Link:Solution.xlsxNote:You must have data of 2021 and 2022 for forecasting 2023.

Hello, JITENDRA!

Hope you are doing well. You have a query to get the product name and min & max sell price monthly basis. We have to use the following formulas for that.

For Max values:=MAXIFS($E$5:$E$17,$D$5:$D$17,$G$4)For Min values:=MINIFS($E$5:$E$17,$D$5:$D$17,$G$4)For Product Name:=INDEX($B$5:$E$17,MATCH($J$6,$E$5:$E$17,0),1)We just need to change the month name of

Cell G4. Hope you will get your desired solution. Regards-Alok Paul

Author at ExcelDemy

Hi JUAN MARTIN OPACAK,

Thanks for reading our articles. We have found a solution to fulfill your requirement. You need to insert the following macros with the existing VBA code.

`Worksheets(ActiveSheet.Index + 1).Select`

`ActiveSheet.Name = (Worksheets(2).Range("A1").Value) & " " & ActiveSheet.Name`

Here, Worksheets(2).Range(“A1”).Value defines we want to insert the text of Cell A1 of Worksheet 2 with the current sheet name.

Have a look at the image below, where to insert the mentioned VBA code.

Hi NAYAZ,

Thanks for following our article. We have checked the article again and found no errors. Please go through the whole article again and create the template. Or you can do this easily by downloading the given template.

Keep in mind that if you want to import stock prices from any other website, then it will not work. You should use “^NSEI” in the ticker box.

Let us know if your problem is fixed.

Regards.

-Alok Paul

Author at ExcelDemy

Hello, DANIEL.

Thanks for reading our articles.

Look at the below link. Hopefully, you will get your solution.

https://www.exceldemy.com/extract-data-from-one-sheet-to-another-in-excel-using-vba/

For example, you can use the following code:

`Sub Extract_Data()`

`Selection.Copy`

`Sheets("Dataset2").Activate`

`Range("F4").Select`

`ActiveSheet.Paste`

`Application.CutCopyMode = False`

`End Sub`

Enter your sheet name instead of Dataset2 in the 3rd line. Change the cell range in the 4th line. Hope you will get desired output. If your problem is yet solved, then let us know.

Regards.

-Alok Paul

Author at ExcelDemy

Hello, CL.

Of course, it’s possible to solve your mentioned problem!

You will need to get the ASCII code of the respective keys. Now, open a new Excel file and follow all the steps written in this article, i.e. downloading Barcode fonts 128, installing them, creating a UDF, and the next steps. Now insert the ASCII codes of the respective keys and you will get your desired Barcode. See what we have got.

So, what you need to do yourself, just finding the ASCII codes! What else to do are already mentioned in this article.

Note: You may need to open a new file because as per the new update of Excel, a VBA code will be disabled in a downloaded xlsm file. Or you can solve the problem following

this way.Hi DAVID,

Thank you very much for your appreciation. Follow our website ExcelDemy for other problems, and hope you will always get the best solutions.

Hello JEFF WHALE,

Thank you very much for following our articles.

You mentioned that your sample code is not working properly. We attached a VBA code that will help you to solve this problem. You need to choose a cell from the dataset that contains an ISBN number when running the code. One thing adding that change the location of Chrome according to your computer.

And you are getting this 404 error because without any ISBN number this https://www.isbnsearcher.com/books/ will show an error by default.

`Sub OpenstrHyperlinkInChrome()`

`Dim strChromeLocation As String`

`Dim strURL As String`

`Dim strISBN As String`

`strISBN = Application.InputBox("Please Select Desired Cell", Type:=8)`

`strURL = "https://www.isbnsearcher.com/books/" & strISBN`

`strChromeLocation = """C:\Program Files\Google\Chrome\Application\chrome.exe"""`

`Shell (strChromeLocation & "-url " & strURL)`

`End Sub`

Hi BARNEY! Hope you are doing well. Thanks for your nice compliments. We are happy to know that the readers find our articles useful.

However, the problem you are facing is not quite clear from what you have told us. Are you trying to sum up entries that meet specific criteria? In that case, you have to use the

SUMIF functionif you have to meet just a single criterion. If you have multiple criteria, then you have to use theSUMIFS function. There are more articles in our blog related to these functions. To explore them, scroll down and click on the function tag names.If this is not what you are looking for, please let us know more details. You can send me the problem with a sample file at

[email protected]or at[email protected].Best wishes. Keep staying with us.

-Alok Paul

ExcelDemy Team

Hi KATIE WILDER,

You probably have missed this part. The solution to your problem is already given in the article.

Click the below link and will get the solution.

https://www.exceldemy.com/make-excel-read-only-with-password/#Disable_Excel_Read-only-recommended_Option

If I am not wrong, this is what you are searching for. As far as we know, you cannot undo protection to a password-protected file, but save a copy of it without password protection. If this is not your case, please let us know a bit more details.

Thanks for being with us.

Hi JEFF! Thanks for your nice compliment. To remove the InputBox and make the code always select Column 1, just remove the InputBox command and variable VCL. After that, replace the VCL with 1.

You can directly use the following code:

`Sub Split_Data()`

`Dim L As Long`

`Dim DS As Worksheet`

`Dim XCL As Long`

`Dim MARY As Variant`

`Dim title As String`

`Dim titlerow As Integer`

`Application.ScreenUpdating = False`

`Set DS = ActiveSheet`

`L = DS.Cells(DS.Rows.Count, 1).End(xlUp).Row`

`title = "A1"`

`titlerow = DS.Range(title).Cells(1).Row`

`XCL = DS.Columns.Count`

`DS.Cells(3, XCL) = "Unique"`

`For X = 2 To L`

`On Error Resume Next`

`If DS.Cells(X, 1) <> "" And Application.WorksheetFunction.Match(DS.Cells(X, 1), DS.Columns(XCL), 0) = 0 Then`

`DS.Cells(DS.Rows.Count, XCL).End(xlUp).Offset(1) = DS.Cells(X, 1)`

`End If`

`Next`

`MARY = Application.WorksheetFunction.Transpose(DS.Columns(XCL).SpecialCells(xlCellTypeConstants))`

`DS.Columns(XCL).Clear`

`For X = 2 To UBound(MARY)`

`DS.Range(title).AutoFilter field:=1, Criteria1:=MARY(X) & ""`

`If Not Evaluate("=ISREF('" & MARY(X) & "'!A1)") Then`

`Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = MARY(X) & ""`

`Else`

`Sheets(MARY(X) & "").Move after:=Worksheets(Worksheets.Count)`

`End If`

`DS.Range("A" & titlerow & ":A" & L).EntireRow.Copy Sheets(MARY(X) & "").Range("A4")`

`Next`

`DS.AutoFilterMode = False`

`DS.Activate`

`Application.ScreenUpdating = True`

`End Sub`

You are most welcome, MANUEL!

We provide the best and easy solutions to Excel-related problems. You are invited to visit our blog for more such articles.

If the last two rows contain the same data, then it fails to delete both rows. Otherwise, it works.