Hi JUN,

Thanks for your comment. I assume you are having this problem because Excel considers 1 (‘dataset’!=”1″) as a number. You don’t need to add the double quotation symbol for that part. So, an error occurs and it is showing “–” instead of “A“. You can use the formula below to get “A“:
=IFERROR((INDEX(datasheet!C:C,MATCH(1,(datasheet!A:A=1)*(datasheet!B:B="a")*(datasheet!D:D="22/8/1"),0))),"-")
To get “B“, use the formula below:
=IFERROR((INDEX(datasheet!C:C,MATCH(1,(datasheet!A:A=2)*(datasheet!B:B="b")*(datasheet!D:D="22/8/1"),0))),"-")
And to get “C“, you can use the formula below:
=IFERROR((INDEX(datasheet!C:C,MATCH(1,(datasheet!A:A=3)*(datasheet!B:B="c")*(datasheet!D:D="22/8/1"),0))),"-")
For your convenience, I have attached the excel file below with the formulas that I have used. I have used the formulas in Excel 365.
Download Excel File
I hope this will help you to solve your problem. If you still find any problem in your excel file, then you can send it to [email protected]. I will take a look and email you the solution.
Thanks!

Hi MYLES,

Thanks for your comment. We are very glad to know that we could be of help to you. Let us know if you have any queries.

Good luck.

Hi STEPHEN,

Thanks for reaching out to us. I am replying to you on behalf of Exceldemy. We will be happy to help you.
Please send the Excel file to [email protected]. Also, mention what you want to update in the Excel file. We will try to give the solution as early as possible.

Thanks!

Hi KATIA,

Thanks for your comment. I am replying to you on behalf of Exceldemy. To find the average, you can use the AVERAGE function. For Method 1, you can follow the steps below.
STEPS:
1. Firstly, select Cell F5 and type the formula below:
=AVERAGE(C4:INDEX(C4:C12,E4))

2. Press Enter to see the result.
3. Secondly, select the range B3:C12.

4. Press Ctrl + T to convert the range into a table.
5. A message box will appear.
6. Click OK to proceed.
7. As a result, you will see a table like the picture below.

8. Now, if you add Oct-15 in Cell B13, then the table and formula of Cell F5 will automatically update.

For Method 2, you can follow the above steps and use the formula below:
=AVERAGE(OFFSET(B4,0,0,1,-MONTH(B3)):B4)
For Method 3, use the formula below and convert the range into a table:
=AVERAGEIFS(C4:C17, B4:B17, ">="&B4, B4:B17, "<="&F5)
You can also find the formulas in the workbook below:
Workbook with AVERAGE Formulas.xlsx
I hope this will help you to solve your problems. Please let us know if you have other queries.
Thanks!

Hi JESSICA,

Thanks for your comment. I am replying to you on behalf of Exceldemy. You can check the comment thread for the answer. Also, to know more about copying formulas across cells, you can check the link below.
Copy Formulas Across Cells
Thanks!

Hi JOJO,

Thanks for your comment. I am replying to you on behalf of Exceldemy. You can use VBA to remove texts that have strikethrough in them. Suppose, you have some text strings like the picture below:

Let’s follow the steps below to remove the texts with a strikethrough.
STEPS:
1. Firstly, go to the Developer tab and click on the Visual Basic option.

2. Secondly, select Insert >> Module to open the Module window.

3. Now, copy the code below and paste it into the Module window:
Sub Remove_Strike_through_Texts()
Dim iRng As Range, iCell As Range
Dim iStr As String
Dim X As Long
On Error Resume Next
Set iRng = Application.InputBox("Please select range:", "Microsoft Excel", _
Selection.Address, , , , , 8)
If iRng Is Nothing Then Exit Sub
Application.ScreenUpdating = Fase
For Each iCell In iRng
If IsNumeric(iCell.Value) And iCell.Font.Strikethrough Then
iCell.Value = ""
ElseIf Not IsNumeric(iCell.Value) Then
For X = 1 To Len(iCell)
With iCell.Characters(X, 1)
If Not .Font.Strikethrough Then
iStr = iStr & .Text
End If
End With
Next
iCell.Value = iStr
iStr = ""
End If
Next
Application.ScreenUpdating = True
End Sub

