About author
## Designation

Excel & VBA Developer at ExcelDemy in SOFTEKO.
## Lives in

Savar, Dhaka, Bangladesh.
## Education

B.sc in Institute of Information Technology, Jahangirnagar University, Bangladesh.
## Expertise

VBA, C#, .NET, Python, JavaScript, HTML, SQL, and technical content writing.
## Experience

## Summary

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess a lot.

- Technical Content Writing
- Software Development
- Accounting System (Using Excel and VBA)

- Undergraduate Projects
- Cafeteria Management System (JavaScript, Java, MySQL)
- A StegoCrypt System (Using the ideas of Cryptography and Steganography)

- Currently working as Excel & VBA Developer of ExcelDemy
- Started technical content writing of Excel & VBA in November 2022

This is an overview: Download Practice Workbook Download the practice workbook. Trendline in Excel.xlsx How to Add a Trendline ...

Lutfor Rahman Shimanto
Aug 2, 2024

This tutorial will provide an ultimate guide on Excel Bar Chart topics: You will learn to insert a bar chart using features, shortcuts and VBA. We'll ...

Lutfor Rahman Shimanto
Jun 15, 2024

Here's a basic overview of where you can find the Insert Object command. Download the Practice Workbook Insert Object.xlsm What Are ...

Lutfor Rahman Shimanto
Aug 1, 2024

Method 1 - Use the Format Cells Window Click on cell C5 in your Excel worksheet. Enter the following formula in the formula bar: =B5/24 ...

Lutfor Rahman Shimanto
Jul 29, 2024

In the following overview image, clicking on any cell of the Link column will redirect to the corresponding sheets. Download the Practice Workbooks ...

Lutfor Rahman Shimanto
Jul 3, 2024

Download Practice Workbook Organize Sheets.xlsm Example 1 - Apply Shortcuts to Highlight Sheet Select range B5:D9 >> press Alt+H+L+N. ...

Lutfor Rahman Shimanto
Jul 6, 2024

Suppose you have the following dataset to use for a 3D Scatter Plot. Insert a Scatter Chart Steps: Select the active range (B4:D16 in this ...

Lutfor Rahman Shimanto
Jul 6, 2024

Here's an overview of converting coordinates into latitude and longitude. Download the Practice Workbook Easting Northing to Lat Long.xlsm ...

Lutfor Rahman Shimanto
Jun 28, 2024

Cubic Spline Interpolation is a curve-fitting method to interpolate a smooth curve between discrete data points. We use this interpolation in various ...

Lutfor Rahman Shimanto
Apr 30, 2024

One of the most helpful software you can use is Microsoft Excel. It is possible to do an endless number of things with a dataset by utilizing Excel's ...

Lutfor Rahman Shimanto
Apr 29, 2024

This article will discuss two causes and solutions for why the Number Format might not be working. Number Format Is Not Working in Excel: 2 Reasons ...

Lutfor Rahman Shimanto
Jun 16, 2024

The fundamental goal of inventory control is to guarantee that sufficient items or resources are available to fulfill demand without generating surplus ...

Lutfor Rahman Shimanto
Apr 30, 2024

Method 1 - Establish a Data Model for Rainflow Counting Theorem Create three columns titled Indicator, Damage-weight, and Time Interval. Build ...

Lutfor Rahman Shimanto
Jul 5, 2024

To illustrate using the IF function with the not equal to operator, let's examine a representative dataset. First Collection and Second Collection are columns ...

Lutfor Rahman Shimanto
Jun 16, 2024

What Is Drop Down List? A drop-down list allows the user to choose text or numbers from a list rather than typing them into a cell. To insert a drop-down ...

Lutfor Rahman Shimanto
Jun 17, 2024

Browsing All Comments By: Lutfor Rahman Shimanto

Hello Raj

Thank you for reaching out with your comment. You encountered a different result than what was described in the post. I assume that you missed inserting positive numbers in the range C16:C17. However, once those values were included, I discovered a result identical to what is described in the post. Therefore, It’s essential to ensure all intended data is inputted correctly.

Regards

Lutfor Rahman Shimanto

Hello Charlotte Fahey

Thank you for reporting on this fascinating issue. I experience the same problems when a postal code begins with 0, and it is essential to preserve the leading zero when entering data into the system or application.

The Postal Code column must be formatted as text. Following that, insert the desired data.

Now, adhere to the methods mentioned in this article. Ideally, you will observe the desired results.

Regards

Lutfor Rahman Shimanto

Thank you for bringing this issue to my attention, William Wyatt. I understand that you have been experiencing difficulties using formulas on cells formatted as fractions in your workbook. I apologize for any confusion or frustration this may have caused you.

I have gone through this article and did not experience any of your issues. I am using Microsoft 365 to investigate this case. Could you share your workbook with us via email to better understand your situation? I would appreciate it if you could assist me more effectively.

Regards

Lutfor Rahman Shimanto

Hello DEBB WOLFE

We appreciate your comment. I understand your difficulty, and you can avoid the issue by importing the CVS file into the existing worksheet.

Change the column type in the Power Query window to text, as this article mentions. Next, select the Home tab. Select Close & Load and then Close & Load To at a later time.

As a result, the Import Data window will display. Check the Existing Worksheet and then press OK.

Thus, you will be able to solve the problem.

Regards

Lutfor Rahman Shimanto

Hello ARON HOLMBERG

Thank you for reporting your issues. To count the number of components for each station, when the Station ID may be in one of three different columns, you can use the SUMPRODUCT function.

=SUMPRODUCT((InputSheet!$B:$B=B5)+(InputSheet!$C:$C=B5)+(InputSheet!$D:$D=B5))

This formula will test each of the three columns for the station ID and return 1 if it’s present in any of the columns and 0 if it’s not. Then, SUMPRODUCT will sum up the results, giving you the components for the station.

This solution is more elegant than creating a new column that combines the three columns, as it avoids the need to manipulate the data. If you would like a copy of the illustrated workbook, please click the link provided below this section.

https://www.exceldemy.com/wp-content/uploads/2023/01/To-Count-the-Number-of-Components-for-Each-Station.xlsx

Best regards,

Lutfor Rahman Shimanto

Hello ABIGAYLE PAULSON

Thank you for reporting on this fascinating issue. I have reviewed this article and found an interesting idea to solve your problem. For illustration, let’s walk through 2nd Example. Filtering the dataset (Sheet3) based on cell values on another sheet (Sheet4). The context filters the dataset for Apple or Tomato products.

VBA Code:

If the changed cell is either C2 or E2, the macro will execute and filter the data in “Sheet3” based on the new values in these cells. Note that the Worksheet_Change event must be placed in the code module for the “Sheet4” worksheet.

The changes we must make to the original code to create the auto-filtering behavior:

1)We have to add a Private Sub Worksheet_Change(ByVal Target As Range) procedure to the code module for the “Sheet4” worksheet. This procedure runs automatically whenever a cell value is changed on this worksheet.2)We can add an If statement to check whether the changed cell is in Sheet4 and C2 or E2. If so, the macro continues executing; if not, it exits without doing anything.3)We need to move the Dim statements for product1 and product2 inside the If statement, so they are only declared if the macro is going to run.4)The rest of the macro code is the same as the original code, so it will apply the same filter to “Sheet3” based on the new values in “Sheet4” whenever the cell value changes.By adding this Worksheet_Change procedure to the code module, the macro will run automatically whenever a change is made to the specified cells in “Sheet4” without manually opening and running the macro.

Regards

Lutfor Rahman Shimanto

Hello

ChrisThanks for your comment! You want to insert multiple leave records at a time.

I have reviewed your requirements. I am delighted to inform you that I have found an idea that uses an Excel UserForm to fulfil your goal. Please check the following:

To improve the Excel file, I had to write many lines of code and adjust many features, so I am not explaining how I did this. You can down the improved file from the following link: https://www.exceldemy.com/wp-content/uploads/2024/06/Chris-SOLVED.xlsm

Hopefully, you will like the idea. Good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello Sherry

Thanks for reaching out! Providing an ultimate solution without glancing at your Excel file is difficult. However, I suggest several things you may check to ensure the multi-selection drop-down works properly.

Ensure you have created named ranges for your dataset as described. Make sure that the strDVList variable correctly references your named range. To ensure that the named range CityNames is correctly referenced by

strDVList, you can call the InitializeDVList subroutine in theWorksheet_SelectionChangeevent before it tries to usestrDVList.So, double-click on the user form and replace the existing code with the following:

Hopefully, these ideas will help you overcome your situation. Good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Cathy ThompsonThanks for visiting our blog! We cannot provide an ultimate solution without reviewing your Excel file and being remote. However, you apply several adjustments to the dataset, like checking for non-numeric characters, converting text to numbers, ensuring correct formatting, and checking calculation mode. To clean up your numeric data, you can create a helper numeric column (e.g., real estate tax) and use the formula:

`=VALUE(TRIM(CLEAN(B1)))`

Lastly, sum the values in the helper columns using theSUMfunction.Hopefully, these ideas will help. If you still have difficulties, you can share your problem with the

ExcelDemy Forumby attaching your Excel file. Good luck.Regards

Lutfor Rahman ShimantoExcelDemyHello

OnkarThanks for your invaluable feedback!

When copying data from the Excel files, you wanted a sub-procedure to copy only the header from the first file and skip the header row for the subsequent files. Currently, you are getting the runtime error 1004 with the existing code, which is typically caused by issues with object references or out-of-bound ranges.

Don’t worry! I have reviewed your problem and improved the existing sub-procedure to fulfil your goal. Please check the following:

Improved Excel VBA Sub-procedure:Hopefully, with the code, you will not get any runtime error, and you will be able to copy the header only from the first filter, skipping the header row for the other files. I have attached the solution workbook used to solve your problem. You can download it for better understanding. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

JohnThanks for your compliments! Your appreciation means a lot to us.

I have reviewed your requirements. To do so, first, you must create helper cells to store the dates associated with each checkbox when marked TRUE. Next, use the MAX function to find the latest date from these helper cells. Finally, the latest date calculates the new expiration date in A27. Please check the following:

Follow these steps:

Hopefully, these ideas will help you reach your goal. I have attached the solution workbook as well. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemyHello

ShazThanks for your question! You want to link checkboxes directly to the same cells where these are presented.

To do so, insert a check box and edit the text like described here. Next, select the checkbox by holding the

Ctrlkey. Now, type the cell reference in the formula bar where the checkbox is located. Please check the following:You can download the workbook used to solve your problem: https://www.exceldemy.com/wp-content/uploads/2024/06/Shaz-SOLVED.xlsx

Regards

Lutfor Rahman ShimantoExcelDemyHello

David WangThanks for your kind words! You are very welcome.

I have reviewed both of your requirements. These requirements can quickly be developed, and I think they will overcome all your hassles. I have made the necessary changes. Please check the following:

To fulfil your goal, I had to make many changes to the codes and design, develop several sub-procedures and event procedures, and add the necessary validation. As there are many more things, I am not describing everything here. If you are interested in how I developed such a customized date picker, you can post your queries in the

ExcelDemy Forum.Hopefully, you have found the solution you were looking for. I have attached the Date Picker file. Good luck.

DOWNLOAD CUSTOMIZED DATE PICKERRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Lisa HofferThanks for visiting our blog and sharing such an interesting question. You wanted an Excel VBA sub-procedure to clear the contents of non-contiguous rows. I have developed such a sub-procedure to fulfil your goal. Please check the following:

Excel VBA Sub-procedure:Hopefully, you have found the VBA macro you were looking for. I have attached the workbook used to solve your problem. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

RobertThanks for your question! You want to calculate the number of weeks between two dates. To do so, you can apply several formulas:

Hopefully, you have found the solutions you were looking for. I have attached the solution workbook as well. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemyDear

JessThanks for your comment! Only leading spaces typically don’t affect the

SUMfunction in most Excel versions. However, leading spaces can indeed cause numbers to be treated as text in some Excel versions like yours. It is great to hear that removing leading spaces resolved the issue for you.I am sharing another exciting solution for summing numbers with inconsistent spaces using an Excel formula. Please check the following:

Excel Formulas(usingSUM,VALUE, andSUBSTITUTEfunctions):`=SUM(VALUE(SUBSTITUTE($B$2:$B$7, " ", "")))`

Hopefully, you will like the solution. Good luck.

Regards

Lutfor Rahman ShimantoExcelDemyHello

Michael A. DunnThanks for visiting our blog and noticing a critical fact. You were right. Sorry for the inconvenience. We have improved all the formulas and modified the article.

Using the arithmetic formula would be best if you worked with a fixed rate. So, it may provide different results from other procedures. However, all the procedures except for using the arithmetic formula will calculate almost the same result. So, if you do not have a fixed rate, consider applying the formulas Using

Nested IF,IFSandSUMPRODUCTfunctions.Regards

ExcelDemyDear

DougThanks for visiting our blog and sharing an exciting problem. You needed help with some Excel VBA sub-procedures to move rows from the Account sheet to the Archive sheet under specific conditions. You want this to happen only when you click a button on the Metrics sheet and turn it on. The conditions are as follows: if a row in the Account sheet has Closed or Archive in its Status column. You also wanted a pop-up to confirm the action before moving a row. Additionally, the row should be deleted from the Account sheet after moving.

Don’t worry! I have reviewed your requirements and demonstrated the situation within an Excel file with a suitable dataset. I have solved the problem with the help of some

Excel VBA sub-procedures. Please check the following:Excel VBA Sub-procedures:Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello there! Thanks for sharing an exciting problem. Since using the conditional formatting option for text alignment is impossible, you can only use an Excel VBA procedure.

Note: The event procedure will trigger when range B2:B7 is changed. If the cell value is Red, it will apply left alignment. For Blue, it will be center alignment; for the other values, it will apply left alignment. You can modify the code based on your needs.Excel VBA Event Procedure:Hopefully, the solution will fulfil your goal. Download the attached solution workbook for a better understanding.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyDear, Thanks for pointing out the fact! You are right. The

Alignmenttab in theFormat Cellsdialog box is unavailable when setting up conditional formatting rules. So, using the conditional formatting option for text alignment is impossible.Don’t worry! There is an idea of using an

Excel VBA event procedure. Please check the following:The event procedure will trigger when range

B2:B7is changed. If the cell value isRed, it will applyleft alignment. ForBlue, it will becenter alignment; for the other values, it will applyleft alignment.Excel VBA Event Procedure:Hopefully, the solution will fulfil your goal. I have attached the solution workbook as well. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyDear, Thanks for your compliment! That’s fantastic to hear! We are glad the template was helpful. Keeping track of sales data can be overwhelming, but a well-designed sales tracker can make a difference.

Regards

ExcelDemy

Dear Naqavi

Thanks for your compliment! Your appreciation means a lot to us.

Regards

ExcelDemy

Hello

UmarThanks for visiting our blog and sharing an exciting problem. You want to apply Data validation in two columns, with options in the second column dependent on the first column selection.

Don’t worry! I have demonstrated your situation within an Excel file and solved it. Please check the following:

You can download the solution workbook for a better understanding: https://www.exceldemy.com/wp-content/uploads/2024/06/Umar-SOLVED.xlsx

Regards

Lutfor Rahman ShimantoExcelDemyDear

AnnieThanks for visiting our blog and sharing an important fact! You are right that the holiday range reference shifts when you copy the formula. You need to use

absolute references for the holiday rangeto fix the issue. Another improvement you can consider is not keeping the holiday list and start date or next working dates in the same column. Assume the start date and next working date data are in columns B and C; the holiday list is in column E.Don’t worry! I have demonstrated an improved Excel file to overcome the situation. Please check the following:

Follow these steps:Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemyHello

BrandyThanks for your wonderful compliment! Your appreciation means a lot to us.

The

Error 13 Type Mismatchin VBA typically occurs when you try to perform an operation on incompatible data types. I have reviewed the code and found thatValue2is used to contain cell values when looping through and comparing with Value1. It seems like some of your values contain errors, which is why it is not possible to use theLenfunction with this value. So, to avoid this type of situation, you can useIsErrorto check whether the cells contain any errors or not. If not, perform an operation; otherwise, do nothing.You can use the following structure:

Hopefully, you have found the ideas you were looking for. Good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

MaryThanks for visiting our blog and sharing your problem. The issue you are facing is due to the

RANDfunction, which generates a new random number every time the worksheet recalculates.To prevent the problem, copy the random values, then use

Paste Specialand paste them back asValues. The idea should keep your random names stable.Regards

ExcelDemyDear, Thanks for your question! Unfortunately, it isn’t possible to disable copying directly within Excel Online. Excel Online offers some control over how users interact with a shared workbook, but it doesn’t provide a direct way to turn off copying explicitly. You can set the workbook to

Read Onlyfor users who cannot copy data.Regards

Lutfor Rahman ShimantoExcelDemy

Hello

