Mashhura Jahan

Hi DOUG KIMZEY,
Thank you for your comment. I am replying on behalf of ExcelDemy. Power Query sometimes shows grayed-out menus if you try to perform mathematical operations in multiple columns at the same time.

• To solve that problem, you can select the column >> select Standard >> select Divide (or whatever operation you want).

• Select the Value according to your preference. Here, I want to divide by 200 because these scores are in 200 >> select OK.

• Now, you can see the values in the column are divided by 200.

• In the formula bar, add your preferred operations >> click on the Tick mark.

• Finally, you will see that you have performed the grayed-out features without using them.

If this tip doesn’t work for you then please share a screenshot of your problem or the Excel file with us so that we can see what is causing the problem and solve it.
Regards,
Mashhura Jahan
ExcelDemy.

Hi PIET,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. You can apply the following code. By using this code, you will be able to double-click on any cell from columns C, G, or L and the entire range (A4:L60) will be sorted based on the column of the selected cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim srt_rng As Range
    Dim srt_col As Range
    Set srt_rng = Range("A4:L60")
    If Not Intersect(Target, srt_rng) Is Nothing Then
        If Target.Column = 3 Or Target.Column = 7 Or Target.Column = 12 Then
            Set srt_col = Target.EntireColumn
            With srt_rng
                .Sort key1:=srt_col, Order1:=xlAscending, Header:=xlYes
            End With
            Cancel = True
        Else
        Exit Sub
        End If
    End If
End Sub

I hope this will help you to solve your problem. Please let us know if you have other queries.
Regards
Mashhura Jahan
ExcelDemy.

Hi RICHARD O’CONNOR,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. You can apply the following formula to calculate working days until a future date. And then, drag the fill handle down to copy the formula in other cells.

=NETWORKDAYS.INTL(C5,D5,1,$G$5:$G$9)

Here, Cell C5 is start_date, D5 is end_date, 1 refers to Saturday and Sunday as the weekend, and range $G$5:$G$9 refers to the holidays.
Note: If you skip weekend and holidays arguments in the formula then it will take Saturday and Sunday as weekends and no holidays.
I hope this will help you to solve your problem. Please let us know if you have other queries.
Regards
Mashhura Jahan
ExcelDemy

Hey NICO,
Thank you for your comment. I am replying on behalf of ExcelDemy. You can use the IF function to check through every error group. The formula will be something like this:

ID=IFERROR(IF(error group = 1,TEXTJOIN(“, “,TRUE,FILTER(ID array ,error group 1 array=”x”,””)),IF(error group = 2,TEXTJOIN(“, “,TRUE,FILTER(ID array ,error group 2 array=”x”,””)),IF(error group = 10,TEXTJOIN(“, “,TRUE,FILTER(ID array ,error group 10 array=”x”,””)),””))) ,””)

I hope this will help you to solve your problem. Please let us know if you have other queries.
Regards
Mashhura Jahan
ExcelDemy.

Hi BIANKA,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. If you want to get the distance in KILOMETERS then you can use the following VBA code and the rest of the procedures will be the same.

Option Explicit

Public Function Driving_Distance(startlocation As String, destination As String, keyvalue As String)

Dim First_Value As String, Second_Value As String, Last_Value As String, mitHTTP As Object, mitUrl As String

First_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
Second_Value = "&destinations="
Last_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"

Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")

mitUrl = First_Value & startlocation & Second_Value & destination & Last_Value
mitHTTP.Open "GET", mitUrl, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")
Driving_Distance = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3) * 1.60934, 0)

End Function

I hope this will help you to solve your problem. Please let us know if you have other queries.
Regards
Mashhura Jahan
ExcelDemy.

Hi XIONG YANG,
Thanks for your comment. I am replying on behalf of ExcelDemy. You can not add a label directly in the QR Code. But, you can add a label to the cell where you are inserting the QR Code by following these simple steps.
Step-01: Write the following VBA code in the module instead of the code that is provided in this article.
Function GenerateQR(qrcode_value As String)
Dim URL As String
Dim My_Cell As Range
Set My_Cell = Application.Caller
URL = "https://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl=" & qrcode_value
On Error Resume Next
ActiveSheet.Pictures("My_QR_CODE_" & My_Cell.Address(False, False)).Delete
On Error GoTo 0
ActiveSheet.Pictures.Insert(URL).Select
With Selection.ShapeRange(1)
.Name = "My_QR_CODE_" & My_Cell.Address(False, False)
.Left = My_Cell.Left + 5
.Top = My_Cell.Top + 5
End With
GenerateQR = ActiveCell.Offset(0, -1).Value
End Function

Now, Save the code and go back to your worksheet.
Step-02: Select the cells where you want the QR Codes >> go to the Home tab >> select Bottom Align.

Step-03: Select the cell where you want the QR Code >> write the following formula in that cell.
=GenerateQR(B5)

Next, press Enter and you will get the QR Code with the Label.

Step-04: Finally, get the QR Codes for other data in the same way.

I hope this will help you to solve your problem. Please let me know if you have other queries.
Regards
Mashhura,
ExcelDemy.

Hey MARK,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. For the provided practice workbook, I used the following VBA code and it solved this problem for me.

Function QR_Generator(qrcodes_values As String)

Dim Site_URL As String
Dim Cell_Values As Range

Set Cell_Values = Application.Caller
Site_URL = "https://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl=" & qrcodes_values
On Error Resume Next
Worksheets("Using User Defined Function").Pictures("Generated_QR_CODES_" & Cell_Values.Address(False, False)).Delete
On Error GoTo 0
Worksheets("Using User Defined Function").Pictures.Insert(Site_URL).Select
With Selection.ShapeRange(1)
.Name = "Generated_QR_CODES_" & Cell_Values.Address(False, False)
.Left = Cell_Values.Left + 2
.Top = Cell_Values.Top + 2
End With
QR_Generator = ""

End Function

Now, Save the code and go back to your worksheet. Let’s see the steps of using the function.
Step-01: Select the cells where you want the QR Codes. Here, I selected cell range D5:D7 >> write the following formula.
=QR_Generator(C5)

Step-02: Press Ctrl + Enter and you will get your desired output.

I hope this will help you to solve your problem. Please let me know if you have other queries.
Regards
Mashhura,
ExcelDemy.

Hello Alun Vaughan-Evans,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. There is a way to keep the formatting and also values. You can follow these steps to do that.
Step-01: Select the range you want to copy and then copy the range by pressing Ctrl + C on your keyboard.

Step-02: Select the cell where you want to paste the range. And then go to Home > Paste > Values & Source Formatting (like the following image).

Finally, you will see that you have copied the values with formatting.

I hope this will help you to solve your problem. Please let me know if you have other queries.
Regards
Mashhura
ExcelDemy

Hi SEM,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. If you want to filter dates older than a number of days from today, you can follow the steps from Method-2. But, instead of the formula used here, write the following formula.
=C5<(TODAY()-30)

Here, I wrote the formula for dates older than 30 days from today. You can change the formula according to your preference.
I hope this will help you to solve your problem. Please let me know if you have other queries.
Regards
Mashhura
ExcelDemy

Hello DQNOK,
Thank you for your feedback.