4. Press Ctrl + S to save the code.
5. After that, go to the Developer tab and select Macros.

6. In the Macro window, select the desired code and Run it.

7. A message box will appear.
8. Select the range from where you want to remove the strikethrough texts.
9. Finally, click OK to proceed.

10. As a result, the texts with strikethrough will be removed.

I hope this will help you to solve your problem. Please let us know if you have other queries.
Thanks!

Hi DAWID,

Thanks for your comment. Sorry to hear that you are not getting the desired output. I have tried to convert 192697 and 194898 to barcodes using the same code. In my case, it worked. You can see the result below:

I am also attaching the Excel file below:
Answer.xlsm
Moreover, the VBA code is not error-free. It can introduce errors sometimes. You can copy the code and paste it into a new workbook. It may help you to get the desired results.
I hope this will help you to solve your problem. Please let us know if you have other queries.
Thanks!

Hi MIKE M,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. You can mail your problem to [email protected]. Our team will take a look and try to give a solution to your problem.

Thanks!

Hi PETE,

Thanks for your comment. I am replying on behalf of Exceldemy. To paste the values, you can try the code below:

Sub Copy_Data_from_Another_Workbook()
Dim wb As Workbook
Dim newwb As Workbook
Dim rn1 As Range
Dim rn2 As Range
Set wb = Application.ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
.Filters.Clear
.Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
Application.Workbooks.Open .SelectedItems(1)
Set newwb = Application.ActiveWorkbook
Set rn1 = Application.InputBox(prompt:="Select Data Range", Default:="A1", Type:=8)
wb.Activate
Set rn2 = Application.InputBox(prompt:="Select Destination Range", Default:="A1", Type:=8)
rn1.Copy
rn2.PasteSpecial xlPasteValues
newwb.Close False
End If
End With
End Sub

Here, we have used the code of Method 1. We changed the highlighted lines to copy and paste only the values.
I hope this will help you solve your problem. Please let us know if you have any other queries.
Thanks!

Hi A V S S PRASAD,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. To match the addresses, you need to use the INDEX-MATCH functions. You will find similar formulas in the articles below.
https://www.exceldemy.com/index-match-multiple-criteria-partial-text/
https://www.exceldemy.com/index-match-partial-match/
I hope this will help you to solve your problem.
Thanks!

Hi BRIAN WINKLE,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. To create a timestamp like the given example, you can take a look at the article below.
https://www.exceldemy.com/create-a-timesheet-in-excel/
I hope this will help you to solve your problem.
Thanks!

Hi JOE FRAZIER,

Thanks for your comment. I am replying to you on behalf of Exceldemy. In this article, Cells I16:I22 of Column I counts the overtime for each day of a week. That means if you want to see the overtime for Monday, you need to check Cell I16. So, you can use the same formula to calculate overtime for a single day.
I hope this will help you to solve your problem. Please let us know if you have other queries.
Thanks!

Hi DEE ZELAYA,

Sorry for the late reply. I am replying to you on behalf of Exceldemy. Here, Cell D12 is renamed as work_hours_per_week. You can use the Name Manager in the Formulas tab to define the name. To check the defined names, follow the steps below.
Firstly, download the practice book, go to the Formulas tab and select Name Manager.

In the Name Manager box, you will find all the defined names.

To apply the same formula in your new spreadsheet, you need to define the names using the Name Manager. To do that, you can follow the link below.
https://www.exceldemy.com/excel-edit-named-range/
Thanks!