LukeThanks for sharing your problem with such clarity. Based on your requirements, you can use the following Excel VBA Sub-procedure to fulfil your goal.

Excel VBA Sub-procedure:Hopefully, the sub-procedure will be helpful. Stay blessed.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

LukeThanks for reaching out and sharing an exciting problem.

You want to generate a comma-delimited (

.txt) file from anExcelsheet. When saving asCSV, you want to remove thedouble quotesthat Excel automatically adds. The text file should have each data point separated by commas, without any quotes surrounding the text.Don’t worry! I have developed an Excel VBA code to help you overcome your situation. Please check the following:

Excel VBA Sub-procedure:I hope you have found the solution you were looking for. I have attached the solution workbook. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Nancy CruzThanks for visiting our blog! And also sharing your problem with such clarity. I have reviewed your issue with date formatting and come up with a solution. Please check the following:

Follow these steps:Home.Cellsgroup, expandFormatand click onFormat Cells.// As a result, the Format Cells window will open up.Format Cellswindow,Numberand click onCustom.Typesection:`mm/dd`

OK.// As a result, the date will be formatted as expected like the following:

Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemyHello

PabloThanks for visiting our blog and sharing your queries! You can modify the existing code slightly to update specific columns or rows in

Google SheetsfromExcel. Follow the previously mentioned steps, ensure you have the necessary permissions for the Google Sheet and adjust these with the VBA code.Excel VBA Code:Things to keep in mind: To adjust the VBA code, replace YOUR_SPREADSHEET_ID with your actual Google Sheet ID, and replace your YOUR_CLIENT_ID and YOUR_CLIENT_SECRET with the values from your OAuth 2.0 credential JSON file. Adjust the rangeName to specify the exact range (column/row) you want to update in the Google Sheet. Next, modify dataToUpdate to include the data you want to update. After running the VBA code, it will open a browser for you to authenticate with Google; enter the authorization code.Hopefully, the code will fulfil your goal. Good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Rahul DixitThanks for visiting our blog and sharing such an exciting problem! I have reviewed your problem and come up with an E

xcel VBA User-definedfunction. Please check the following:Excel VBA Code:Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello,

Rekayasa Perangkat Lunak Aplikasi!Thank you for your question. The main purpose of the Excel sales template provided in the article is to facilitate the recording and analysis of sales data for any merchandising business. Also, this comprehensive template includes various sections, such as a sales summary, a sales recording table, sales performance analyses, and a sales plan template. It helps businesses

track sales transactions,compare planned versus actual sales, andanalyze overall sales performance, enabling more informed decision-making and strategic planning.If you have any further questions about the article or the templates, feel free to ask!

Regards

ExcelDemyDear

Jon PeltierThanks for your invaluable feedback and suggestions! You are correct about the positive percentage error bars being inserted incorrectly. Based on your suggestions, we have updated the article section.

Regards

ExcelDemyHello

Parvez AlamThanks for the compliment! You can include the “

RefreshAll” command to refresh all Power Query connections. Don’t worry! I have improved the code a bit to fulfil your goal.Excel VBA Code:I hope you have found the code you were looking for. Good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

VickieThanks for visiting our blog and sharing an exciting problem with such clarity. I have reviewed your situation and developed two sub-procedures to fulfil your goal. Your problem is about automating email alerts in Excel based on conditional formatting for expiry dates of lifting equipment test certificates.

Don’t worry! I have demonstrated it in an Excel workbook. Please check the following:

Excel VBA Sub-procedure:I hope you have found the sub-procedures you were looking for. I have attached the solution workbook as well. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

MohammedThanks for sharing your problem! However, we can not accurately get the corresponding latitude and longitude values using only the

UTM EastingandUTM Northing. We also needUTM Zone,Easting,andNorthingvalues to getLatandLongvalues.For your address, the UTM Zone would be

39Z. Let’s say your UTM Easting, UTM Northing, and UTM Zone are 585302.3, 27464684.41, and 39Z. Using this UTM information, you can get the corresponding Latitude and Longitude. To achieve your goal, I have developed some Excel VBA User-defined functions.Follow these steps:Hopefully, these user-defined functions will help. I have attached the solution workbook as well.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

DavidThanks for visiting our blog! For your address, the

UTM Zonewould be32U. Let’s say yourUTM Easting,UTM Northing, andUTM Zoneare691609.5,5334764.67, and32U. Using this UTM information, you can get the correspondingLatitudeandLongitude. To achieve your goal, I have developed some Excel VBA User-defined functions.Follow these steps:Hopefully, these user-defined functions will help you reach your goal. I have attached the solution workbook as well. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyDear

SanjeevThanks for visiting our blog and sharing your problem. After adding the desired rows, you must drag the

Fill Handleicon to copy the existing formulas for new employees. We have improved the file and made the necessary formula adjustments based on your goal.SOLUTION Overview:You can download the solution file: https://www.exceldemy.com/wp-content/uploads/2024/05/Sanjeev-Fernandes-SOLVED.xlsx

Regards

Lutfor Rahman ShimantoExcelDemyDear

JenThanks for explaining your requirements clearly. I have reviewed the existing user-defined function and tried to improve it to achieve your goal. The improved user-defined function will try to populate

house numbers,roads, cities, statesandzip codesby takingLatandLongvalues; if any item is missing, it will display an extension likeIncomplete!Follow these steps:I hope you have found the user-defined function of reverse geocoding you were looking for. I have attached the solution workbook as well. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

WattThanks for visiting our blog and sharing your queries. I have reviewed your requirements. Unfortunately, you cannot directly use any formulas to add a hidden apostrophe.

Formulascan only output visible characters; not even aUser-defined functioncan do that. So, you can type theapostrophebefore the number or text to do so, though it is very exhausting when it comes to lots of cells.Don’t worry! I have developed a

sub-procedurethat will add a hidden apostrophe to cells in a selected range without displaying it with one click.SOLUTION Overview:Excel VBA Sub-procedure:Hopefully, you will find the solution helpful. I have attached the solution workbook as well; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyDear

IonaThanks for visiting our blog and sharing your requirements! I have reviewed your goal and created an

Excel VBA Eventprocedure (assuming your dates are incolumn A).Excel VBA Event Procedure:Right-click on the sheet name tab, paste the given code into the sheet module and save it. Hopefully, the idea will fulfil your goal; good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

JamesThanks for your compliment! We are glad these ideas helped you a lot.

You are facing difficulties in excluding blank cells that hold a formula from the print area. The following code can give you ideas; here, the

HasFormulaproperty is used, along with checking whether it is empty or not.Excel VBA Code:I hope the ideas will help you exclude blank cells holding a formula. Good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyDear

AbinshThanks for sharing your requirements. You want to convert numbers to words in

Qatar Riyal. Don’t worry! I have modified the previously given code to fulfil your goal.Excel VBA User-Defined Function:I hope you have found the solution you were looking for. I have attached the solution workbook. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHi

DiwThanks for thanking me! All three ideas clear the

Excel memory cache. However, they differ in the approaches and memory they target.So, it would be better to clear PivotCache Memory for

heavy workbooks. You can apply Nothing Literal to any unwanted object forgeneral memory cleanup. For aslight memory boost, you can assign zero to RecentFile Properties.I hope these ideas will help you. Thanks once again for visiting our blog. And good luck.

Regards

Lutfor Rahman ShimantoExcelDemyHello

Fazlay RabbyThanks for your compliment! You are very welcome. We are glad that you found the article helpful.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

BarryThanks for your compliment. Your appreciation means a lot to us. Thanks once again for sharing an exciting problem.

I have reviewed your requirements. You wanted to place small rectangles at each of the

12 positions on a clock face. Besides, each shape will be placed atan angle of 30 degrees; more than its neighbor. Don’t worry! I have come up with a sub-procedure and a user-defined function to fulfill your goal.SOLUTION Overview:Excel VBA Sub-procedure:I hope you have found the solution, you were looking for. I have attached the solution workbook as well; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHi

NatThanks for your patience and feedback. We apologize for any confusion. While the article covers two common reasons for date filter issues, other factors might be at play in your case.

You mentioned trying the two solutions and not getting the desired results. We recommend joining our

ExcelDemy Forum. You can post your question there and attach yourExcel filefor a more detailed look.Regards

Lutfor Rahman ShimantoExcelDemyDear

HarmanThanks for thanking me. You are most welcome. We are glad the solution worked perfectly.

Regards

ExcelDemyHello

FuladThanks for your compliment! As you requested, you can use the following dataset, which contains data with your mentioned fields:

You can download the workbook from the following link:

DOWNLOAD WORKBOOKI hope you have found the dataset you were looking for; good luck.

Regards

Lutfor Rahman ShimantoExcelDemyHello

OlaideThanks for thanks! You are very welcome. Your appreciation means a lot to us.

You are right! The user-defined function works perfectly fine for a few sets of coordinates. I have reviewed the code and estimated the time required to perform it on a pair of coordinates; it takes

5.7seconds on my machine (the time can vary from device). It will take almost8 hoursto perform5000 setsof coordinates with a spontaneous internet connection.The user-defined function mentioned in this article uses the

OpenStreetMap Nominatim APIto perform reverse geocoding. It retrieves location information based on the lat and long values given to it. It must take the required time to perform perfectly.So, it is impossible to reverse geocode 5000 sets of coordinates within minutes. If you ever feel like getting location information without applying the user-defined function like the regular Excel function, you can use the following sub-procedure. You need to run the code, and it will consider the lat and long values as columns B and C starting from row 3 and display the result in column D.

I hope you understand the situation. Stay blessed.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Alan AngThanks for visiting our blog and asking an interesting question. You seek the language codes for

Simplified Chinese,Traditional Chinese, andCentral Khmer. Please check the following table:You can easily translate and fulfil your goal using the above language codes and the user-defined function mentioned in this article.

I hope you have found the language code you were looking for. I have attached the workbook used to investigate your question; good luck.

DOWNLOAD WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemyDear, Thanks for visiting our blog and sharing your questions. You want to modify the existing formula to deal with the southerly direction of the bearings. The provided formulas in the article work for any bearing, regardless of direction. This is because the

SINandCOSfunctions account for the direction within their calculations.Southerly bearingstypically range from180to270 degrees. The formulas will handle these values fine and automatically calculate the appropriate change inNorthingandEastingbased on the southerly direction. The cosine value for bearings in this range will be negative, meaning there will be a decrease in Northing because the reference point is likely north of the new point. Likewise, the sine value will be positive, meaning an increase in Easting because a southerly direction moves the point eastward.So, you don’t need to modify the formulas for southerly directions. They will work as intended.

Regards

Lutfor Rahman ShimantoExcelDemyDear

AdamThanks for your feedback. I have reviewed your requirements. It seems like you want to perform a lookup of

multiple entries(country names) ina single cell separated by a delimiter, such as commas. There will be alookup tableto keep the corresponding short names for each country. You are looking for a complex formula that takes multiple country names separated by commas, returnscorresponding values(country short names), and displays them ina single cell.Don’t worry! I have demonstrated your situation and developed a complex formula to fulfil your goal. I used the

TEXTJOIN,INDEX,MATCH,TRIM, andTEXTSPLITfunctions to build the formula.SOLUTION Overview:Suppose you want to enter the comma-separated country names in cell

E1. In cellE2, you want to display country short names. To do so,I hope you have found the formula you were looking for. I have attached the solution workbook as well; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemyHello Harman

Thanks for visiting our blog and sharing your query. You want 0 as leading when returning a number against a character. Don’t worry! I have come up with two solutions:

Use of TEXTJOIN, TEXT & VLOOKUP FunctionsUse of VBA User-Defined FunctionI hope the formulas and VBA code will fulfil your goal. I have attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

AnonThanks for your question! You wanted formulas to check for changes in multiple columns. Assuming you have three columns: First Name, Middle Name, and Last Name, you want to check for changes in the Timestamp column.

Solution Overview:Follow these steps:Now, input the intended names to see the output, like the

GIF above.I hope these are the formulas you were looking for. I have attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemyHello

Charlie VThanks for visiting our blog and sharing your ideas. You are talking about shortcut keys to hide all rows or columns.

Do worry! We have demonstrated the whole idea. Please check the following

GIF:RequiredShortcut Keys to Hide All Rows or Columns:>>

Ctrl + Shift + Right Arrowto select all columns to the right.>>

Ctrl + 0to hide the selected columns.>>

Ctrl + Shift + Down Arrowto select all rows to the bottom.>>

Ctrl + 9to hide the selected rows.Hopefully, these are the shortcut keys you were looking for. Once again, thanks for the comment; good luck.

Regards

Lutfor Rahman ShimantoExcelDemyHello

Steve ZimmermannThanks for visiting our blog and sharing your problem. You mentioned that the existing article methods work fine for regular Excel but not when opened inside

SolidWorks(SW). Instead of showing the cell color, you get a#BLOCKED!Error; it shows up when a required resource can not be accessible.It seems there are compatibility issues between

VBA functionalitiesandSolidWorks. When Excel is opened by other applications like SolidWorks, some features and functionalities may not be available. As a result, when some resources are unavailable, they show #BLOCKED!To overcome your situation, you can check the settings related to external scripting. You can also reach out to the SolidWorks community forum. Providing a solution without glancing at your file and being remote is very tough. I hope these ideas will help you; good luck.

Regards

Lutfor Rahman ShimantoExcelDemyHello

AbdulThanks for visiting our blog and sharing your requirements. You wanted to calculate the number of hours worked by each employee in a month. You also mentioned that you have

4 guards,2 on dayand2 on nightshifts. If each shift is 6 hours long and there are 4 shifts in a day, you can calculate the total hours worked by multiplying 6 by the total shits.You have demonstrated your situation within an Excel file. You can download it by clicking the following link.

DOWNLOAD SOLUTION WORKBOOKNote: If you want to display more detailed information like calculating exact amount of work time, you must create columns for start and end times. Then, calculate the hours worked for each shift by subtracting the start time from the end time.I hope these ideas will help you overcome your problem; good luck.

Regards

Lutfor Rahman ShimantoExcelDemyDear

Peter BergerThanks for your feedback. Perhaps you are right about using the direct results from Nominatim, which would lead to more accurate outcomes. However, we have to get the result from the response from the Nominatim API.

In addition to your previous post, you wanted to get all the information in a column but in different cells. Do not worry! I have improved the previously given code by using a 2D array.

SOLUTION Overview:Required Excel VBA User-Defined Functions:Hopefully, you have found the idea helpful. Download the solution workbook. Stay blessed.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Peter BergerThanks for your nice words. Your appreciation means a lot to us.

You wanted to get all the address information in different columns. To do so, I have developed another assistive VBA user-defined function and improved the existing user-defined function named

ReverseGeocoder. Using these two functions, you can quickly fulfil your goal.Follow these steps:I hope you have found the VBA user-defined function helpful. I have attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

James MartinThanks for visiting our blog and sharing your problem. When working with this code in another workbook, you are facing “

Compile error: User-defined type not defined“. This error typically occurs when the required library or reference is not activated in theVBA Editor.Initial Problem:To overcome the problem, you must refer to the

Microsoft VBScript Regular Expressions 5.5library in VBA Editor.SOLUTION Overview:Press

Alt+F11>>Tools>>References>>Microsoft VBScript Regular Expressions 5.5>>OK.I have improved the codes and solved your problem in a workbook. You can find these codes in the modules. Hopefully, you have found the solution helpful. I have attached the solution workbook as well; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyDear

Vyshnav V SIt is good to see you again. Thanks for sharing further requirements. You want to improve the existing VBA code to add

a hyperlinkin the email content. The hyperlink should display only thetask ID, but when clicked, it will direct to theALM linkalong with the task ID.I have improved the previously given code to fulfil your goal. When sending an email, ensure that you uncomment the line that contains the “.

Send” property. Also, change the existing base AML link with the intended one.SOLUTION Overview:Improved Excel VBA Sub-procedure:I hope the improved sub-procedure will reach your goal. I have attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

AdeleThanks for your nice words. We are glad you found the article helpful!

In Excel, we are directly unable to undo a

VBA macroaction in the same way you can undo regular actions using theCtrl + Zshortcut. So, you must use theExcel Track Changesfeature, or you can also use external version control systems likeGit.I hope these ideas will overcome your situation; good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

Harry IngramThanks for such an interesting comment. You wanted to extract numbers even if there are decimal points. Additionally, if there are more numbers, you want to put them in different columns. I have come up with a solution using several Excel VBA User-defined functions.

SOLUTION Overview:To do so, follow these steps:I have also attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Vyshnav VSIt is good to see you again. When sending a reminder email, you want to add a user interface instead of running the

Excel VBA Sub-procedure. To achieve this goal, you must use anExcel UserForm. I have designed a user interface in anExcel File.SOLUTION Overview:I hope the solution will help you reach your goal. I have also attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyDear

