##### User Posts: Mashhura Jahan

Whenever working with Excel you may face a situation where you will need to link data from other workbooks to get a large amount of data. But this data changes ...

A Cross Join combines each row from the first table with each row of the second table. Cross Join returns all the possible combinations of two tables. The ...

The X Bar symbol is used in statistics frequently. Whenever working with Excel, you may face a situation where you will need to write X Bar. The focus of this ...

A Spin Button helps to increase or decrease values easily by clicking on the button easily. But, you can not get negative values directly by using the Spin ...

Whenever working with a set of data a regression line helps you to see the connection between the scatter data points. If you are looking for a way to find the ...

When you divide a number by another number it returns a quotient and a reminder. If you are looking for a way to separate these two in Excel then you are in ...

If you are looking for a way to calculate 2 sigma then you have come to the right place. The main objective of this article is to explain how to calculate 2 ...

If you want to calculate payroll for overtime then this article will be beneficial for you. The focus of this article is to explain how to calculate payroll ...

If you are looking for a way to scrape data from a website, this article will be helpful for you. The focus of this article is to explain how you can use Excel ...

If you are stuck with an Excel file that is password protected and you donâ€™t know the password then this article will be beneficial to you. The objective of ...

There are many ways of calculating Depreciation. Units of Production is one of them. The main objective of this article is to explain how to calculate the ...

If you are looking for a way to calculate NPV for monthly cash flows then you have come to the right place. The focus of this article is to explain how to ...

A data table helps to represent data more clearly in an Excel chart. If you are looking for a way of editing a data table in an chart then you have come to the ...

The Sunburst Chart in Excel is generally used for displaying hierarchical data. Each ring of the chart represents each level of the hierarchy. The data in a ...

If you are looking for a way to translate language with formula in VBA then you have come to the right place. Excel doesnâ€™t provide a function for translation. ...

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

Hey NICO,

Thank you for your comment. I am replying on behalf of ExcelDemy. You can use the

IFfunction 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.

Hey EXC,

Thank you for your comment. I am replying on behalf of ExcelDemy. If you are facing this problem with

Method 1then you will have to select the reference cell in the formula from the first row of the range you are selecting. You can see that rangeC5:D12is selected in this article. And, cellsC5andD5were used in the formula. These cells are in the first row of the selected range. You will have to maintain this rule while writing the formula.I hope this will help you to solve your problem. And, if it doesnâ€™t, let us know in which method you are facing the problem.

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

KILOMETERSthen you can use the followingVBAcode and the rest of the procedures will be the same.I hope this will help you to solve your problem. Please let us know if you have other queries.

Regards

Mashhura Jahan

ExcelDemy.

Hello ZAIN,

Thanks for your comment. I am replying to you on behalf of ExcelDemy. For

Example 5, you will have to keep2things in mind.1. You will have to change the

ColumnCountin theListBoxPropertiesaccording to your dataset.2. In the

VBAcode, you will have to changeRowandColumnnumbers according to your dataset.I hope this will help you to solve your problem. If it fails to solve your problem, then please specify where are you facing the problem.

Regards

Mashhura,

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 theQR Codeby following these simple steps.Step-01:Write the followingVBAcode 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,

Savethe code and go back to your worksheet.Step-02:Select the cells where you want theQR Codes>> go to theHometab >> selectBottom Align.Step-03:Select the cell where you want theQR Code>> write the following formula in that cell.`=GenerateQR(B5)`

Next, press

Enterand you will get theQR Codewith the Label.Step-04:Finally, get theQR Codesfor 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 ERIK NIELSEN,

Thanks for your comment. I am replying to you on behalf of ExcelDemy. If you want to get metadata from subfolders of the specified source folder then you can use the

jamsoftwarethat is used in theâ€śHow to Export File Metadata to Excelâ€ťsection of this article. Letâ€™s see the steps.â€˘ Firstly, download and then copy and paste the

FileList.exefile to the folder from where you want to get the metadata by following the steps from theâ€śHow to Export File Metadata to Excelâ€ťsection of this article.â€˘ Secondly, press