Dear JESSE BATES,
Thanks for your comment. Declaring variable types is not mandatory in VBA. VBA by default assigns the necessary data type to any variable, you just need to put the values. In the given code, the timer variable contains the data type Double.
Please let us know if you have any other queries.
Thanks!

Dear MICHAEL,
Thanks for your comment. You can insert a Tab using “~009” inside the string. For example, XXXX.1TAB123456 can be written as XXXX.1~009123456. But unfortunately, it will not show two separate fields in one barcode. From my understanding, you need to use other barcode fonts for that purpose.
Otherwise, you can look at the solution below. But this solution will not provide you with one barcode. Here, we tried to concatenate two separate barcodes in one cell. Here, we will use the dataset below.

Let’s follow the steps below for the solution.
● Firstly, change the fields into barcode strings.
● To do so, select Cell D5 and type the formula below:
=Code128(B5)

● Also, do the same for the second field.

● After that, select Cell B7 and type the formula below:
=CONCAT(D5,CHAR(9),E5)

● Hit Enter to see the result.

● Now, select Cell B7 again and change the font theme to Code 128.
● Also, adjust the font size.

● Finally, you will get results like the picture below.

I hope this solution will help benefit you to some extent. Please let us know if you face any other issues.
Thanks!

Hi MKM,
Thanks for your comment. To answer your question, we can use the dataset below. It contains 3 rows of sales for Germany. The first sale happened in March and the other two sales happened in May. From this dataset, we can easily calculate the average sales for May in Germany.

To calculate the average sales for May in Germany, select Cell F5 and type the formula below:
=AVERAGEIFS(D5:D15, B5:B15,”<=5/31/2022", B5:B15,">=5/01/2022″,C5:C15,”Germany”)
Press Enter to see the result.

I hope this will help you to solve your problem. Please let us know your queries if you face any issues.
Thanks.

Dear CHRIS,
Thanks for your comment. Excel omits the leading zero by default. So, it’s not possible to get the desired barcode.
• To solve this, you must add an apostrophe (‘) in front of the leading zero.
• So, select Cell C5 in the VBA sheet of the workbook.
• Type ‘02628107336750 in Cell C5.
• Press Enter.
• You will get your results in Cell D5.
I hope this will help you to solve your problem.
Thanks.

Hello, Dom!
Thanks for your feedback. If you are facing any problems generating code 128 barcode font in Excel, then you can ask here. We will try to reply to you as soon as possible.
Thanks!

Hi BIPLAB,
Thanks for your query. You can build a formula with the nested CONCAT function. In that case, you don’t need any helper column. For the dataset we have discussed in the article, the formula will be:
=CONCAT(CONCAT(B5,” “,C5,” “,D5),”,”, CONCAT(B6,” “,C6,” “,D6),”,”,CONCAT(B7,” “,C7,” “,D7),”,”,CONCAT(B8,” “,C8,” “,D8))
You can also use the VBA code below. To apply this, you need to select the rows of the first column of the dataset and then run the code from the Macro window.

Sub Merge_Rows_with_Comma()

Dim iSelection As Range
Dim iRow As Range
Dim iCell As Range
Dim iStr As String

Application.ScreenUpdating = False
On Error Resume Next
Set iSelection = Intersect(Selection, ActiveSheet.UsedRange)

For Each iRow In iSelection.Rows
For Each iCell In iRow.Cells
iStr = iStr & ” ” & VBA.Trim$(iCell)
Next iCell
iRow.ClearContents
iRow.Cells(1, 1).NumberFormat = “@”
iRow.Cells(1, 1) = Mid(iStr, 2)
iStr = vbNullString
Next iRow

For Each iCell In iSelection
iStr = iStr & “,” & VBA.Trim$(iCell)
Next

With ActiveWindow
.Selection.ClearContents
.Selection(1, 1).NumberFormat = “@”
.Selection(1, 1).Value = Mid(iStr, 2)
End With
End Sub