Vito CasalinuovoThanks for further clarifying your problem. Based on the requirement, I have come up with another solution, though the previous solution works perfectly on our end.

Assuming you have a dataset like the following:

You want to get all the tasks that fall on the same day. To achieve the goal, you can combine:

I hope these formulas will help you to reach your goal. I have attached the solution workbook as well; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemyHello

GregThanks for visiting our blog and informing us that the previous solution worked perfectly. You want to filter the data that includes the date falling within the specified date range (0 to 14 days). Also, you want to include the rows where the document submittal date is blank.

I have come up with an Excel VBA code. You can try it; if needed, make changes to fulfil your goal.

SOLUTION Overview:Excel VBA Code:I have attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyDear

Vito CasalinuovoIt is good to see you again. Yes! You can capture all the tasks that fall on the same day. To do so, use the

IFERROR, TEXTJOINandFILTERfunction:SOLUTION Overview:Follow these steps:I hope you have found the formula helpful. I am also attaching the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemyDear

Vyshnav V SIt is good to see you again. You want to upgrade the previously given VBA code to include a feature that retrieves task details from an

ALM systemor a similar work item system.To do so, connect to the ALM or work item system to fetch task details within the VBA. Later, you can develop a function to find the task details based on the task ID from the Excel sheet and generate a link to the task’s details. The implementation will depend on the system used and its access methods (like APIs).

Hopefully, the idea will help you; good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Dear

Peter SummersThanks for visiting our blog and sharing your problem. You wanted to return a time value with “hh:mm”. To do so, you have to enhance your formula with the help of the

TEXTfunction:Hopefully, you have found the idea helpful; good luck.

Regards

Lutfor Rahman ShimantoExcelDemyDear

Vyshnav V SIt is great to see you again. Thanks for reaching out and sharing another exciting problem.

To add a Request for

Delivery Receiptanda Read Receiptin theOutlook Application, we must use theReadReceiptRequestedandOriginatorDeliveryReportRequestedproperties. Do not worry! Based on your new requirements, I have enhanced the existing sub-procedure (I provided earlier).SOLUTION Overview:Excel VBA SUb-procedure:I hope you have found the solution you were looking for. The solution workbook is attached; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Denny HevalahuThanks for visiting our blog and sharing your questions.

To calculate the number of different individual items sold:

Hopefully, the formula will be helpful; good luck.

Regards

Lutfor Rahman ShimantoExcelDemyHello

MEDOThanks for letting us know that the solution works perfectly on your end. However, you want to handle cultural settings in VBA, which also works on non-UK clients.

Assuming your requirements are related to the

decimal separatorandthousand separatorsettings. Different regions worldwide use different conventions to represent decimals and thousands of separators.You can use two user-defined functions to replace these separators with

periodsandcommasto overcome the situation. I have developed two VBA user-defined functions and slightly changed the existing sub-procedures.Calculating Distance in Miles:Calculating Distance in KM:Hopefully, the VBA code will help you with your problem; good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Vito CasaThanks for visiting our blog and sharing your questions.

Sheet1contains a list of tasks with corresponding dates. You want to extract this information and plan the tasks onSheet2based on the dates. To do so, you can develop multiple formulas usingVLOOKUP, INDEX, MATCH,andXLOOKUP.You can also useIFERRORto handle errors.SOLUTION Overview:NOTE: If you are a Microsoft 365 user, you will be able to use theXLOOKUPfunction.I hope the formulas mentioned will reach your goal. I have attached the solution workbook as well; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemyDear

Isaac ChavezThanks for visiting our blog. As requested, I have developed an Excel VBA Sub-procedure that will send an

Email notificationif theDeadlineis7days ahead.SOLUTION Overview:Note: If the Microsoft Outlook dialog box appears, clickAllowto send an email.Excel VBA Sub-procedure:I hope the sub-procedure will fulfil your requirements. I have attached the solution workbook as well; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyDear

Vyshnav V SAs requested, I have improved the existing sub-procedure (previously provided) to fulfil your new requirements.

SOLUTION Overview:NOTE: If the Microsoft Outlook dialog box appears, clickAllowto send an email.Improved Excel VBA Sub-procedure:I hope the sub-procedure will reach your goal. I have attached the solution workbook as well; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyDear

Vyshnav V SThanks for clarifying your requirements further. I have modified the previously given code to fulfil your goal. Now, you can select any column containing

email IDs, and all the other required information will be retrieved from columnsCandD.SOLUTION Overview:Excel VBA Sub-procedure:Make changes to the code if necessary. I hope the code will fulfil your goal; good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyDear

IssacThanks for letting us know that the previously provided

SendEmailssub-procedure worked on your side. Now, you want to automate this task daily. To achieve this, you create aWorkbook_Openevent and call theSendEmailssub-procedure. Later, you must use theTask Schedulerto open the workbook daily at a particular time. I have improved the SendEmails sub-procedure and created a solution to your problem using the mentioned idea.Follow these steps:Hopefully, you have found the ultimate solution to your requirements. I have attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Vyshnav V SThanks for visiting our blog and sharing an exciting problem. I have developed an

Excel VBA Sub-procedurethat meets both requirements.To send email using

VBA, you can use theSendproperty. When sending an email, theMicrosoft Outlookdialog box will appear. Lastly, you must click onAllowto send email.SOLUTION Overview:NOTE: Additionally, the sub-procedure will validate your selection. If you select another column instead of theID column(assuming it iscolumn B), the message will pop up saying to choose the intended column.Excel VBA Sub-procedure:Hopefully, the sub-procedure will meet your expectations. I have attached the solution workbook as well. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Irish JimThanks for your compliments. Your appreciation and suggestions mean a lot to us. You are right; it would be great to mention right-clicking and clicking on

View Codewhen inserting code in thesheet module. Also, it is important to mention theTargetparameter when discussingevents in Excel.Thank you once again for visiting our blog and providing beneficial suggestions. Based on your suggestion, we have updated the sections. Hopefully, other visitors will have a better reading experience.

Regards

ExcelDemyHello

Isaac ChavezThanks for visiting our blog and sharing your problem. I have developed an Excel VBA Sub-procedure that automatically sends emails.

SOLUTION Overview:All you need to do is to click on Allow when the

Microsoft Outlookdialog box appears.Excel VBA Sub-procedure:When testing the sub-procedure, I accidentally emailed you by pressing Allow in the Microsoft Outlook dialog box. Please ignore that. I hope the sub-procedure will fulfil your goal; good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Jim PrattThanks for noticing the typo. We have updated the section accordingly.

Regards

ExcelDemyHello

BhartiThanks for visiting our blog and posting an exciting comment. As requested, I have developed an Excel VBA User-defined function to get

Meteorological Week no.Standard Meteorological Weeks:Follow these steps:I hope the

Excel VBA User-definedfunction will fulfil your goal. I also have attached the solution workbook; good luck.DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

LarryThanks for sharing your problem. Being remote makes it difficult to provide an ultimate solution. However, you can try

Repairing Office Installation,Checking Graphics Card Drivers, andChecking for Conflicting Add-ins.Sometimes, a corrupted Office installation can cause unexpected behavior. To fix this, you can try repairing your Office 365 installation. Outdated or incompatible graphics card drivers could also contribute to display issues. Update your graphics card drivers to the latest version from HP’s website.

If none of the above solutions work, you might need to contact

HP Supportfor further assistance. Though the exact cause (Office 365, HP Spectre, or Windows 11) remains unclear, hopefully, you can try these ideas to overcome your situation.Regards

ExcelDemyHello

Andre Van NiekerkThanks for sharing your problem. It is unusual for copying and pasting to stop working entirely in

Excel 2013if it worked perfectly inExcel 2010. Both versions rely on the same coreWindows clipboardfunctionality.The

PDF filecopied from might have a complex layout or structure that Excel 2013 has difficulty handling during the copy-and-paste process. Also, if you’re trying to paste intomerged cellsin Excel 2013, it might cause problems. Try un-merging the cells before pasting.If you are still having trouble with the issue, we recommend you use the other approaches mentioned. Let us know if they lead you to the same result; good luck.

Regards

ExcelDemyHello

Ignacio ChavezThanks for thanking me. Though, I was unable to access the link you have given, I understand your requirements. I have modified my previous VBA code in such a way that this time, it will identify the columns dynamically (Assuming column headings are in row 5).

SOLUTION Overview:Excel VBA Code:Hopefully, you have found the idea. I have attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

MarvinThanks for visiting our blog and sharing your questions so clearly. Basically, you want to have a timestamp for each row and store it in two different columns: one for the

Date Enteredand the other for theDate Modified.SOLUTION Overview:I have developed an

Excel VBA Event Procedureregarding your problem:Hopefully, you have found the idea. I have attached the solution workbook for better understanding; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

HendrikThanks for visiting our blog and posting your question. You must use the

SUMIForSUMIFSfunction to calculate the total sales.I hope these formulas will help; good luck.

Regards

Lutfor Rahman ShimantoExcelDemyHello

BonnieThanks for reaching out. Though a few steps are needed, making mistakes along the way is obvious. But no worries! The

ExcelDemy Forumis there to help. Just share your workbook and ask for advice.Regards

Lutfor Rahman ShimantoExcelDemy

Hello

Keith ShellyThanks for visiting our blog and sharing your problem with such clarity. Instead of the current formula, you can try the following formula:

`=COUNTIF('Leased Parts'!$A:$A, $B1)`

Hopefully, you have found the idea helpful. I have attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemyHello

TraceyThanks for your nice words. Your appreciation means a lot to us.

Creating a Leave Tracker in Excel requires multiple steps, so you may often get unintentional errors when following these steps. Do not worry! You can share your problem within the ExcelDemy Forum by attaching your workbook.

Regards

ExcelDemy

Hello

Rob DevineThanks a lot for your kind words. Your appreciation means a lot to us.

You wanted to display the

UserForm Calenderwhen you select a single cell from theD75:D80range and insert the date into the selected cell. Thanks once again for sharing such a practical requirement.SOLUTION Overview:To do so, follow these steps:I have attached the solution workbook for better understanding. I hope the solution will fulfil your needs; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Denise SanoThanks for visiting our blog and leaving an exciting comment. You want to rank salespeople by region based on number of units sold and then (in the case of ties) by total sales amount. However, developing such a formula using

Excel’s built-infunction would be time-consuming.So, I have developed an

Excel VBAsub-procedure to help you overcome your situation.Follow these steps:As a result, you get the intended rank like the following

GIF.I have attached the solution workbook for better understanding. Hopefully, the idea will help; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyDear

SteveIt seems you are having some issues when dragging formulas across columns. Excel may be challenging, mainly when working with complicated formulas and extensive data.

When creating a formula, use

absolute references ($)for fixed cells (such as your time series cells) andrelative referencesfor cells that should change as you drag the formula across columns. Excel offers many built-in tools for data analysis, such as theAnalysis ToolPak, which can assist with forecasting tasks. If your forecasting process is highly repetitive and complex, consider automating it with aVBA macro.I understand how you feel and your frustrations. Hopefully, these suggestions can help you overcome your problem.

Regards

Lutfor Rahman ShimantoExcelDemy

Dear

Brent HamiltonThanks for visiting our blog. You’re right; the article was described based on an older version of

MS 365, likely pre-dating March 2024.From now on, in Excel 365, the

Track Changesfunctionality has been integrated into theShow Changesbutton. It is important to note that saving the file toOneDriveis a prerequisite for using this feature.Regards

Lutfor Rahman ShimantoExcelDemy

Hello

Zakostelsky JanYou can use the

Worksheet_Changeevent inExcel VBAto make the code run automatically whenevercolumn Hchanges.Here’s how you can modify the code:

I hope the idea will help you; good luck.

Regards

ExcelDemyHello

JAN ZAKOS TELSKY,I hope you are doing well. Thank u so much for your query. Well, I can see you want to add the ID number (Column A) in the mail body when the changing row falls below the value from the corresponding cell. You also added that the changing values are in column H, and the static value is in column I.

Now, follow the below VBA code to write the ID number from column A in the email body when the value in column H falls below the value of column I.

Code:Once you apply the code, you will get an email as below.

Hope this information will help you. Please let us know if there is any further query in the comment section.

Best Regards,

Afrina NafisaExceldemyHello

RaviThanks for visiting our blog and posting your problem. The

ByRefargument type mismatch error typically occurs when the data types of variables are passed to a subroutine or function mismatch. In this case, the issue seems to be with the variabledatabasein theUserForm_Activate()event.To fix the

ByRefargument type mismatch error:Regards

ExcelDemyHello

NirmaniThanks for reaching out and sharing your problem. You passed an argument that could not be manipulated into the type expected; the

ByReferror happens. For example, when you try to supply anInteger variablewhen aLongis required.If you encounter a

ByRef errorrelated to yourdatabase variable, it could be due to how you pass the worksheet object reference. Ensure that the data types of thedatabasevariable matches the expected data type in the subroutine.When the

UserForm_Activateevent triggers, pass the correct worksheet reference. Double-check that the worksheet name you’re passing matches the actual name of the worksheet in your Excel file.Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

Greg SmallsThanks for visiting our blog and sharing your problem so clearly. After reviewing your code, I assume you have a dataset like the following image.

The code you provided is

OK; however, perhaps you have somehow messed up it when offsetting the names.I have revised your code and made some changes that work perfectly for the mentioned dataset. Follow these steps:

I hope the idea and code will help you; good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

VipinThanks for your nice words. Your appreciation means a lot to us.

You are facing an issue with a worksheet with a

Doughnut chartandatextboxin its center to display a value from another cell. The textbox is visible on the desktop version of Excel but not on the online version.You have tried moving the textbox to the front using Bring to Front, but it doesn’t work. You have also tried sending the chart to the back using Send to Back, but it doesn’t help either.

The issue might be

browser-specific. Different web browsers sometimes display online applications differently. For example, I do not have this type of issue at all.You may be using an older Excel Online version with this bug.

Microsoftfrequently updatesExcel Online, and they might have fixed the issue in newer versions. Check if there are any updates available.Regards

Lutfor Rahman ShimantoExcelDemyHello

ALEXANDER WILCOXThanks for visiting our blog and sharing your problem. When sorting categories, you get all the pictures from the unselected category in the top cell of the row.

To overcome your situation, you can insert images using the

Place in Cellfeature, like the followingGIF.I am delighted to inform you that I have developed an automated solution for you using the Excel VBA Event procedure and Sub-procedure. Please, follow these steps:

As a result, you will no longer see pictures from the unselected categories in the top row cell when filtering or sorting categories, like the following

GIF.I am attaching the solution workbook for better understanding; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

CHANDRAThanks for visiting our blog and posting an exciting comment. You want to convert decimal numbers (

up to two decimal places) to words in Rupees.To do so, follow these:I am also attaching the solution workbook for better understanding. I hope the solution will help you; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

MIkeThanks for visiting our blog and sharing an exciting query. It seems like you are attempting to expand the range of cells to which the multiple selection functionality applies.

However, the line: “

Target.Address = Range(“C4:K38”)” doesn’t quite work as expected because the “Target.Address” property returns the address of the changed cell, not the range as a whole.To check if a change occurred within a specific range of cells in

ExcelusingVBA,use theIntersectmethod. It simply checks if any cells in the changed range overlap with your desired range. This way, your code responds accurately to changes within the specified range, regardless of the number of cells involved.I have developed a solution for your query. Follow these steps:

I am also attaching the solution workbook for better understanding; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Peter ClarkeThanks for reaching out and sharing your problems and suggestions. You put a

Public Variableto display in all procedures but got an “out of scope” report. You want a thorough explanation.This issue could arise due to several reasons:

I have demonstrated

watching a Public Variablein theWatch Window. I am using three modules: one for the public variable, one for displaying the public variable, and one for watching the public variable.VBA Code in Module1:VBA Code in Module2:VBA Code in Module3:OUTPUT Overview:Hopefully, the idea will overcome your situation. Good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

Syarif,Thank you for reaching out to us and for your valuable query.

As this article focuses on creating a cash flow projection

format, you have to manually input your projected amounts for all of your cash inflows and outflows.To project cash flows for five years using an indirect method, first, you must collect your company’s balance sheets and income statements for two consecutive years. Using two-year data, you have to find cash flows manually for year 1 (

base of projection). Next, for projecting cash flows for five years,estimate a percentage of expected growth in cash flow. Multiply each cash flow with your estimated projection of increase or decrease of cash flows. Use Fill Handle to apply the formula for all the years you want to project.Hope that the following article will provide you with valuable clues on creating cash flow projections using the indirect method:

Thanks and Regards,

Abdullah Al MasudExcelDemy TeamDear

VICTORI,Thanks for your feedback. It seems that you have mistakenly assumed that we didn’t include the

5.50%growth rate in our calculations.The growth rate is used for calculating the stream of future payments. As we showed this calculation in method 1 (i.e. Using the

NPV Functionmethod), we didn’t show it again in method 2 and suggested users to see it from the previous method.However, as it has dodged your eyes, we have included the detailed calculation in method 2 as well. You also suggested that the