Windows Key+R.â€˘ Thirdly, write

cmdin theOpensection.â€˘ Then, select

OK.â€˘ After that, write

. Here,`cd C:\Images`

is the source folder path.`C:\Images`

â€˘ Then, press

Enter.â€˘ Next, write the command below.

`FileList.exe /USECOLUMNSNAME,FULLPATH,LASTCHANGE,EXTENSION,SIZE, AUTHORS,System.Photo.DateTaken > output.txt`

â€˘ Then, press

Enter.â€˘ After that, minimize the window.

â€˘ Now, you will see a

txtfile is created in your selected location. It contains the metadata from all the subfolders.â€˘ Next, open an Excel file >> go to

Datatab >> selectFrom Text/CSV.â€˘ Afterward, select the

txtfile >> selectImport.â€˘ Now, you will see the metadata are imported to a table.

â€˘ Then, select

CommaasDelimiter>> selectLoad.â€˘ Finally, you will see all the metadata from the subfolders is loaded to an Excel sheet.

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

VBAcode 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,

Savethe code and go back to your worksheet. Letâ€™s see the steps of using the function.Step-01:Select the cells where you want theQR Codes. Here, I selected cell rangeD5:D7>> write the following formula.`=QR_Generator(C5)`

Step-02:PressCtrl + Enterand 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.

Hi TIAGO,

Thanks for your comment. I am replying to you on behalf of ExcelDemy. You can get the correct value by using the

COUNTAfunction instead of theSUMfunction. Letâ€™s see the steps.Step-01:Write the following formula in the selected cell.`=E5/COUNTA($C$5:$C$16)`

Step-02:PressEnterto get theAverage.Step-03:Drag theFill Handledown to copy the formula to the other cells.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 pressingCtrl + Con your keyboard.Step-02:Select the cell where you want to paste the range. And then go toHome > 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

Hello JC,

Thanks for your comment. I am replying to you on behalf of ExcelDemy. The downloaded file works for me.

You can follow the steps from the Feature of

Eisenhower Matrix Template in Excelsection. If it still doesnâ€™t work then check if all cells contain the formula in theEisenhower Matrix.And, while making your own

Eisenhower Matrix, check if you are applying for the same range as in this article. If not, change your ranges accordingly in the formula.Lastly, if you are using an older version of

Microsoft Excelthen pressCtrl + Shift + Enterwhile entering the formula.I hope this will help you to solve your problem. If none of these works then you can let us know at [email protected] with your Excel file and problem details. We will try our best to solve your problem.

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 fromMethod-2. But, instead of the formula used here, write the following formula.`=C5<(TODAY()-30)`

Here, I wrote the formula for dates older than

30days 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

Hi Penny,

If you check to see if the cells are blank or not then they will be evaluated as not blank because the cells contain formula. If you need blank cells then you can follow these steps.

Step-01:In aHelper Columnuse any of the formulas from this article. But instead of an empty string (`""`

) write a letter or a special character (`"#"`

) in the formula.`=IF((D5*E5)>2000,D5*E5,â€ť#â€ť)`

Step-02:Copy the values from theHelper Columnand Paste them asValueswhere you want the final output.Step-03:PressCtrl+Hand theFind and Replacedialog box will appear. Replace#with nothing and you will get your blank cells.Finally, if you check the cells, then you will see the cells are blank.

I have shared the necessary images for your convenience. I hope this will solve your problem. Please let me know if you have other queries.

Thanks!

Hello DQNOK,

Thank you for your feedback.

Hello Frank,

This article contains different methods for different situations.

Method 1.3hides the numbers that are less than Zero. But, if you want to hide zeros only then you can try the following code.`Option Explicit`

`Sub Hide_Rows_With_Zero()`

`Dim row As Long`

`Dim col As Long`

`Dim qq As Boolean`

`For row = 6 To 14`

`qq = True`

`For col = 2 To 4`

`If Cells(row, col).Value <> 0 Then`

`qq = False`

`Exit For`

`End If`

`Next col`

`Rows(row).Hidden = qq`

`Next row`

`End Sub`

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

Thanks!