Osman Goni Ridwan

Hi Paulie,
Thanks for your comment. The VBA code is working completely fine from our end. Can you please share your workbook (.xlsm) file so we can check and find out the error easily?

Regards
ExcelDemy Team

Hi Sue,
Thanks for your comment. In the formulas given in the article to retrieve the City name, we used SUBSTITUTE to remove extra spaces, find the position of the first comma, and extract the city name using the MID function. Also, we had to insert the length of the city name manually. If you want to make it dynamic, you can use the following formula in cell D5:
Dynamic Formula to Retrieve City Name from Full Address:
=MID(B5, FIND(“,”, B5) + 1, FIND(“,”, B5, FIND(“,”, B5) + 1) – FIND(“,”, B5) – 1)
This formula finds the position of the 1st and 2nd commas and extracts all characters between them to get the city name.

And for state names, we have used formula to retrieve only those in the abbreviated formats. If you want to get state names in full forms, then use the given formula:
Dynamic Formula to Retrieve Full State Name from Full Address:
=MID(B5, FIND(“,”, B5, FIND(“,”, B5) + 1) + 2, FIND(“,”, B5, FIND(“,”, B5, FIND(“,”, B5) + 1) + 1) – FIND(“,”, B5, FIND(“,”, B5) + 1) – 2)

Using this formula, you will get all characters between the 2nd and 3rd comma. Thus you will get the state name in full form.

Hope, your problem will be solved. If not, share with us in the reply or you can share your workbook in the ExcelDemy Forum.

Best Regards,
ExcelDemy Team

Hi Strike,
Thanks for your comment. The Mix Variance formula provided in the article is based on Revenue rather than Quantity because it is intended to capture the impact of changes in the sales mix of different products on revenue, which includes both price and quantity components. Here’s the formula:

Mix Variance = Revenue PY * (Price PY – Average Price PY) * Mix Change / 100

By using Revenue PY in the formula, you are considering both the price and quantity component. Thus it becomes a comprehensive way to evaluate how the mix of products contributed to changes in revenue. It provides a more complete picture of why the revenue changed. Using Quantity alone would only consider the quantity component and not account for price changes, which are also crucial in understanding revenue variations.

If you have any further query, then please inform us in the reply section.

Regards,
ExcelDemy Team

Hi Harrison,
Thanks for your comment. Calculating the probability of success of a project is actually a complex task that depends on various factors. One common approach is to use a weighted scoring system based on certain criteria for each project. Here’s a simplified template you can use:

Step 1: Define Criteria and Assign Weights

=>> Create a list of criteria that are important for determining the success of a project. These criteria may include factors such as budget, timeline, team expertise, market demand, and alignment with strategic goals.

=>> Assign weights to each criterion to reflect their relative importance. For example, if the timeline is more critical than the budget, you might assign a higher weight to the timeline criterion.

Step 2: Evaluate Each Project

=>> Assess each project in your portfolio against the defined criteria. You can use a scale (e.g., 1 to 10) to rate each project’s performance on each criterion.

=>> Multiply the ratings by the assigned weights for each criterion and calculate the weighted score for each project.

Step 3: Calculate Probability of Success

=>> Add up the weighted scores for each project to get a total score. The total score represents the overall potential for project success.

=>> You can then convert this total score into a probability of success. This step is somewhat subjective, and you might need to set specific thresholds for what constitutes a “high probability of success“. For example, you could establish that a total score above a certain threshold (e.g., 80 out of 100) indicates a high probability of success.

=>> You may want to use historical data or expert judgment to help determine the relationship between total scores and success probabilities.

Finally, you can express the probability of success as a percentage. For instance, if a project has a total score of 85 out of 100, and this is associated with a high probability of success, you could say that the project has an 85% chance of success.

Hello Komal!
Thanks for your comment. I see that you are facing some troubles while running the Date Picker UserForm macro. As you have created some more text boxes so I have the following suggestion that you should check:

>> Check that all the textboxes should be inside one UserForm. Also, check whether all the macro codes are written for the same UserForm or not. They all must be under same UserForm to be run properly.

If still you are facing issue then please send us your workboook for this you can post it the ExcelDemy Forum. As the file is working completely fine from our end, it is necessary to have your workbook to find the error.