FV formulawe provided, is for a regular annuity, not a growing annuity. This is true, and we have updated our article according to your feedback. You can check the updated article and share your feedback with us.Thank you again for your valuable comment.

Regards,

Seemanto SahaTeam ExcelDemy

Hello

AadiThanks for visiting our blog and sharing such an interesting problem. I am delighted to inform you that I have developed an

Excel VBA User-Definedfunction to fulfil your goal.Follow these steps:As a result, you will see an output like the following

GIF.Hopefully, the User-defined function has fulfilled your goal. I am also attaching the solution workbook for better understanding; good luck.

Download Solution WorkbookRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyHello

JULIThanks for visiting our blog and sharing your query. You can modify the formula mentioned in example 1.4 and find the last cell with a number value on another sheet:

`=LOOKUP(2,1/(Week_1!C:C),ROW(Week_1!C:C))`

I hope the formula will overcome your issue; good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hi

Gunjan,Thank you for contacting us. We’ve also emailed you a

practice data entry file. Feel free to download it and start practising.Best regards,

ExcelDemy TeamHello

HardikThanks for visiting our blog and sharing your difficulties. To find the total cost of the products sold that cost more than

$1,000usingcell value as Criteria:Hopefully, the formula will overcome the situation; good luck.

Regards

Lutfor Rahman ShimantoExcelDemyHey

SMN,Sorry to hear that you were confused about Exercise 4. Let me help you by clarifying the problem.

In the dataset of Exercise 4, look at the rows of Order ID A001, A002, and A003. These orders are placed at the same

Date & Timeby the sameCustomer. Therefore, only the row of Order ID A001 is considered aNew Order. Similarly, rows of Order ID A015, A016, A017, A018, and A019 are placed on the same Date & Time by the same Customer. Therefore, only the row of Order ID A015 is considered a New Order.In Exercise, 4 your primary task is to identify these New Orders. While this can be achieved with a simple formula, an additional requirement of this Exercise is using structured references with Excel tables.

Therefore, you have to convert the data range into a table first. And then apply formulas to identify new orders.

The primary concept behind solving this Exercise is to compare the Date Time and Customer values of each row with the Date Time and Customer values of the previous row. If any match is not found, then the current row is considered a New Order.

I hope this explanation will help you understand the exercise problem and its solution. Let us know your feedback.

Regards,

Seemanto SahaTeam ExcelDemyDear

KEITH BASKETT,Thanks for sharing your problem with us. I understand that you are using

InputBoxto take the number of rows you wish to fill in a column and then repeat the process for the next columns. However, you are facing errors when you enter a text or invalid input or press theOK,Cancel, orXbutton without entering any value.To fix these errors, you have to include a few error-handling situations in your code. Here, I have provided a sample code that contains error handling for your described situations:

Here’s how this code operates:

You can preview the output in the following GIF:

I hope this example will be helpful for you to understand how to handle errors while working with an InputBox in Excel VBA. Let us know your feedback.

Regards,

Seemanto SahaTeam ExcelDemyDear

CYNTHIA,Thanks for reaching us. I understand that you want to create attendance sheets for many employees and shifts, including check-in/check-out time, break-in/break-out time, hours late in the morning, and overtime hours.

Although the current article includes an attendance sheet for only 1 employee, our site contains multiple other articles that can help you to fulfill your requirements. Here are some recommended articles for creating an attendance sheet with your given requirements:

This article shows how to include break time in an attendance sheet in Excel.

For the remaining requirement, the hours late value in the morning, you can add a column after the check-in time column and simply subtract the check-in time value from the advised check-in time (use absolute cell reference if it is specified in a single cell).

I hope you will be able to develop your required attendance sheet with the help of the above-mentioned articles. Let us know your feedback.

Regards,

Seemanto SahaTeam ExcelDemyHello

ALANThank you for reaching out. Please ensure that you’re using a

64-bit Windows system. Let us know if the issue persists.You can share your dataset by creating a Conversation with my forum account in the

ExcelDemy Forum.Best regards

Lutfor Rahman ShimantoExcelDemy

Hello!

DIANA. Thanks for sharing your queries. Depending on yourOS version, the user-defined functions may cause this error. Ensure you run64-bit Windows, and let us know if you are still facing the error.Regards

Lutfor Rahman ShimantoExcelDemy

Dear

NADEEThanks for sharing your problem with us. I understand that you want to extract the Julian date from a string and convert the Julian date to the Gregorian Date.

This can be accomplished using a simple formula. To demonstrate an example, I have taken the following dataset. Here, column A contains the employee names, and column B contains the string code with Julian dates. In column C, I have applied the following formula to extract the Julian Date:

`=MID(B2,LEN(A2)+1,7)`

Afterward, I applied the following formula to convert the Julian date to the Gregorian date:

`=DATE(LEFT(C2,4),1,0)+MOD(C2,1000)`

Thus, you can easily convert a Julian date to a Gregorian date. I hope this solution will be helpful for your requirements. Let us know your feedback.

Regards

Seemanto SahaTeam ExcelDemyHey

DAVID,Thanks for sharing your problem with us. We are glad to hear that you liked our article. We understand that you want to use the

NOWfunction inConditional Formattingto change the format of a range that contains visiting times.Here, we will provide such an example. Consider the following dataset. It contains Visiting Time and Appointment Duration for multiple clients. We have calculated the Visit End Time using the following formula:

`=C3+D3/24`

To highlight the rows where the current time falls within the Visiting Time and End Time using the

NOWfunction inConditional Formatting, follow the steps below:As a result, rows that meet the condition will be formatted.

We hope this example will help you understand how to apply the

NOWfunction in theConditional Formattingrule. Let us know your feedback.Regards

Seemanto SahaTeam ExcelDemy

Hello

SHIVAMThanks for your comment. To create a shift scheduler for a

5-member teamwith the specified shifts and off days, you can follow these steps:Here is an example of how you could

structure the scheduler:Hopefully, the idea will help you; good luck.

Regards

Lutfor Rahman ShimantoExcelDemyHello

CHARLIEThanks for your comment. The existing article’s date picker displays the date based on the date order of the Machine (Windows, Mac or Linux). However, you wanted to modify the VBA code to adjust the date format to match the desired month/day/year (American format) for both the calendar display and input into the cell. Thus, the idea will ensure consistency in date formats between the calendar view and the input into the cell.

To do so, you only need to modify the existing sub-procedure named

Create_Calenderby replacing it with the following.Excel VBA Sub-procedure:I hope you have found the idea helpful. Stay blessed.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

SHIQIANGThanks for sharing your problem. Typically, we are unable to perform such tasks in Excel. However, you can try using an

Excel VBA Sub-procedureI have developed. It will display all the color indexes of the selected cells in theImmediate Window. The idea works perfectly if background is set manually or only one conditional formatting rule is applied in a cell. You can easily modify the formula for multiple conditional formatting rules based on your needs.OUTPUT Overview:Excel VBA Code:Reach out again if you have any further queries. Hopefully, the idea will help you; good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

NADHAY SARYThanks for visiting our blog and sharing your requirements. You wanted a

Dynamic Leaderboardthat updates automatically when the Average Sales will be changed.OUTPUT Overview:I am delighted to inform you that I have developed an

Event ProcedureandSub-procedureusingVBAto fulfil your goal.Excel VBA Code:Follow the steps: Right-click on the sheet name tab >>View Code>> Paste the given code in the sheet module >>Save>> Return to the sheet and make your desired changes.Hopefully, the code will help you in reaching your goal.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

KARANThanks for reaching out and posting such an interesting comment. Yes, you can manually specify holidays using the

NETWORKDAYS.INTLfunction to customize which days are considered weekends and also lets you include specific holidays.In your case, the first and second arguments in the formula are for the start and end dates; you can use

113rd arguments to consider only Sunday as the weekend. In the 4th argument, you insert the specified holiday.I have developed a formula for January by focusing on your specified weekends and holidays.

Hopefully, the formula will help; good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

MATTEOThanks for visiting our blog and sharing your problem. Excel’s internal precision for numerical calculations can sometimes lead to unexpected results, especially when dealing with tiny or huge numbers.

To overcome your situation, you can combine the

ROUNDandCONVERTfunctions.“mm” to “ft”:“mm” to “in”:Hopefully, the formula will help you overcome your situation; good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hi

ANDREW G,Thank you very much for reading our articles.

You wanted to know a way to run the macro periodically without opening any file that will auto-refresh all the Excel files in a certain folder.

Here, I will discuss a way to fulfill your requirements. But to run a macro you need to open an Excel file and insert a macro in it. After that, the macro will refresh all your files in the mentioned folder. We will use the

Task Schedulerfeature of Windows to run the macro periodically.Best Regards,

Alok PaulTeam ExcelDemyDear

CARLOS,Thank you for sharing your problem with us. To print 7 graphs as a single image:

Following the steps, we will get an image like the picture below.

As a result, you will get all the graphs printed as a single image.

Regards

Wasim AkramTeam ExcelDemyHi

ManishaIt is working fine for me and returning 10:16. Please give us more details about the formula and/or the dataset you are using.

Regards

NiloyExcelDemyHello,

Thank you for reaching out and sharing your experience. It seems like there might be an issue with the

X-axisformatting.To address the

x-axisdisplaying as1900,try the following steps:Format X-Axis:After combining the scatter plots, right-click on the x-axis and selectFormat Axis. Check the settings to ensure the date format is correctly applied to your month values.Date Formatting:Make sure that the Month values on the x-axis are recognized as dates. You may need to format the cells containing the monthly data as dates if they are not already.Data Source:Double-check the data source for your scatter plot. Confirm that the Month values are correctly assigned to the x-axis.X-Axis Labels:Ensure that the x-axis labels correspond to the Month values. Adjust the labels if necessary.If you face the issues again, please provide more details about your data and the steps you followed, and I will do my best to help you.

Please let us know if your problem is solved or not.

Regards,

Bishawajit ChakrabortyExcelDemyHi

ED,Thank you for your query. Unfortunately, directly importing your Yahoo Finance watchlist into Excel for stock details retrieval is not feasible due to technical limitations.

Reason:Yahoo Finance watchlists are dynamic web pages primarily rendered by JavaScript, while Excel’s

Data>From Webfeature captures static HTML snapshots. This fundamental difference prevents Excel from capturing the interactive and user-specific nature of watchlists.As an alternative solution, I recommend downloading the template from this

link. Add your company names from the watchlist to this template. This template facilitates tracking and decision-making for your listed stocks within Excel.We trust that this solution aligns with your interests and needs.

Best regards,

Ishrak KhanExcelDemyHi

JAMES MARTIN,Thank you for your positive feedback and for taking the time to recreate the example from the article. I’m glad you found the

non-VBA REGEXexample useful for your learning. I rechecked the process, and it worked fine for me. Did you recreate the example on our provided Excel file or try it on another workbook? For our provided workbook, if you want to recreate another named range, then you might need another name or a space, as you mentioned. However, it’s always helpful to receive feedback. If you have any further questions or suggestions, feel free to let us know. Your input is appreciated!Regards

Rafiul HasanExcelDemyHi DINESH,

Thanks for your comment. You can use the VBA code given below for the desired output.

I hope this user-defined function will solve your problem. Please let us know in the comment section if you have any other queries.

Regards

Maruf HasanExcelDemyHi

ISRAVEL,To achieve this in Excel, you can use the

COUNTIFfunction to count the occurrences of each number and then check which numbers occur more than twice. Here’s how you can set up your Excel sheet:Assuming your numbers are in column A starting from

A2, you can use the following formula in another cell to get the count of numbers that appear more than twice:`=COUNTIF(A:A, A2)`

Drag down this formula for each number in your list. This will give you the count of each number.

Then, you can use another column to check if the count is greater than 2:

`=IF(B2>2, "More than two", "Not more than two")`

Drag this formula down for each number in your list.

This setup will give you a clear indication of which numbers occur more than twice in your list.

I hope this comment will help you get your required output. Please let us know in the comment section if you have any other queries.

Regards

Maruf HasanExcelDemyHello

OLIVER,I hope you are doing well. Thank u for your query. Well, you can use the below formula to extract only the minimum value in the same cell.

`=MIN(IFERROR(VALUE(MID($B$4:$B$8, FIND(":", $B$4:$B$8) + 1, LEN($B$4:$B$8))), ""))`

Note:Change the range(B4:B8)according to your dataset.Hope this information will help you. Please let us know if there is any further query in the comment section.

Best Regards,

Afrina NafisaExceldemyHi

Kim, thanks for reaching out. Here’s a solution to your query to run the VBA code from the command prompt.Here, I have used the VBA code shown in Method 4 of this article. The name of this macro is “

Sheet3.SortPivotTableByValues” according to this article. Use it in your workbook and modify it according to your references.Now, create a Notepad/Text document file and copy the following code in it. Change the file location and macro name if needed. Keep in mind that this macro was written in a sheet. So the sheet name should be added before the name of the Sub procedure.

Now, save the file as a

.vbsextension file. In this case, I named itRunMacro.vbs.After that, open the Command Prompt or

cmd.exe. PressWindows + Rbuttons and typecmdin theRundialog box and clickOK.The Command Prompt will appear. Copy and paste the following line and press Enter:

`cd "C:\Users\DELL\Desktop”`

After that, copy and paste the following code in the next line and press Enter.

cscript RunMacro.vbs

Finally, your .xlsm file will open with the Pivot Table sorted. Hope this helps.

Regards

Meraz Al NahianExcelDemyHi

Chua!It brings me great joy to know that you found our advice helpful. You are most welcome. As for troubleshooting your problem, it would be best if you could provide us with the Excel file. However, you could use the following steps to try to solve your problem on your own.

If your problem is not yet solved, please join our

ExcelDemy Forumand post this problem with your Excel file attached to it.Regards,

NafisExcelDemyDear

MARCOS,Thank you for your query regarding the convertible bond pricing model presented in this article. Your query is valid, convertible bonds are bonds with the option to be converted into common stock. The conversion price refers to the price per share at which a convertible security (such as corporate bonds) can be converted into common stock.

The formula for conversion pricing is:

`Conversion Price = Market Price of Convertible Bond / Conversion Ratio`

To find the conversion price, you need the “Conversion Ratio”, which is the number of shares that investors receive upon conversion. You can divide the

Bond Value (C17)by theConversion Ratio (C18)to find theConversion Price (C19)as illustrated below.I hope this clarification is helpful. If you have any further questions, please feel free to ask.

Kind Regards,

Sumaiya MirzaExcelDemyDear

DORABABUThank you for your comment and your insightful observation. You are correct in pointing out that making prepayments towards the principal amount of a home loan can indeed result in a reduction in the total number of EMIs. This is an important consideration and can have a significant impact on the overall loan repayment timeline.

To incorporate this feature into the home loan

EMIcalculator, you can make the following adjustments to the existing formula:Update Total Number of Months(N):Instead of fixing the total number of months as

G4×12, you can dynamically adjust it based on the prepayments made. You can introduce a new variable, sayM, representing the total number of months after considering prepayments. The formula forMwould be:`M=G4×12−Number of Prepayment Months`

Modify the PMT Function for EMI Calculation:Update the formula for EMI calculation (in cell

C7) to reflect the adjusted total number of months (M):`=ABS(PMT(E4/12,M,F7))`

By making these changes, the calculator will dynamically adjust the total number of EMIs based on the prepayments made, providing you with a more accurate representation of the impact of principal payments on the loan tenure.

I hope this addresses your query. If you have any further questions or if there’s anything else you’d like to discuss, please feel free to ask.

Best Regards

Sumaiya MirzaExcelDemyDear

Khristine,Thank you for your comment. I have made some modifications to the code. Now, when you run it, a file picker dialog box will appear, allowing you to select a file. Once you’ve chosen the file, the

Outlookapp will open and a new email will be generated with the file attached. You can then manually send the email at your convenience. Here is the updated code:I hope it will do the job for you.

Regards

Aniruddah AlamExcelDemy

Hello

Miriam,Thank you for sharing your problem with us. We are sorry you’re experiencing issues with the

Excel VBAscript for getting cell color.Here are a few steps you can take to address the problem:

Please let us know if your problem is solved or not.

Regards,

Bishawajit ChakrabortyExcelDemyHello

ROSEMARIE OLIVERA,Thanks for your response. Yes, you can apply the

CELLfunction to the filtered table.Regards

MD Naimul HasanHello

HANS ENGELS,Thanks for your query. To extract data from the website to Excel automatically when the website requires login credentials, you must apply web scrapping techniques and automation tools.

First, you need to understand the web structure of that website and then use web scraping tools to get access to that website and extract data based on your needs.

Regards

MD Naimul HasanExcelDemy

Hi

Sandhya,You can add 45 days with the invoice date and use it as a replacement for TODAY() in any of the formulas from above. Or, you can use a simple IF formula.

Here is an example, the invoice date is in cell C13. Use the formula:

`=IF(C5>$C$13+45,"Overdue","Not Overdue")`

Make sure to use the correct reference style if you intend to drag the fill handle.

Regards

Abrar-ur-Rahman NiloyExcelDemy

Dear

NORTH80,Thank you very much for reading our articles.

You have mentioned that, when pulling historical dividend information you faced a problem. The problem is:

“Expression.Error: The column ‘Dividends’ of the table wasn’t found.

Details:

Dividends”

Here is the modified query code to solve the mentioned problem.

If you face further problems, please write in the comment box.

Best Regards,

Alok PaulTeam ExcelDemy

Hello

VARUNThanks for reaching out and sharing your requirements. Your appreciation means a lot to us.

Yes, We can provide information on the Indian stock market with charts in dollars (

INR) instead of dollars ($).You can download this template below for your help. Additionally, I’m also writing down the steps so that you can modify it yourself.

Download Excel file: Track-Indian-Stock-Market.xlsxUse these steps for indian stock market tracking:

Repeat the same process for other columns where necessary till

JorBeta Column.To update the format of Our Stock information section (

Column K to P), follow these steps:It will apply the same currency format to the applied range. Use the same process for

Column OandP.To update the format with Conditional formatting of

Current Investment Column, copy the formatting ofChanges (INR)column usingFormat Painterand paste it. It will automatically update both currency format and conditional formatting.Note:The charts will update automatically.Hope this helps you out.

Regards,

Ishrak KhanExcelDemy

Hi

Zafar Iqbal,In response to your request for some data entry work files, we have sent a PDF file for you to practice. Kindly check your email. You can also download the practice file provided with the article. Stay connected with ExcelDemy.

Regards

Rafiul HasanExcelDemy

Hi

Laurie!It feels good to know that you found our content understandable. Judging from you query, I think you want a continuing compound interest formula where the loan doesn’t have a specific term/year.

The term continuous compound interest is used to emphasize the continuous compounding of interest, as opposed to discrete compounding periods. Here, the compounding frequency becomes infinite, resulting in a continuous growth formula. Let us look at the formula below:

A=P_{0}e^{rt }Where,

For example, if you invest $1,000 at an annual interest rate of 5% continuously compounded for 2 years, the future value can be calculated using the continuous compounding formula.

Regards,

NafisExcelDemy

Hello

LAKSHMI,I hope you are doing well. Thank you for your query. You can combine sheets row-wise using the VBA code provided in the first method of this article. (

Merge Data Sets from Multiple Sheets into One Sheet with VBA Row-wise). Once you go through this method of merging data sets, you will know where to change the code as the details are written in the “Note”.Best Regards,

Afrina NafisaExceldemy

Hello

Lea, thanks for reaching out. Here’s a solution for your query.Procedure:Regards

Meraz Al NahianExcelDemy

Hello

ODMAThanks for reaching out and sharing your queries. The previous question by

ABIGAYLE PAULSONwas to automate the idea that the filtering algorithm will be applied if the drop-down list inSheet8is changed. After analyzing your requirements, I understand that you do not want to use the filter algorithm if the drop-down is empty.I have developed an

Excel VBA Worksheet_Change Eventthat will fulfil your goal. All you need is to paste the following code in the sheet module of Sheet8 (the sheet that contains the drop-down).Excel VBA Event Procedure:OUTPUT Overview:Hopefully, the event procedure will help; good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

POOJAThanks for reaching out and sharing your requirements with such clarity. The problem you want a solution for can quickly be developed using several

Excel VBA Sub-procedures,Event Procedures, and aUserForm.Here is an algorithm you can follow:

OUTPUT Overview:Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

ANDREWYes, that’s correct. In both methods described in the article, you would need to open the

file containing the macro to auto-refresh the target Excel files. However, once the macro is executed, it will open and refresh the specified Excel files without manually opening them individually.Regards

Lutfor Rahman ShimantoExcelDemy

Hello

GRAHAMThanks for reading our blogs and sharing your requirements. You wanted to display the range-converted images horizontally in the email body. This can be achieved by slightly modifying the article’s Excel VBA code.

OUTPUT OVERVIEW:Excel VBA Code:I have another present for you. If you ever want to attach the images generated from the selected ranges, you can use the following code:

Hopefully, the codes will help in various situations. I have also attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

RAMIThanks for visiting our blog and sharing your queries. You want to create multiple stopwatches in a sheet. Of course, the requirement is very much achievable.

I have displayed four stopwatches in the

B4,F4,B15, andF15cells.OUTPUT OVERVIEW:Follow these steps:I hope you have learned how to create multiple stopwatches in a sheet. I am also attaching the solution workbook for better understanding; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

SINTA RThanks for reaching out and sharing your queries. The formula you have mentioned is almost correct. However, you miss to insert the

<>sign somehow in your formula.You can apply the following formulas:

For

Important & Urgent:`=IFERROR(INDEX($C$4:$C$30,SMALL(IF((LEN($C$4:$C$30)<>0)*($D$4:$D$30="Yes")*($E$4:$E$30="Yes"),ROW($C$4:$C$30)-ROW($C$3),""),ROW(C4)-ROW($C$3))),"")`

For

Important & Not Urgent:`=IFERROR(INDEX($C$4:$C$30,SMALL(IF((LEN($C$4:$C$30)<>0)*($D$4:$D$30="Yes")*($E$4:$E$30="No"),ROW($C$4:$C$30)-ROW($C$3),""),ROW(C4)-ROW($C$3))),"")`

For

Not Important & Urgent:`=IFERROR(INDEX($C$4:$C$30,SMALL(IF((LEN($C$4:$C$30)<>0)*($D$4:$D$30="No")*($E$4:$E$30="Yes"),ROW($C$4:$C$30)-ROW($C$3),""),ROW(C4)-ROW($C$3))),"")`

For

Not Important & Not Urgent:`=IFERROR(INDEX($C$4:$C$30,SMALL(IF((LEN($C$4:$C$30)<>0)*($D$4:$D$30="No")*($E$4:$E$30="No"),ROW($C$4:$C$30)-ROW($C$3),""),ROW(C4)-ROW($C$3))),"")`

OUTPUT OVERVIEW:Hopefully, you have found your solution. I have also attached the solution workbook for better understanding; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoyExcelDem

Hello

DORYThanks a Ton! for your nice words. Your appreciation means a lot to us.

You would like to copy only a specific range from the source workbook. I am presenting an

Excel VBA Sub-procedurethat will fulfil your requirements.OUTPUT OVERVIEW:Excel VBA Sub-procedure:Hopefully, you will like the example and the sub-procedure. Good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

JAMESThanks for reaching out and sharing your queries. To return the text value of two cells in different columns if they match, you can combine the

EXACT,IFandISTEXTfunctions.Excel Formula:`=IF(AND(ISTEXT(A2), ISTEXT(B2), EXACT(A2, B2)), A2:B2, "No Match")`

So, in simpler terms, the formula checks if both cells A2 and B2 contain text and if the text in both cells is the same. If they are, it returns the values from cells A2 and B2 together. If not, it returns “No Match”.

OUTPUT:Hopefully, the idea will help you; good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

BRYANThanks for visiting our blog. All the methods described in the article are working perfectly on our end. It is important to remember the intended effect you want to see when you have printed the sheet.

OUTPUT OVERVIEW:So, I recommend you reread the article; good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

GARY SHERMANThanks for reaching out and sharing your problem. You want to find the total number of sheets for each material type. To do so, you can use

the SUMIF functionto reach your goal.Follow these steps:As a result, you will see an output like the following image.

Hopefully, the idea will help; Good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Dear

KellyThank you so much for your kind words! It’s fantastic to hear that our tutorial has been helpful to you. You’ve already made quite an impact in your new role, becoming the

Excel expertin your office – that’s quite an accomplishment!Let us know if you ever need more help with Excel or anything else. We’re here for you. Best wishes for your continued success at the

mom & popshop!Best Regards

Lutfor Rahman ShimantoExcelDemy Team

Hello

CHRISTOFThanks for sharing your queries. Depending on your

OS Version, the User-defined functions may give this type of error. Ensure you run a64-bit Windows, and let us know if you still face the error.Regards

Lutfor Rahman ShimantoExcelDemy

Hi

KarthickThank you for reaching out with your inquiry! Designing a shift schedule for a

4-member team rosterwith diverse shift patterns can be challenging.Each shift,

A,B,C, andD, has a schedule for weekdays, weekends, and special arrangements on Wednesdays. Setting these parameters upfront allows you to assign team members to their shifts more efficiently.On Wednesdays, all four members are active but in different roles. It would be best if you had a flexible framework to handle this. One idea is to create a

dynamic formulathat adjusts shift assignments based on the day of the week and specific requirements. This might involve using conditional logic to distribute team members across day and night shifts, ensuring we have enough coverage while using resources effectively.Don’t forget to explore the resources in the article, including the practice workbook, to gain hands-on experience with building and refining your shift scheduler. Good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

ERALGood to see you again. You wanted shifts like, Instead of using D1, D2, D3, D4, OFF, A1, A2, A3, OFF, is it possible to use just like this only D, D, D, D, OFF, A, A, A, OFF. Technically, it is impossible without VBA, but I have found a trick to reach your goal. I have used

extra spacesin theSettings_Shift_Legendto make the shifts unique.OUTPUT OVERVIEWHopefully, I will like the idea. I have attached the solution workbook to help you understand better; good luck.

Download Solution WorkbookRegards

Lutfor Rahman ShimantoExcelDemy

Hello

FRED FUTCHThanks for sharing your problem. You might be experiencing some formatting issues in Excel when inputting numbers.

Ensure that the cells where you are typing numbers are formatted as

GeneralorNumber. Sometimes, Excel may automatically apply a different format if it detects a specific pattern in the data.Sometimes, Excel’s behaviour can be influenced by the

regional settingson your computer. Make sure that your computer’s regional settings are configured correctly.Hopefully, the idea will help in your situation; good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

FRANKY LAMThanks for visiting our blog and sharing your problem. Based on your problem, it seems the VBA User-defined function named

Code128is not recalculating on your system.So, you can try forced fully recalculating using the

CalculateFullproperty of theExcel Application.Follow these steps: Right-click on the sheet name tab >> Click onView Code>> Paste the following code in the sheet module andSave:Hopefully, the idea will help you good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

SamirThanks for visiting our blog and posting an interesting comment. You want to convert a number (e.g., 10245) into a string where each digit of the number is represented by its word form (e.g., “One Zero Two Four Five”).

I am delighted to inform you that I have developed an

Excel VBA User-definedfunction namedNumberToStringto fulfil your goal. I am using the function in aSub-procedure. However, you can also use it inExcel cellslike other worksheet functions.OUTPUT OVERVIEW:Excel VBA Code:Hopefully, the user-defined function will help you reach your goal; good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

ASNATHThanks for reaching out and posting your comment. You want to know how to use the

COUNT,IFandAVERAGEfunctions in a formula. I am presenting an example where I will form a formula using these functions.OUTPUT OVERVIEW:Suppose your dataset has Employee ID, Name, Department, Age, and Salary columns. You want to find out the average salary based on department. You can get the result using only

the AVERAGEIF function, but if the department does not exist, it will provide a#DIV/0!Error. So, to handle this type of situation, you can combineCOUNT,IF,SUMandAVERAGEfunctions:Hopefully, the idea will help you. Good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

JAN ZÁKOSTELSKÝThanks for reaching out and sharing your problem. You want to compare two Excel rows: one that remains constant and another that changes. You need to receive an email whenever a number in any cell of the changing row becomes lower than the value in the corresponding cell of the constant row.

I am delighted to inform you that I have developed an

Excel VBA Sub-procedureandan Event Procedure. These will fulfil your requirements.Follow these steps:Hopefully, the idea will help you; good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

VICTORIA SHARPThank you for reaching out and sharing your requirements. Based on your comment, it seems like you’re looking for a dynamic link between your

Excel sheetandWord documentto ensure that any updates in the Excel sheet reflect immediately in the Word document.The solution provided in the article serves this purpose quite effectively. Using

the Mail Merge featurein Word, you can connect your Excel sheet (containing theConflict of Interest Registerdata) and your Word document.So, do not hesitate to follow the steps outlined in the article to establish the connection between the two. Once set up, any changes you make to the Excel sheet will automatically propagate to the Word document when you update the merge fields. This way, you can maintain an accurate and current Conflict of Interest Register without manual effort. Good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

ANUPAMThanks for reaching out and sharing your queries. You not only want to count total links but also to display cells containing links.

I am delighted to inform you that I have an

Excel VBA sub-procedurethat will fulfil your goal. The code will display the total links in aMsgBoxand show all the cells containing links in theImmediate window.OUTPUT OVERVIEW:Excel VBA Sub-procedure:Hopefully, the code will help you; good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

VIKASThanks for reaching out and sharing your queries. You wanted to count the total number of named ranges in a workbook.

I am delighted to inform you that I have developed an

Excel VBA sub-procedureto display thetotal number of named rangesin anExcel MsgBox.OUTPUT OVERVIEW:Excel VBA Sub-procedure:Hopefully, the code will help you. Good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

EXCELFLASHThanks a ton for taking the time to dive into our article and sharing your thoughts! We’re thrilled to hear that you found it excellent overall – that means a lot to us.

We’re sorry you encountered an issue with the formula from

method 1. After investigating, we discovered that you are correct about theline spacingandquotation marksissues in that formula when copying.We’ve updated the article, so feel free to retry it. Hopefully, this time around, you won’t encounter the same hassle with the formula. Let us know if you have any further questions or suggestions.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

BHARATH LThanks for your nice words. Your appreciation means a lot to us. You wanted to mention a series of numbers for every copied row. In your words, if you have copied 50 columns using the code, you need to mention

1 to 50 numbersin each row.I am delighted to inform you that I have developed an

Excel VBA sub-procedureto fulfil your requirements. I have modified the existing article code and made it an advanced version.OUTPUT OVERVIEW:Advanced Excel VBA Code:Hopefully, the code will fulfil your goal. Good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

TIMThanks for reaching out and sharing your query. You want an

Excel VBAcode that works perfectly for multiple columns and rows of checkboxes. I am presenting an enhanced VBA sub-procedure that may help you.OUTPUT OVERVIEW:Enhanced Excel VBA Code:Hopefully, the sub-procedure will reduce your hassle. Good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

KHALEDThanks for your nice words. Your appreciation means a lot to us. You only want to get the city name from lat and long values.

I am delighted to inform you that I have developed another VBA User-defined function named

ExtractCityNamesthat will extract the city names by taking input from the User-defined function mentioned in this article.Follow these steps:Things to Remember: The return time can be longer than usual as we use complex Excel VBA User-defined functions.I am attaching the solution workbook for better understanding. Hopefully, the idea will fulfil your goal. Stay Blessed.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello Abdul

Thanks for reaching out and posting your comment. You want to highlight the scenario where a student, Raju, fails in one subject out of five but still manages to achieve a high percentage overall due to scoring well in the remaining subjects.

Basically, you want to ensure that Raju isn’t labelled a failure just because he fails one subject. In your view, Raju’s overall performance across all subjects should involve when determining his pass/fail status, not just the grade in one subject.

Hopefully, you have found the idea helpful. Good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

NIKKIThanks for reaching out and sharing your queries. You want to automatically fill a cell background to yellow anytime a change is made to the data inside the cell.

I am delighted to inform you that I have developed an

Excel VBAchange event that will fulfil your goal. To demonstrate, assume you want to auto-fill a cell incolumn Ato yellow.To do so, open the sheet module => Insert the following code and

Save.Now, return to the sheet and change some cell values in column A to get an output like the following

GIF.Hopefully, the idea will help you. Good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

SCOTTThanks a lot for your kind words. You want to specify a particular column from a table in Excel VBA ComboBox’s RowSource property.

Output Overview:Let’s assume you have a table named Data in the sheet Database with several columns, and you want to populate your ComboBox with the values from a specific column, let’s say the Project column.

You can use the following format for the RowSource property: =Database!Data[Project]

Hopefully, the idea will help. I have also attached the solution workbook for better understanding. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

ARONNThanks for reaching out and sharing your requirements. You want to link a checkbox directly to another one with no cell reference.

I am delighted to inform you that I have developed an Excel Event Procedure that will fulfil your goal.

Output Overview:If you check the first box, a linked check box (second check box) will also be checked or vice versa.

Follow these steps:Hopefully, the idea will help you. I have attached the solution workbook for better understanding. Good luck.

SOLUTOIN WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

RENEThanks for reaching out and sharing your problem. You have observed that the formula works for the first eight rows. When you expand to include cells B5:B144 and C5:C144, it returns