Regards

Hello Ankit,
Thanks for sharing your problem. The file is working completely fine from our end. So, if you think your Excel VBA function is not working after saving and reopening the workbook, there could be several reasons for this issue. Here are some common troubleshooting steps to help you resolve the problem:

1. Check for Macro Security Settings:
>> Excel has a security feature that may disable macros by default.
>> Make sure that macros are enabled in your Excel settings.
>> Go to “File” > “Options” > “Trust Center” > “Trust Center Settings.” Under “Macro Settings,” select “Enable all macros” or “Enable macros for this session” to allow macros to run.

2. Check Workbook FIle Extension
Check if the VBA code is stored in a macro-enabled workbook (with the .xlsm file extension) rather than a regular .xlsx workbook. Macros won’t run in regular Excel workbooks.

3. Check Event Triggers:
Check that we have used the code associated with each worksheet. Make sure that these events are still properly associated with the worksheets in your file.

I believe by following these steps, you should be able to identify and resolve the issue. Please inform us of the outcome in a reply. Thanks for staying with us!

Hello AMIR,

Hope you are doing well and thank you for your query. You can extract comments from word document with their respective locations in an Excel sheet. For this, you can use the following code:
Sub ExtractCommentsFromWordToExcel()
Dim cmt As Comment
Dim j As Long: j = 1
Dim xAPP As Excel.Application
Dim xWB As Excel.Workbook
Set xAPP = CreateObject(“Excel.Application”)
xAPP.Visible = True
Set xWB = xAPP.Workbooks.Add

Set xWB = Nothing
Set xAPP = Nothing
End Sub

Here is an image displaying the result in Excel sheet:

Hope you find this useful. Have a good day. Please let us know if you have any further queries. Also, you can post your Excel-related problems in the ExcelDemy Forum with images or Excel workbooks.

Hello ANDREW,
Hope you are doing well and thank you for your query. You can use VLOOKUP and MAX functions among other methods to look for a value when the value you want to analyze for is in columns D through O.
You can use the following formula.
=MAX(VLOOKUP(A2,Sheet2!$A$2:$O$11,{4,5,6,7,8,9,10,11,12,13,14,15},0))
You can find the solution to your problem in the Excel file linked to this reply.
VLOOKUP and MAX to Look for Max Value
Regarding your query of product names changing in accordance with your changing the names in Sheet 1, you can use cell reference to copy the product names in Sheet 2. This will look for the correct row of data in Sheet 2.
Here is a screenshot of the results in the Excel file.

Hope you find this useful. Have a good day. Please let us know if you have any further queries. Also, you can post your Excel-related problems in the ExcelDemy Forum with images or Excel workbooks.
Regards,
ExcelDemy Team

Hello FELICIA SANTOS,
I hope you are doing well! Here, I have created a dataset as you have described and calculated the total amount spent per category.
>> Here, you have to insert the name of the category in cell F5.
>> Then, insert the following formula into cell G5:
=SUMPRODUCT(D3:D10*(C3:C10=F5))

Thus, you will get the sum of the amount as per the selected category.

I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

Hello BRYAN!
As per your question, I have added a new portion in the article which contains the method how you can calculate the accrued time after the completion of probationary period automatically.

I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

Hello JUSTINA!
I have an easy solution to your problem. To copy data of range Sheet1 (A10072:AT10801) to range Sheet 2 (A10772:AT10801), you can use this VBA Code.

Sub copy_to_another_worksheet()
Worksheets("Sheet1").Range("A10072:AT10801").Copy _
Worksheets("Sheet2").Range("A10772:AT10801")
End Sub

And to copy data of range Sheet 1 (A10802:AT10831) to the range Sheet 2 (A10802:AT10831), use the following VBA code-

Sub copy_to_another_worksheet()
Worksheets("Sheet1").Range("A10802:AT10831").Copy _
Worksheets("Sheet2").Range("A10802:AT10831")
End Sub

I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

Hello BORIS!
You can try to “Multilayer Pie Chart” or “Bar of Pie chart” to solve your problem. You can follow this article to create these types of pie charts for your dataset. Please, go through this article,
How to Make a Multi-Level Pie Chart in Excel
I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

Hello N R RAVINDREA!
Thank you for your suggestion. We are taking this into concern. And, You can share your Excel-related problems in an email at [email protected]