#N/A. However, our machine works perfectly fine, even though we work with an extensive range. TheFILTERfunction is only available in Excel forMicrosoft 365andExcel 2021. Assuming you are using one of these versions.To resolve your problem, double-check the references in the formula to ensure they correspond to the correct columns and cells. Confirm that the formula is entered correctly without any typos or syntax errors. Good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

JITHUThanks for reaching out. You are right about the file not allowing the opening of macros. The password is recovered using the

Zip Tool, which will not let you open macros. In the article, the writer has demonstrated the file as a macro-free file when using the Zip tool. That’s why when changing back to the file type Zip to Excel, the.xlsxis used.If you are working on a macro-enabled file when recovering a password using Zip Tool, change the file’s extension (when changing the file type Zip to Excel) to

.xlsminstead of .xlsx.Regards

Lutfor Rahman ShimantoExcelDemy

Hello

DENNIS IVAN FORDFor the

Time_Boxto work properly, paste the following code in the UserForm.Through this code, the UserForm will be able to add time with a date when the

Add Timecheck box is ticked.Download the practice workbook provided in the article for better understanding. Good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

DENNIS IVAN FORDThanks for your comment. For the

Month_Boxto work properly, paste the following code in theUserForm.Additionally, you can remove the Option Explicit statement at the beginning when declaring initial variables.

The article has been updated. So, I recommend you to go to the article again. Good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

SHASHAThanks for reaching out and posting your question. I would say

yes, you have found the solution to your problem. You can fix this problem by changing the date format in your computer’s date and time settings. Change the date format to your desired one.However, I am delighted that I have an ultimate solution. I am presenting an Excel VBA sub-procedure that will display the date format based on the PC date & time setting.

OUTPUT Overview:All you need to do is replace the existing Create_Calender sub-procedure with the following enhanced sub-procedure.

Hopefully, the idea will help you good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

KaranThanks for sharing your problem. The practice workbook attached to the article should be usable in

Windows 11 OS.As the Excel file is

macro-enabled, check your Excel security settings: Go toExcel Options>Trust Center>Trust Center Settings>Macro Settings, and enable macros if they are disabled.Besides, ensure that the

Code 128font is correctly installed on your system.Hopefully, these actions will resolve your issue. Good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

Jon JacobsonThanks for your nice words. The code and font work perfectly fine in our machine for numbers

greater than 999. See the following Image,You

can test the barcodesshown in the image using your barcode gun. On our end, we have found the original text accurately.It is impossible to thoroughly observe your problem remotely and not glance at your machine. Make sure you have installed the

Code 128barcode font properly. You can check the scanner manual for specific settings related to Code 128 barcodes.If you still face this type of problem, you can share the issue within the

ExcelDemy Forumwith details of your machine. Stay blessed.Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

THThanks for reaching out and sharing your requirements. You wanted a roaster including D1, D2, DN, N1, N2, and O to rotate full month and continue next month.

I am delighted to inform you that I have created a roaster to fulfil your goal. So, follow these:

Return to the sheet, and use the file like the following

GIF.Hopefully, the idea will work for you. I am attaching the solution workbook for better understanding. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemy

Hello

KIMHONGThanks for sharing your query. In your case, an employee works 11 hours and starts working at 5:30 AM. If the overtime is between

0 and 15 minutes, you wanted a formula that returns0 minutes. If it’s15 minutes or more, it’s considered as30 minutes.You can achieve the goal by developing a formula using the

IFandTIMEfunctions. Follow these steps:Hopefully, the idea will help. Good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

NCThanks for sharing your requirements. You wanted a setup not to overwrite the existing entry but to create a new entry timestamp when an item is checked in or out more than once.

I am delighted to inform you that I have developed an

Excel VBA Sub-procedureto fulfil your requirements. So, follow these steps:After inserting the bar code and running the sub-procedure, you will see an output like the following GIF.

Hopefully, the idea will help you to reach your goal. I have attached the solution workbook. Good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

KAJA POHARThanks for your compliments. Your appreciation means a lot to us. You wanted to work with a

read-only Excel file; however, you had issues with the existing VBA code.I am delighted to inform you that I have developed an

Excel VBA Sub-procedureby modifying the existing VBA code. The main idea behind the sub-procedure is it changes theFile Accessproperty toxlReadWrite, and before closing, it changes the File Access back toxlReadOnly.Follow these steps:As a result, you will see an output like the following GIF.

Hopefully, the idea will help you to reach your goal. Good luck!

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

L. O.Thanks for reaching out and sharing your problem. You are facing difficulties with including a heading in a chart, and those headings affect the graph’s appearance.

As the headings are treated as zeros, you can exclude them from the data by combining the

IF,ISNUMBERandNAfunctions. The formula structure can be like the following:This formula checks if the value in cell

A2is a number. If it is, it includes the value. Otherwise, it returns#N/A. The#N/Avalues will be ignored in the chart, and the line won’t connect to them.Instead of a scatter plot, you might consider using a line chart or other chart types that suit your needs better. Line charts, for example, automatically ignore

#N/Avalues and don’t connect them.Hopefully, the ideas will help you to overcome your situation. Good luck!

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

NUNOFThanks for your beautiful words. Your appreciation means a lot to us. We are delighted to know you have learned a lot from our blog.

The issue you describe hampers performance for sure in such scenarios. I have gone through the article and also investigated your problem.

What I have found: Perhaps you are calling your desired macro within another worksheet event likeWorksheet_Calculateinstead of theWorksheet_Changeevent of cell$K$10, which results in calling the macro unintentionally.When the cell values change by formulas, the Worksheet_Change event will not trigger. So, call your macro or sub-procedure within a change event. In your case, the code within the sheet module can be like the following:

Hopefully, you have found the idea to overcome your situation. Good luck!

Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

RAYThanks for reaching out and sharing your query.

Unfortunately, the

Paste Special featureis unavailable inExcel Onlinecompared to the desktop version.There is a list of missing features that you will also not find in

Excel Web:Hopefully, the answer and list will help you regarding your queries. Good luck!

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

RAMNATH TAKIARThanks a Ton! for your nice words. Your appreciation means a lot to us. Thank you once again for sharing your expertise with our

ExcelDemyblog!The extra values you have added prove effective. We appreciate you sharing your program with us, and we believe it will be valuable to others who visit our blog.

To generate even

500 random samplesfrom theselected population of 200:OUTPUT OVERVIEW:Excel VBA Sub-procedure(Contributed byRAMNATH TAKIAR):Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

ALEXThanks for your nice words. Your appreciation means a lot to us.

You are facing a difficulty like when you press the enter button on

cell D5with the formula, thebarcodechanges. It’s different from the original one and can’t be scanned. Providing the ultimate solution without properly glancing at your problem is very hard. However, I am giving you some ideas that might be helpful.Suggestions: Your issue concerns the font encoding or handling of the Code 128 font in Excel. Make sure that the Code 128 font is installed correctly on your system. Check thescanner manualfor any specific settings related toCode 128 barcodes. Format the cell asTextwhen using the Code 128 barcode font with acustom VBA functionto ensure accurate content and avoid unintended changes byExcelformatting rules.You can also share your problem with more details in the

ExcelDemy Forum. Good luck.Regards

Lutfor Rahman ShimantoExcelDemy

Hello

GSFRAGAROThanks for sharing your problem. You have encountered an issue like you have a string with

two consecutive zeros, but the code generates an empty character. In Our machine, the code and font work perfectly fine if we have a string with two consecutive zeros. See the following Image,The possible reason for the issue you encountered could be not using the

Code 128 font. Maybe you are using some other barcode font. So, please ensure you are using the intended font. Good luck.Regards

Lutfor Rahman ShimantoExcelDemy

Hello

NURITThanks for sharing another exciting problem. The issue is raised because the

UDF(User Defined Function) is not re-calculating.I am excited to let you know that I have resolved the issue by developing an

Excel VBA Sub-procedurenamedForcedReCalculation(responsible for application re-calculation).OUTPUT OVERVIEW:Follow these steps: Open theVBA Editorwindow => Right-click on thesheet module=> Click onView Code=> Replace the previous code with the following code =>Save.Hopefully, you have found your solution. I look forward to seeing you at the

ExcelDemy Forumwith another exciting problem. Stay blessed.Regards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

JOHNThanks for sharing your query. You wanted to split strings within Excel cells that do not always have 3 words separated by commas. In contrast, there can be a string like one or at most three words like the following image. Besides, we must keep the cell empty if more than 3 words mean more than 2 commas exist.

I am delighted to inform you that I have developed two formulas. The first formula consists of the

IF,LEN,SUBSTITUTE,TRIM, andMIDfunctions. The other formula consists of theIF,LEN,SUBSTITUTE,TRANSPOSE, andFILTERXMLfunctions.OUTPUT OVERVIEW:1. Using IF, LEN, SUBSTITUTE, TRIM, and MID functionsFollow these steps:Step 1: Select cellC5, insert the given formula and drag theFill Handleicon toE5.Step 2: Select rangeC5:E5and drag theFill Handleicon to cellE10.2. Using FILTERXML, TRANSPOSE, IF, LEN, and SUBSTITUTE functionsI am attaching the solution workbook for better understanding. I hope these ideas will help you to reach your goal. Good luck.

DOWNLOAD WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemy

Dear

NURITIt is good to see you again. Thanks for thanking me. Your appreciation means a lot to us.

You are right. When a cell background color is changed by conditional formatting, the cell color index remains

xlColorIndexNone. When using conditional formatting to change the cell background based on a condition, it fetches the intended color, but the cell background is not changed ultimately.SOLUTION to the Mentioned Problem: Remove the conditional formatting from theS8:AO43range. Use anExcel VBA Eventprocedure that I developed to solve your problem. This event will ultimately change the background color based on the condition.Follow these steps: Right-click on the sheet name tab => Click onView Code=> Paste the code below in the sheet module, andSave=> Return to the sheet and make changes to see the output like the followingGIF.Enhanced User-defined Function: I am delighted to share an Enhanced version of the previous sub-procedure.Follow these: Hover overInsert=> Click onModule=> Paste the following code in the module =>Save.Finally, to calculate the sum, Select cell

Q8=> Insert the following formula => Drag theFill Handleicon to cellQ14.Besides, I am presenting an sub-procedure that will display the color index of a selected cell.

A Friendly Suggestion: Dear Nurit, we are discussing a topic different from the article. So, another visitor may get confused. It would be great if you shared your problem through theExcelDemy Forum.I am also attaching the solution workbook for better understanding. Hopefully, this idea will help you reach your goal. Good luck.

DOWNLOAD WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemy

Hello

ZAPPERThanks for reading ExcelDemy Blogs. You wanted a button to click and copy some sheets in a folder.

I am delighted to inform you that I have enhanced the sub-procedure so you will like it. The sub-procedure will create a folder dynamically, and the path will be the workbook path. Besides, it will handle errors as well. I have inserted a print icon and assigned the sub-procedure as follows.

OUTPUT OVERVIEW:Enhanced Excel VBA Sub-procedure:I am attaching the solution workbook for better understanding. Good luck.

DOWNLOAD WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemy

Hello

MR MARTYN WILLIETSThanks for reaching out and sharing your requirements. You wanted to create a personal payment tracker sheet. To create such a sheet, we can work with fields like

Date,Start Time,End Time,Total Paid Hours,Overtime Hours,Overtime Pay,Normal Hours Pay, andTotal Pay. Assuming you get$76.5for overtime hours and$51for regular hours. I am delighted to share how you can create a sheet to fulfil your goal.Overview of Personal Payment Tracker Sheet:Follow these steps:Step 1: Total Paid HoursSelect cell

E3=> Insert the following formula => Drag theFill Handleicon to cellE10.Step 2: Overtime HoursSelect cell

F3=> Insert the formula below => Drag theFill Handleicon to cellF10.Step 3: Overtime PayChoose cell

G3=> Insert the formula below => Drag theFill Handleicon to cellG10.Step 4: Normal Hours PayChoose cell

H3=> Insert the formula below => Drag theFill Handleicon to cellH10.Step 5: Total PayChoose cell

I3=> Insert the formula below => Drag theFill Handleicon to cellI10.I am also attaching the solution workbook for better understanding. Hopefully, the idea will help you to reach your goal. Good luck.

DOWNLOAD WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemy

Hello

ASIF MAHMOODThanks for reaching out and sharing your requirements with such clarity. You wanted to know how to convert a string like

159.3 Binto a regular unit format (for example,159300000000.00).I am delighted to inform you that I have developed an

Excel VBA Event Procedurethat will trigger when a value is inserted in a format like13 Bincolumn Band convert the values into a regular unit. Besides, I have developed an Excel formula to fulfil your goal using theTEXTandSUBSTITUTEfunctions.OUTPUT OVERVIEW(Excel VBA Event Procedure):Follow these steps:Step 1: Right-click on the sheet name tab => Click onView Code.Step 2: Hover overInsert=> Click onModule=> Paste the following code in the module =>Save.OUTPUT OVERVIEW(Using Excel TEXT and SUBSTITUTE Functions):Follow these steps:Step 1: Select cellC5=> Insert the following formula => HitEnter.Step 2: Drag theFill Handleicon to cellC14to copy the formula down.I am also attaching the solution workbook for better understanding. Good luck.

DOWNLOAD WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemy

Hello

GARY PHILLIPSThanks for reaching out and sharing your queries. You want to know how to use the

Monthly Logpresented in the article. Copy the drop-down of symbols and paste it in all the day cells. Choose the intended symbols using drop-down for all the day sections of the month. If a task is complete, mark it asCompletein the left section above the calendar.You can easily use the Monthly Log like the following

GIF.Hopefully, the idea will help you. Good luck!

Regards

Lutfor Rahman ShimantoHello

DAN HThanks for your nice words. Your appreciation means a lot to us. Thanks once again for sharing your requirements with such clarity.

You wanted to get an Excel VBA Sub-procedures or Excel VBA User-defined functions to calculate the distance between two addresses using

Address String(for example,New York,Alaska) instead of Latitudes and Longitudes values.I am delighted that I developed some

Excel VBA User-definedfunctions to fulfil your requirements.OUTPUT OVERVIEW:Follow these steps:Step 1: Hover overInsert=> Click onModule=> paste the following code in the module.Step 2: Select cellC9=> Insert the following formula.Step 3: HitEnterto see the result in cell C9.Step 4: Select cellC10=> Insert the following formula.Step 5: HitEnterto see the result in cellC10.Things to Keep in Mind: Ensure to checkMicrosoft XML, v3.0from theReferences – VBAProjectwindow.I am attaching the solution workbook. Hopefully, these user-defined functions will help you reach your goal. Good luck.

DOWNLOAD WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello

BRIANYour appreciation means a lot us. Thanks for reaching out and posting a well-layout question. You encounter an issue with hidden columns and buttons that toggle between different sheet views. And you want to delete these buttons and the hidden column features. It isn’t easy to give an exact solution without the exact details of the worksheet views. However, I have some general ideas to help you with your problem.

First, identify the

toggle buttonsand go to sheet views, followed by view options. Later, delete that sheet view. Additionally, you remove the appliedfilters and groupings. Select the columns surrounding the hidden ones, right-click, and chooseUnhideto ensure all columns are visible.If you still have difficulties, you can share your problem in the

ExcelDemy Forumwith more detailed information. Good luck!Regards

Lutfor Rahman ShimantoExcelDemy

Hello

NURITThanks for your nice words. Your appreciation means a lot to us. You wanted a VBA code that will ignore the text within a cell and sum only the number portion on that cell of the S8:AO43 range based on specific conditionally formatted colors.

I am delighted to inform you that I have developed an

Excel VBA User-definedfunction to fulfil your goal.OUTPUT OVERVIEW:Excel VBA User-defined Function:Follow these steps:Step 1: Open theVBA Editorwindow => Hover overInsertand click onModule=> Insert the code above in the module andSave.Step 2: Select cellP20=> Insert the following formula => HitEnter.Hopefully, the idea will help you. Good luck.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

BERNARDThanks for reaching out and posting your observation. The

ZIP Toolmethod mentioned in the article can be applicable If you have aprotected sheet within a workbook.I have experienced the same error when the workbook is encrypted with a Password. But, if only a sheet is protected, the ZIP Tool works perfectly. When you preserve

your workbook with an Encrypted Password, the ZIP Tool method will not work. So, use the VBA code to remove the password from the encrypted workbook. Good luck.Regards

Lutfor Rahman ShimantoExcelDemy

Dear

AVAYour appreciation means a lot to us. Thanks for describing your problem with such clarity. You want a column of comments and a column of texts in which a comment was made (

e.g. Josef Albers | Artist).I am delighted to let you know that I modified the previous code to fulfil your requirements.

Excel VBA Sub-procedure:OUTPUT OVERVIEW:I hope the code presented will make your task easier. Good luck!

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

AVAThanks for reaching out and posting an exciting comment. You wanted to extract comments besides the links within the comment from

Word to Excel.I am delighted to inform you that the requirement you describe can be met with the help of

Excel VBA. I am presenting a sub-procedure that will fulfil your goal.Excel VBA Sub-procedure:OUTPUT OVERVIEW:To learn more you can read the following articles.

You can download the

Solution WorkbookandWord Documentfor better understanding.Download Excel WorkbookDownload Word DocumentI hope the given code and resources will help. Stay blessed.

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

GURUThanks for sharing your queries. Your

Excel Datais like“01/01/23 14.20 PM”(24-Hour Clock Time does not need AM/PM extensions). You want to store “01/01/23” and “14.20 PM” in another column.I am presenting another sub-procedure that will fulfil your requirements by modifying the sub-procedure mentioned in the article.

Excel VBA Sub-procedure:OUTPUT OVERVIEW:You have one more doubt when using

Text to Columnfeatures. Typically, the Text to Column features will display dates in the desired format you mention. You can apply a custom format like“m/d/yyyy”after that if it does not.Hopefully, the suggestions and the presented code will help you. Good luck!

Regards

Lutfor Rahman ShimantoHello

KURTThank you for reading our article and providing helpful input. We sincerely appreciate your time and effort in reviewing the content and pointing out the calculation error.

We have already taken corrective action and have updated the article accordingly. Thank you one more. Best wishes!

Regards

Lutfor Rahman ShimantoExcelDemy

Hello

LUIS CARRERAThanks for sharing your problem. The error you are facing is like the following:

On the line below:The error arises from setting the

range offsetoutside theworksheet’s boundaries. The rangeRGstarts at cellH5, and when you try to set theoffset to (1, 0), it might go beyond the last row of the worksheet.However, I am presenting an

Excel VBAcode to handle that error.Excel VBA Code:Hopefully, the code will work perfectly for you. Good luck.

Regards

Lutfor Rahman ShimantoHello

MATS WIKMANThanks for your nice word. Your appreciation means a lot to us. I am sharing my findings regarding your issues as follows:

Hopefully, the suggestion will help you in reaching your goal. If you still have doubts, you can share your issues with precise details within the

ExcelDemy Forum. Good luck!Regards

Lutfor Rahman ShimantoExcelDemyHello

YVONNEThanks for reaching out and sharing your query. You wanted a formula to find duplicates when columns can be suffered, like

Laura & China and China & Laura.I have developed two formulas that will fulfil your requirements using the

IFandCOUNTIFfunctions. I will use column C as a Helper column, displaying the result in column D.Follow these steps:Step 1: Select cellD5=> Insert the given formula => Drag theFill Handleicon to cellD10.Step 2: Select cellD5=> Insert the given formula => Drag theFill Handleicon to cellD10.Hopefully, you have got the solution. Good luck.

Regards

Lutfor Rahman ShimantoHello

GFIN SUNNYThanks for your nice words. Your appreciation means a lot to us. Thanks once again for sharing your problem.

Unfortunately, using VBA to automate web interactions with sites that require authentication is not easy when it requires login. You may need a more advanced approach to handle authentication, such as sending

HTTP requestswith the necessary credentials.Regards

Lutfor Rahman ShimantoHello

CYNTHIAThanks for visiting our blogs and sharing your queries. You want to know how to initialize the weight in the method 3. When investigating your problem, we found that the dataset we previously used does not suit the weighted moving average formula. So, we have modified the article (method 3) for better understanding.

When implementing a weighted moving average formula, it is better to fix the total weight first and distribute the weights among durations. So, please go to method 3 of this article again. Hopefully, you will not have any doubts. Good luck!

Regards

Lutfor Rahman ShimantoExcelDemyHello

BENThanks for reaching out and sharing your problem. The requirements you mentioned can be achieved by following some steps.

Follow these steps:Step 1: Create a Drop-down for SubsStep 2: Find Employees Based on Subs in an Intermediate ColumnSelect cell

F2=> Insert the following formula => HitEnter.Step 3: Create a Drop-down for EmployeesStep 4: Display Title and Rate Based on EmployeeSelect cell

C23=> Insert the following formula => HitEnter.Step 5: Hide the Intermediate Column and Insert an Event ProcedureHide column

F=> Right-click on the sheet name tab => Click onView Code=> Paste the following code in the sheet module =>Save.OUTPUT: Return to the sheet and make desired changes to see the result like the followingGIF.You can download the solution workbook for better understanding.

Download Solution WorkbookHopefully, the idea will help you in reaching your goal. Good luck!

Regards

Lutfor Rahman ShimantoHello

GregThanks for your nice words. Your appreciation means a lot to us. You wanted to know the formula for adding a Double time column for anything

above 12 hoursdaily.I am delighted to inform you that I have developed an Excel Formula using the

IFandTIMEfunctions to fulfil your requirements.Follow these steps:Step 1: Select cellG11=> Insert the following formula.Step 2: HitEnterto see the result, like the image below.Step 3: Hover over the cursor on the right button corner of cellG11to see theFill Handleicon.Step 4: Drag theFill Handleicon to cellG18to copy down the formula.Hopefully, this idea will help you reach your goal. Good luck!

Regards

Lutfor Rahman ShimantoHello

HUONGPTThanks for sharing your problem. You want to get data from

Excel 365(WEB) to alocal Excel application. Fencing data fromExcel 365(WEB) is different from importing data fromGoogle Sheets. I am presenting an Excel VBA sub-procedure. However, you must have validWindows Securitycredentials on your own.Excel VBA Sub-procedure:After Running the code, the

Windows Securitydialog box will appear => Next, insert the intended username and password => HitOK.Hopefully, the idea will help you. Good luck!

Regards

Lutfor Rahman ShimantoHello

ALAN FARTHINGThanks for reaching out and sharing your problem. I found a

Syntax Errorwithin your given code. You have to write the.sortproperty within theWithblock.Corrected Sub-procedure:OUTPUT:Hopefully, the correction resolves your problem. Stay blessed.

Regards

Lutfor Rahman ShimantoHello

ROBERT MCPHEEThanks for reaching out and posting your query. You wanted to create a nested formula using

the IF function. You were almost there. The formula you have given contains somesyntax errors.Corrected Formula:If you want to use the formula from another sheet, for example, from

any sheetand use theR5,F5,L5, andM5cells ofSheet1, use the following formula.Hopefully, this idea will help you reach your goal. Good luck!

Regards

Lutfor Rahman ShimantoHello

XUThanks for thanking me. Your appreciation means a lot to us. You wanted to convert

UTM(UTM Easting,UTM NorthingandUTM Zone) toLatitudeandLongitudevalues.I am presenting some Excel VBA User-Defined functions, and these functions will achieve your goal.

Follow these Steps:Step 1: Open theVBA Editorwindow => Paste the following code in a module =>Save.Step 2: Return to the sheet => Choose cellE4=> Insert the following formula => And drag theFill Handleicon to cellE11.Step 3: Choose cellF4=> Insert the following formula => And drag theFill Handleicon to cellF11.Things to Keep in Mind:As we cannot use all decimal points for some values (

UTM Northing), we may not get the same value (Latitude) like previous one.Hopefully, the idea will help you. Good luck.

Regards

Lutfor Rahman ShimantoHello

XUThanks for reaching out. All the User-Defined functions mentioned in this article calculate

UTM Easting,UTM NorthingandUTM Zoneaccurately.I am comparing the result with an

Online UTM Calculatorwebsite (LatLong.net) like the image below for demonstration.If you still have doubts, you can share your dataset in our

ExcelDemy Forum. Good luck!Regards

Lutfor Rahman ShimantoHello

SUBBARAMANThanks for reaching out and sharing your requirements. Your appreciation means a lot to us. Thanks once again.

You want to analyze the

Indian Stock Markets. To achieve your goal, you only need to usethe Indian company names, and the rest of the procedure is described in the article.Like the following

GIF, I generated the intended data for some notedIndian Companies.So, type the Indian company names and follow the steps of this article. Good luck!

Regards

Lutfor Rahman ShimantoHello

RON SMITHThanks for reaching out and sharing your problem. I want to thank

MAHFUZA ANIKA ERAfor writing such a helpful article. In particular, she developed the calendar within a UserForm.However, you are having trouble when clicking the time box. After checking that box, you expect to get the time after the date, but the time is not displaying. I went through this article and discovered why you are having trouble displaying the time and date with a cell.

SOLUTION: You must check the time box before choosing dates to display the date and time.If you want to choose a date and later display time, I am delighted to inform you that I have developed a

Change Event Procedurefor the check box namedTime_Boxfor that. When the check box is checked, the time will be next to the date; otherwise, the time will not be displayed.Steps: Paste the following code with the module ofUserForm1=>Save.OUTPUT:Hopefully, the Idea will help you. Good luck.

Regards

Lutfor Rahman ShimantoHello

ADAM INGLETONThanks for reaching out and posting an exciting query. You are right about the

run-time error “13” Type mismatch. This is because the active sheet containstext valuewithin cellB4.I am delighted to inform you that I have developed other

improved sub-proceduresthat have overcome the issue you mentioned by modifying the previous code. To be specific, I have taken aworksheet objectto avoid this error.Raised Error on Your End:Improved Sub-procedures:OUTPUT of Applying the Improved Sub-procedures:Hopefully, the idea will resolve your problem. Good luck!

Regards

Lutfor Rahman ShimantoHello

KRISHThanks for reaching out and posting your question. The mentioned schedule can be maintained in several combinations. However, I am presenting a suitable one that will fulfil your requirements.

SHIFT:Day Shift (D): 8 AM to 4 PMAfternoon Shift (A): 4 PM to 12 AMNight Shift (B): 12 AM to 8 AMMEMBERS:Staff Rotation Schedule (7-Day Cycle):Day 1 (Staff D’s day off):Day 2 (Staff B’s day off):Day 3 (Staff C’s day off):Day 4 (Staff A’s day off):Day 5 (Staff A’s day off):Day 6 (Staff B’s day off):Day 7 (Staff C’s day off):Hopefully, the idea will help you. Good luck!

Regards

Lutfor Rahman ShimantoHello

AJKThanks for sharing this exciting issue with us. The mentioned site arranges the required information on many pages. When I went through, there were

69 pages(25records are displayed on each page). However, the page numbers will change over time. You wanted to use thePowerQueryand fetch all the information into asingle sheet.I am delighted to inform you that I have developed an

Excel VBA Sub-procedureto fulfil your requirements. Thanks toMD. ABDUR RAHIM RASEL. I developed such an Excel VBA Sub-procedure by considering the idea he had given in the previous comment.Excel VBA Sub-procedure:OUTPUT:Things to Remember:Limitations: The code will take10 to 15 minutesto fetch data from that site. It will depend on the capacity of your computer and internet connection.I am attaching the solution workbook for better understanding. Good luck!

Download Solution WorkbookRegards

Lutfor Rahman ShimantoHello

REZAThanks for reaching out and posting your comment. You are right about the raised error. If you do not add the

Microsoft PowerPoint 16.0 Object Library, you must see theUser-Defined Type not definederror.To solve the issue, follow these:

Step 1: Hover overTools=> You will see theReferencesoption.Step 2: Click onReferences=> In theReferences – VBAProjectwindow, checkMicrosoft PowerPoint 16.0 Object Library, and you will see theOKbutton in the top right corner.Step 3: After clickingOK, you will be able to run the code like the followingGIF.Hopefully, the solution will help you overcome your situation. Good luck.

Regards

Lutfor Rahman ShimantoHello

MIGHTYThanks for reaching out and posting your comment. You are right about the plan described in

Method 2, but partially. Here, we could not maintain the proposal date forTask D. As mentioned in the article, we had to delay. The predecessor ofTask DwasTask C, which had been completed on the10th of May. In that sense, we can say the plan made is impossible to follow. But, we must keep in mind that if we cannot start the project at the proposed time, it does not mean we are not completing it at the proposed end date. Maybe the project will end at the proposed date.In project management, it’s common to have tasks that depend on completing other tasks. It’s a fundamental aspect of project planning to identify and manage task dependencies and ensure the project is executed in a logical and efficient order. When you have such dependencies, you need to adjust the schedule accordingly to ensure that tasks are performed in the correct sequence.

Regards

Lutfor Rahman ShimantoHello

DUSTINThanks for reaching out and posting your query. You want to add a smoothing option while interpolating. You can achieve the goal with Excel formulas. However, I am presenting an

Excel VBA User-definedfunction where you can add a smoothing option.Assume you have two user-defined functions for applying the Cubic Spline and Cubic Hermite Spline interpolations. You want to create another user-defined function that will have the same parameters as before, including another extra parameter for choosing the smoothing option. Let’s say if p is provided, it will apply the Cubic Spline interpolation. And if q is provided, it will apply the Hermite Spline interpolation. If the Choose Parameter is not provided, it will raise a warning.

Excel VBA Code:OUTPUT:1. Cubic SplineHere, we are providing

pas the Choose Parameter.2. Hermite SplineWe are providing

qas the Choose Parameter.3. Error HandlingNow, we are not providing any of the Choose Parameters. As a result, a Warning Window appears.

Press

OK=> You will get an output like the following image.Hopefully, the idea will help you. Good luck!

Regards

Lutfor Rahman ShimantoHello

HEATHERThanks for reaching out and posting your comment. You are right about the results from using

Geography DatatypesandExcel VBA user-definedfunctions being slightly different. However, avalid API endpointin VBA code will give you accurate results like the Geography datatypes.If you are a

Microsoft 365user, I recommend using the first method, where you can use Geography datatype. I have presented theExcel VBAto other Excel users. However, I must admit that using the Geography is more accurate than the Excel VBA user-defined functions.Regards

Lutfor Rahman ShimantoHello

BICThanks for reaching out and posting an interesting problem. You want to create a

2nd instanceof the existing stopwatch code.Excel VBA Code(2nd Instance):2nd Instance of stopwatch code

OUTPUT:Hopefully, the idea will fulfil your requirements. Good luck!

Regards

Lutfor Rahman ShimantoHello

NIRMALThanks for reaching out and posting your comment. You asked if it is possible to insert images in a particular size in Excel using the same code or not. The answer is

Yes. As mentioned in the article, you can easily set the.heightand.widthproperties for customization.I am going to present a modified version of the given code mentioned in the third method to handle some errors.

Excel VBA Event Procedure:Hopefully, the idea will fulfil your requirements. Good luck!

Regards

Lutfor Rahman ShimantoHello

MeniThanks for reaching out and sharing your expertise. I have investigated the formula you have shared and found it very powerful. We can easily separate the texts and numbers with this single formula.

Your suggestion proved effective, and We are genuinely grateful for it. Thank you again!

Regards

Lutfor Rahman ShimantoExcelDemy TeamHello

MX A HUSKINSThank you for reaching out and posting your comment. You are right about setting up

your own valid Bing APIto work properly.The

API Keywill become an invalid one If3 monthsor10K transactionsare reached. The API mentioned in this article may have reached 10 K transactions.Regards

Lutfor Rahman ShimantoHello

NATS COThanks for reaching out and posting your comment. You have given us wonderful advice, which is very appreciated. You want to modify the existing formula you have given in such a way that it will look up only a cell with value and ignore all blank cells from bottom to top.

I am delighted to inform you that I have developed such a formula by modifying your given formula. I have also developed a User-defined function using Excel VBA.

Modified Excel Formula:Excel VBA User-defined Function:Open the

VBA Editor=> Paste the following code in a module =>Save.Return to the sheet => Use the user-defined function like other worksheet functions.

I hope the idea will fulfil your requirements. Good luck!

Regards

Lutfor Rahman ShimantoHello

AISHWARYA ROYThanks for reaching out and posting your query with such clarity. You want to add new rows with certain information using a cell value. Some rows contain text that can be a mixture of three texts: peanut butter and jelly sandwich. You want to add the new rows accordingly and insert values like 1. Peanut butter, 2. Jelly and 3. Bread.

I am delighted to inform you that I have developed an

Excel VBAprocedure that will fulfil your requirements.Navigate to

Developer>> click onVisual Basic.Due to this, the VBA Editor window will appear.

Finally, you will see an output like the following one.

I am also attaching the solution workbook to help you understand better. Good luck!

WORKBOOKRegards

Lutfor Rahman ShimantoHello

Nadeem KhatriThanks for reaching out and posting your comment. You are right about the

Percentage Completioncolumn showing100%. Thank you once again for noticing the interesting issue. You are also correct about the formula used in the article having no error.Reason: The Percentage Completion column shows 100% for each task. It is important to note that we are usingTODAYas a reference day. All the tasks are completed100%for the dataset shown in the article according to today’s dates.Note:Things to Remember:When inserting date values within the start date and end date columns, ensure all the dates are in the same format.

Solution:To demonstrate, I am using the dataset mentioned in this article. However, I will use some other date values. Here, you will see an improved version of the formula mentioned in the article to avoid negative values.

Select cell