Thank You!

Hello ARIF, to get the dates in the next row use the following code when you will insert values in cells of range B3:I3 and want to auto-populate date and time in cell range B4:I4.

CODE:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("B3:I3"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(1, 0).ClearContents
                Else
                    With .Offset(1, 0)
                        .NumberFormat = "dd mmm yyyy hh:mm:ss"
                        .Value = Now
                    End With
                End If
                Application.EnableEvents = True
            End If
        End With
End Sub

You can change the cell range as your want. I hope, your problem will be solved in this way. If not, please share the Excel file and send us the problem with a little more explanation in an email at [email protected]

Hello BEN!
I think the solution to your problem is already solved in method 1 of this article. To search for partial match, you have the wild cards (*) that have been shown in method 1.
If the cell C13 contains the value of the search item. Then use the following formula:
=VLOOKUP(“”&C13&””,$B$4:$C$11,2,FALSE)

I hope, your problem will be solved in this way. If not, please share the Excel file and send us the problem with little more explanation in an email at [email protected]

Thank You!

Thank you, BOKEP. Thank you for your comment. Read more articles on Exceldemy and share them with your friends and family. Keep supporting us!

Thank you! Shakeel. We are really honored to hear that our articles have helped you. Browse articles of Exceldemy more and share with your friends and family and keep supporting us!

Hello M LAVI! with this code, only the column of the active cell that contains the target cell value will be hidden.
And, you can copy and paste the drop-down cell to other worksheets without assigning data validation again. If you have anything more to know then inform us in a comment!

Hello SHAWN S FAHRER!
You can get a quadratic equation from a graph with a few points that you have mentioned here. Follow the steps below for this
First, create a graph with the points you have.
Then, Go to the Chart Elements clicking the Plus icon.
Click on the arrow beside the “Trendline” option.
Go to the “More Options”
1

Here, you will go to a window named “Format Trendline”.
Select the “Polynomial” option of order 2.
Mark the checkbox saying “Display Equation on Chart”.

As a result, you will there will create a trend line following a quadratic equation which is also shown in the graph.

Try this method and let us know the outcome! Thank You!

Hello, JACK! For this, you have to add a new conditional Formatting rule. Go to Home tab >> Conditional Formatting >> New Rule.

Then, Select the “Format Only Cells That Contain” and select the “Blanks” option in the “Format only cells with” box.

Then, go to the “Format” option and select White as the fill color.

Try this and let us know the outcome. Thank you!

Hello APRIL, We already have an article written based on your problem. I hope, you will find this helpful. Follow this link below-

https://www.exceldemy.com/excel-vlookup-multiple-criteria-without-helper-column/

Try the methods mentioned in this article and let us know the outcome. Thank you!

Hello Johan, you can use the following procedure to serve your purpose-
– Make a column with 101, 102, 103, and so on.
– Make a column with A, B, C, D, and repeat.
Follow this article to repeat numbers or characters serially.
How to Add Numbers 1 2 3 in Excel

– Then use CONCATENATE function to combine them.

Let us know the outcome in the reply. Thank you!

hello, you can use the FILTER function to do this. Suppose, you have inserted your dataset into the range B2:D6.
And, in cell G2. you have inserted the id for what you want to get month and payment.
so, to get the required data, you have to insert the following formula into any blank cell:

=FILTER(C3:D6,G2=B3:B6)

Use this formula for your dataset and let us know the outcome in the reply. Thank you!

Thank You, Roy, for your precious comment!

Hello SONYA, you can face this issue because of the following reasons:
1. Incorrect Cell Ranges used in the formula
2. The Date format used in the formula is not correct. You have to use a similar data format both in the cells and formulas.

Check these things in your workbook and let us know the outcome. Thanks!

Hello Peter and Charlie, the solution described in this article at Criteria 2: “Extracting Data Based on 1 Row & 2 Columns with SUM, INDEX and MATCH Functions Together” is perfectly working from our end.
Just to inform you, you can’t use a cell reference in the Look_up value of the MATCH function instead you have to insert the single of multiple look_up values manually e.g. {“Feb”,”Jun”}. Hope you have found your solution. Try it and let us know the outcome in a reply. Thank you!