E5>> apply the following formula >> dragFill Handleto cellE8.I hope the idea and concept are crystal clear to you now. Stay Blessed.

Regards

Lutfor Rahman ShimantoHello

DENNISThanks for reaching out and noticing the problem. You are right about the Date values not being formatted as expected for 1st to 12th.

I am delighted to inform you that I have come up with a solution. You must choose a date format consistently while inserting date values in cells.

In this case, use the

exact Date Picker mentioned in the article, which typically chooses the data format asmm-dd-yyyy. Next, you have to create an extra command button that will be responsible for inserting the chosen date value in cellB10. And theExcel VBA Eventprocedure will convert the date format intodd-mm-yyyy.VBA Code for CommandButton1: Double-click on the insertedCommandButton1>> insert the following code andSave. You can also change the destination cell by modifying the code.Excel VBA Event Procedure: Insert the following code in the intended sheet module described in my previous reply.OUTPUT:Hopefully, the idea will resolve your issue. Stay Blessed.

Regards

Lutfor Rahman Shimanto

Hello

DENNISThank you for staying with ExcelDemy and posting your queries. You previously asked to change the date format to

dd-mm-yyyy. MUSIHA MAHFUZA MUKTA provided a solution regarding your date format problem. The solution is working perfectly at my end as well.After reviewing your requirements, I assume you want to use the date picker to insert date values in a sheet.

I am delighted to inform you that I have developed an

Excel VBA Eventprocedure. All you need to do is keep the event procedure in the sheet module. The process will format any date values into the dd-mm-yyyy format whether you insert date valuesmanuallyordate picker.Right-click on the sheet name tab >> click on

View Code.Due to this, the

VBA Editorwindow will appear.Paste the following code in the sheet module >> click the

Saveicon.Choose any cell >> Input date values with any valid date format.

After pressing

Enter, we see the date value converted into thedd-mm-yyyyformat.Hopefully, The idea will fulfil your requirements. Good luck!

Regards

Lutfor Rahman ShimantoHello

JAMIEYour appreciation means a lot to us. Thanks for your nice words!

You often click the stop button after you have already stopped the timer. That results in an Error. Thank you once again for noticing the issue. Your claim is correct.

However, I am delighted to inform you that the issue can be avoided with the help of an

Error HandlercalledOn Error Resume Next.All you need to do is to modify the

StopTimesub-procedure to fulfil your requirement.Modified Excel VBA Code:Good luck!

Regards

Lutfor Rahman ShimantoHello

HateThank you for reaching out and posting your comment. The issue you are mentioning is correct. Thanks once again for noticing the problem.

I am delighted to inform you that our team successfully developed a procedure to make a Pie Chart with subcategories. According to the idea, we have modified the article. I recommend you go through the article again and enjoy it.

Regards

Lutfor Rahman ShimantoTeam ExcelDemyDear

FaisalThanks a ton. Your appreciations mean a lot to us. I am glad you have found the article very helpful.

You mentioned the user-defined functions only work for

UTM ZoneandUTM Eastingon your end. The user-defined function for calculatingUTM Northingis not working.However, the issue you are addressing is unclear to us. On our end, it is working perfectly. I am using

Microsoft 365. However, It should not make any difference. The code should work for all versions of Excel.Please share your workbook containing a dataset with us. You can also post your query and attach the dataset in

ExcelDemy Forum. We need more information about your problem, such as if it returns any error or wrong values.The functions often may return values using scientific notation like

2.34344E+13. To avoid this type of notation, you may use an Event procedure.Excel VBA Event Procedure:I am looking forward to receiving your reply. Stay blessed and Good luck.

Regards

Lutfor Rahman ShimantoDear

AmirSorry for the late reply. Thanks a lot for bringing the issue to us. I have developed some user-defined functions using Excel VBA. These functions take

Lat and Longvalues and returnUTM Easting,NorthingandZone. I also have modified the article accordingly. So, I recommend you to go through the article again. You will not be disappointed, I promise.Regards

Lutfor Rahman ShimantoHello

MOSESThanks for reaching out and posting your comment. You are absolutely right. However, I am delighted to inform you that I have modified the article. After going through the article, you must be able to convert

Lat LongtoUTM Easting,NorthingandZone. I have developed some user-defined functions using Excel VBA to achieve the goal.Regards

Lutfor Rahman ShimantoHello

Dr TimberI went through this article and did not find any technical issues. In my view, you should reconsider what you have said.

This article is about finding an

X intercept. To demonstrate this point, this context considers a practical dataset where the percentage of people still using masks is calculated when the number ofCovid CasesbecomesZero. I think you expected the calculation to findX InterceptforY = 0. That’s what is calculated in those methods. When the writer introduced us to the dataset, it was explicitly mentioned.Anyway, I am introducing another method that may overcome your confusion. I have calculated the percentage of people who still use masks when the number of Covid Cases becomes Zero. I am using

FORECAST.LINEAR Functionto predict the percentage. And I get the same result shown in this article.Excel Formula:The data considered in this article is not Linear. That’s why you get confused. However, these methods must be able to find an X intercept for any Linear data.

Stay safe. And good luck!

Regards

Lutfor Rahman ShimantoHello

ANAND,Thanks for reaching out and posting your interesting issue. The requirement you mentioned can be done with the help of an

Excel VBAcode. The code contains two sub-procedures namedImportVCFRecordWiseandExtractData. All you have to do is to run the ImportVCFRecordWise procedure to achieve your goal.Excel VBA Code:Solution Workbook: Download the Workbook used to solve the issue.DOWNLOAD WORKBOOKVCF File: The VCF file contains some raw data. I am using the data described in this article to be more specific.Steps:Things to Keep in Mind:This concept will assist you in reaching your goal. Good luck!

Regards

Lutfor Rahman ShimantoHello

ASADThanks for reaching out and posting your query. You mentioned using

IBM Notes Version 10as your email client. You must modify the code to work with IBM Notes instead ofOutlookto achieve your goal. I am introducing you to anExcel VBAcode that can send emails with the help of IBM Notes.Excel VBA Code:Things to Keep in Mind:This idea helps you reach your goal. Don’t hesitate to contact us again if you have any more questions.

Regards

Lutfor Rahman ShimantoHello

DeAnnaThanks for reaching out. To overcome the situation, I will introduce another method. Here you must convert the locations into

Geographydata types. Later, you will find theLat Longvalues for each location. Lastly, you have to call the belowUser-definedfunction in a cell.Excel VBA Code:API:STEPS:Select range

B3:B4>> go toDatatab >> click onGeography.Choose cell

C3>> apply the below formula >> drag theFill Handleto C4.Press

Alt+F11>> go toInsert>> click onModule>> insert the mentioned code.Choose cell

C9>> apply the below formula.This concept will assist you in reaching your goal. I’ve also attached the

Solution Workbookto help you understand it better. Best wishes.Download WorkbookRegards

Lutfor Rahman ShimantoHi

D KELLY,Thanks for reaching out! I’d be happy to help you with your code and provide a solution. To solve this issue, we built a procedure called

GetLatLongand a user-defined function namedGetTotalDistance3LocationsusingVBA. You can input your desired locations in the GetLatLong procedure. The model calculates the total distance for three locations. Please click the link underneath this section to get a copy of the illustration workbook.Download WorkbookBest regards,

Lutfor Rahman Shimanto(

ExcelDemy Team)Hello

PCLOUDGreetings from our website! Thank you for sharing your question on the platform. There is a way to resolve your submitted query of finding the maximum value of the

Closeprice in a defined period instead of the whole table. I can assist you with anExcel PowerQuerycode to reach your goal.PowerQuery Code:The line

MaxClosePrice = List.Max(#”Changed Type”[Close])calculates the maximum value of theClosecolumn in the transformed table. The result is assigned to the variableMaxClosePrice.I hope this will achieve your goal. I am also giving you the

Workbookused to investigate your issue to help you understand better. Feel free to contact us again with any other inquiries or concerns.WORKBOOK:DOWNLOAD WORKBOOK

Regards

Lutfor Rahman ShimantoExcelDemy TeamHello

SAMIRThanks for reaching out and posting your query. Regarding your question, I can assist you with a

User-definedfunction programmed inExcel VBAthat will take three arguments. Among these arguments, the first and second will be theLatitudeandLongitudeof the start and end location. And the third argument must be anAPI Key. I am giving you anAPIfor demonstration which should work. However, you may use yourAPIas well as the third argument. I am attaching theWorkbookused to investigate the described issue.Excel VBA Code:OUTPUT:WORKBOOK:SOLUTION WORKBOOK

The

User-definedfunction andAPIwill meet your requirement. Don’t hesitate to contact us again with any other inquiries or concerns.Regards

Lutfor Rahman ShimantoHello

DREW7STER,Greetings from our website! Thank you for posting your question on the platform. There is a way to solve your submitted problem of finding the unique values and their frequency. I am providing an

Excel VBAcode compatible with several unique values, as many as you want to be more specific.Excel VBA Code:I hope this will achieve your goal. Feel free to contact us again with any other inquiries or concerns.

Regards

Lutfor Rahman ShimantoHello

CAR,It is great to see you again. I hope this reply finds you well. As you requested, I have reviewed your code and found some

Syntaxerrors. But overall, the algorithm was close to achieving your goal.However, I am introducing a more efficient way of doing the same task with a better algorithm that should be compatible with large datasets.

EXCEL VBA CODE:I hope this will achieve your goal. I am also giving you the Solution workbook to help you understand better.

DOWNLOAD WORKBOOKRegards

Lutfor Rahman ShimantoThank you once again for your trust in us,

CAR. Please do not hesitate to reach out if you need further assistance.Regards

Team ExcelDemyHello

DOUGLAS FORMAN,Thanks for reaching out and sharing your exciting query. When a given condition is met, we can use data bars and conditional formatting to change the color of a cell at a time. I can help you with

Excel VBAcode that loops through theKcolumn and applies your given formula. Latterly, it utilizes anIf Conditionalto format each cell. When iterating, if the cell contains1, that means100%“Yes” It formats the fill color of the cell as Green. On the other hand, it uses aData Barsfeature. To get a better understanding of the issue, I am going to share theWorkbookused in exploring your problem.Excel VBA Code:INPUT:OUTPUT:WORKBOOK: Data Bars And Conditional FormattingFeel free to contact us again with any other inquiries or concerns.

Regards

Lutfor Rahman ShimantoExcelDemyDear

ABDUL KADARGreetings from our website! Thank you for posting your question on the platform. As requested, I can assist you with an

Excelformula using theIFandCOUNTIFSfunctions that determine how many products aRegionmakes even if regardless of the product name (sometimes the product column is blank against a Region).FORMULA:Regards

Lutfor Rahman ShimantoHello

CARThank you for reaching out to us on our website. You can use a single module to move rows between sheets based on various values. You may do this by using an

IFstatement inside aFORloop that iterates through the rows inSheet1, verifies the value, and then moves the entire row to the proper destination sheet based on the value. The intended code is given below.Regards

Lutfor Rahman ShimantoHello

Ruby,Greetings from our website. You have encountered a warning message from the Microsoft Excel window. The error message “

You’ve entered too few arguments for this function” means not providing enough arguments or inputs for a particular formula or function to work correctly. To resolve this error, check the formula or function you’re using and ensure you’ve provided all the required arguments.This article explains how we can highlight cells with more than three duplicates. But, if you need help understanding this post, you can follow another article on the

ExcelDemywebsite link given below.How to Do Conditional Formatting in Excel [Ultimate Guide]

Later go through this article to better understand and highlight if there are more than three duplicates. Good luck.

Regards

Lutfor Rahman ShimantoHello

PRACHI DABHADEGreetings from our website! Thank you for posting your question on the platform. I can assist you with an Excel VBA code that creates new workbooks and allows you to specify names for those files. Furthermore, you can choose a location to store those files. Note that the user can cancel the folder selection dialogue. If they do, the code will end without making any new workbooks.

Download Workbook:I am attaching the Workbook used to explore your issue. You can have it by clicking the link below.

https://www.exceldemy.com/wp-content/uploads/2023/03/PRACHI-DABHADE.xlsm

INPUT:In the sheet named Sheet1, there is a list of names.

OUTPUT:the desired workbooks are created utilizing the Excel VBA code mentioned below.

VBA CODE:NOTE:1)I am using the Application.FileDialog method, this code asks the user to choose a folder where the new workbooks will be saved. Then, it puts the path to the selected folder in the folderPath variable.2)Using the folderPath variable and the Workbook’s name from the wbNames array, the code saves each new Workbook with its name in the chosen folder.3)Lastly, remember to modify the VBA code when you want to work with the other ranges. Don’t hesitate to contact us if you face any other issues.Regards

Lutfor Rahman Shimanto(ExcelDemy Team)Hello

Patrick,Thank you for reaching out with your issue. You encountered a Run-time error. The “Run-time error ‘9’: Subscript out of range” error occurs because you did not modify the VBA code and forgot to insert the intended sheet name. You are misled somehow by the first step of the third method. The post says to go to the Insert tab and choose Headers and Footer from the Text group, and then execute the code mentioned in the third method; that’s all.

I can provide three Excel VBA codes to avoid the error you experienced unintentionally. All you need to do is choose the intended sheet as the active sheet and run a sub-procedure from the below list.

First Method:Insert Page Number in Footer Using Excel VBASecond Method:VBA to Insert Total Page Number in FooterThird Method:Insert Page Number in Selected CellRegards

Lutfor Rahman Shimanto(ExcelDemy Team)Hello DYLAN,

Thank you for reaching out to us on our website. I understand that you have a value assigned to each member and are trying to split them into equal groups while ensuring each group’s total weight is within 1. Based on your requirements, I can assist you with this by applying an Excel VBA code.

The above VBA code splits a list of items into groups based on their assigned value and calculates the total weighted value for each unique group. It loops through each row of the worksheet, sets each item to a group based on the total weighted value of the group, and creates a new group if the current group's total weighted value plus the contemporary item's assigned value is greater than 1. The GetUniqueValues function calculates the unique groups and their total weighted values by using the Unique and SUMIFS functions to generate an array of unique values in column C and calculate the total weighted value for each group.

1) Inserting Required Values:

2) Displaying Final Output:

3) Download Practice Workbook

https://www.exceldemy.com/wp-content/uploads/2023/03/Dylans-Request.xlsm

Thank you again for reaching out to us, and I look forward to hearing from you soon.

Best regards,

Lutfor Rahman Shimanto

Hello KEVIN

Greetings from our website! Thank you for posting your question on the platform. I have yet to experience the issue you described personally. However, I can offer some insights to help you troubleshoot the problem.

The circular reference error may occur because the formula references the cell containing the procedure, creating an infinite loop. To avoid this, you can modify the system to refer to a different cell that doesn’t have the formula.

Excel’s built-in Error Checking feature can help identify and resolve circular references. To access this feature, go to the Formulas tab in the Excel ribbon, click Error Checking, and then click Circular References. Excel will then highlight any circular references in your workbook and suggest resolving them.

Providing any prominent solution without glancing at the workbook is challenging. You can share your workbook with us via Exceldemy Forum to better understand your situation.

Regards

Lutfor Rahman Shimanto (ExcelDemy Team)

Hello DAVID,

Thank you for reaching out with your comment. You encountered a different problem than what was described in the article. There could be several reasons why the problem is happening. Some of your add-ins may mess up the delete sheet feature, which may not work in your version of Microsoft Excel. To fix this, you should update your program.

You can use an Excel VBA code to delete a sheet by inputting the sheet name.

Excel VBA Code:

The VBA code and suggestions will solve your issue. Good luck.

Regards,

Lutfor Rahman Shimanto

Hello AYESHA

Thanks for your query. I have analyzed the workbook Rafiul Haq shared. A prominent approach to avoid data overlapping is to create the employee record separately for each month. So don’t hesitate to make the Employee Leave description for each month.

Regards

Lutfor Rahman Shimanto

Hello ERICH NAGY

I appreciate your time in reading the article. I’m delighted you found the information helpful and learnt something new. Your attention to detail is really appreciated, and you are correct. The appropriate range should be the C5:C20 range instead of D5:D20. An immediate correction will be made. Once again, We appreciate your comments and support. Continue reading our blog, and we aim to offer you further helpful information soon.

Regards

Lutfor Rahman Shimanto (ExcelDemy Team)

Hello ERIC SCHOENTHALER,

Thank you for your nice words! Great that you found our website and information helpful and relevant to your goal. I’m glad we could provide you with the information you were looking for. Regarding your question, you can use the following VBA code to insert a specific amount of page breaks starting at a particular line.

Regards

Lutfor Rahman Shimanto

Thank you so much for sharing this tip, Andy! I sincerely appreciate the time you took to share your expertise and assist others with this problem. Your suggestion proved effective, and We are genuinely grateful for it. Thank you again!

Regards

Lutfor Rahman Shimanto