User Posts: Durjoy Paul
0
Default Alignment of Numbers in Excel (Detailed Analysis)
0

When you enter any number in the Excel worksheet cell, you will see the number will align on the right side of the cell as a default. This is a normal ...

0
AVERAGEIFS Function with “Not Equal to” Criteria (3 Examples)
0

Sometimes, you need to find out the average of your dataset excluding certain cells. In that case, you can use the AVERAGEIFS function. The AVERAGEIFS function ...

0
How to Use SUM and 3D Reference in Excel (Detailed Analysis)
0

Sometimes you have several worksheets to work on and finally, you need to have the SUM of a certain value from different worksheets. In that case, the 3D ...

0
How to Add Up and Down Buttons in Excel (2 Suitable Examples)
0

Sometimes you need to increase or decrease the cell value. It is tough to change cell value every time. In that case, Excel gives you a perfect solution to add ...

0
How to Hide Secondary Axis in Excel Without Losing Data
0

Sometimes, you need to use a secondary axis in Excel to express the dataset more clearly. Because of using a secondary axis, we will have a new axis and ...

0
How to Use Spell Number in Excel (2 Suitable Examples)
0

Sometimes you need to convert the number into a text value. In that case, you can use the spell number function in Excel. Though it is not a built-in function ...

0
How to Combine Two Tables Using Power Query in Excel
0

Sometimes, you need to combine two tables in Excel. You can do it manually but that takes a lot of valuable time. Instead of doing this manually, you need to ...

0
How to Calculate Confidence Interval in Excel (4 Easy Methods)
0

When you want to do research with a large number of people, it is impossible to take all people individually at a time. That’s why we can take a small number ...

0
Convert Decimal Coordinates to Degrees Minutes Seconds in Excel
0

Sometimes you have some decimal coordinates in your Excel spreadsheet and you need to convert them to degrees minutes or seconds in Excel. In Microsoft Excel, ...

0
Return Value If Time Is Greater Than and Less Than a Range in Excel
0

Sometimes, we need to find remarks if time is greater than and less than in Excel. In that case, there are starting and closing times. We need to find if the ...

0
How to Remove Excel Links from Word Document
0

Links in Microsoft Word can take you to that certain Excel file from where you created the link in Microsoft Word. After going to that Excel file, if you alter ...

0
Excel VBA: Copy Range to Clipboard (7 Suitable Examples)
0

In Microsoft Excel, you can copy the range to the clipboard using the VBA code. In this article, we will show several examples to copy range to the clipboard ...

0
Excel VBA: Clear Contents If Cell Contains Specific Values
0

Sometimes you need to clear contents if the cell contains several items in Excel VBA. It can be a numeric value, text, date, specific value, or blank. You can ...

0
What-If Analysis in Excel with Example
0

What-if analysis is a process through which you can see the outcome by changing any cell in the dataset. In Microsoft Excel, you can get three different types ...

0
How to Skip Every Other Column Using Excel Formula (3 Methods)
0

Sometimes you need to skip every other column in Excel. Using Excel Formulas, you can do such a job in a few moments. In this article, we will show the Excel ...

Browsing All Comments By: Durjoy Paul
  1. Hi Niki,
    To find out the price, you need to calculate the product of unit and unit quantity. Your sheet 1 denotes the unit of rate 1, rate 2, rate 3, and rate 4. The unit quantity will be found in sheet 2. So, in that case, you can apply the VLOOKUP formula to get the specific unit and unit quantity. You can use the following formula to calculate.
    =VLOOKUP(J8,$B$7:$C$12,2,FALSE)*VLOOKUP(J8,Sheet1!$B$4:$C$8,2,FALSE)
    =VLOOKUP(J8,$B$7:$C$12,2,FALSE):
    First, you need to define the value you are looking for. Here, J8 refers to the lookup value. After that, define the table array where you think the required answer can remain. Then, define the column number of the expected result. Finally, use false to get the exact match. Then, we have to multiply the result of the first VLOOKUP by the result of the second VLOOKUP.
    VLOOKUP(J8,Sheet1!$B$4:$C$8,2,FALSE):
    As we put the unit in sheet 1, we have to look up the rate 1 unit by utilizing the VLOOKUP function. First, define rate 1 as the lookup value. Then, set the table array in sheet 1. After that, define the column number of the expected result. Finally, use false to get the exact match.
    The product of these two values will give the price of rate 1. Do the same procedure for other cases.
    Do you get your preferred answer? check on it. Otherwise, give us a more accurate question. We are always ready to help you.
    Thank you.

  2. Hello Anton,
    First of all, you have to create three columns including items produced in 2018. Here, we need to paste the same value as items produced in 2019. Just one or two blanks in there.
    null

    Then, take a new cell and write down the following formula.

    =IF(COUNTIF(D5:D12,B5:C19)>0,B5:C19,””)

    Here, the range becomes (D5:D12 as the column of items produced in 2020 changes. Then, the criterion becomes B5:C19. Here, it follows if the condition match for both cells then it will return the value. Otherwise, it returns blank. After that, press Ctrl+Shift+Enter as it is an array formula.
    Here, you can see, that the product Trousers is available in 2019 and 2020. but, we give criteria for products in 2018 and 2019. As they don’t find trousers in 2018, that’s why you won’t get them in the result. So, you will get the products that are available in both 2018 and 2019.
    null

  3. Hello BOMBSHELLSHOCK, first, you need to convert your dataset into Excel table format. To do that, you have to select your whole dataset and go to the Insert tab on the ribbon. After that, select Table from the Tables group. As a result, it will convert your dataset into a table. Then, you can write something in the last row and column and press Enter. As a result, Excel will insert new rows and columns into your Excel table. So, to perform the whole process, you must convert your dataset into a table. Mind it.
    If you face any more problems, you can share them with us. We are happy to deal with it and provide you with a proper solution.

  4. Hello Tomas,
    You can store these non-object events in a personal file module. The solution is applicable to any module. First, you need to define the time value perfectly. To do that, you may use the following code.
    Application.OnTime TimeValue(“11:48:00 am”), “DisplayAlarm”
    If you set the time value properly, then there will be no problem in any module. Hope you get your required solution.

  5. Hello Nathan,
    first of all, if you want to use sum color function, you can use the following code.

    Function SumByCol(SumRange As Range, SumColor As Range)
    Dim SumColValue As Integer
    Dim TotSum As Long
    SumColValue = SumColor.Interior.ColorIndex
    Set pCell = SumRange
    For Each pCell In SumRange
    If pCell.Interior.ColorIndex = SumColValue Then
    TotSum = TotSum + pCell.Value
    End If
    Next pCell
    SumByCol = TotSum
    End Function

    Then, close the VBA code and select any cell where you want to put the sum value. After that, write the following formula
    =SumByCol(C5:C10,C13)
    Where the range of cells C5 to C10 defines the range. C13 defines the color. You need to show only color in one cell. From where you can take the reference. Here, C13 denotes that color reference. So, when you want to change the color, you have to change the color reference also.
    null

  6. Hello Marian,
    There are some unwanted issues that may arise while dealing with the VBA code. In that case, you need to utilize the following code.
    Sub Extract_Data_from_PDF()

    Dim MyWorksheet As Worksheet

    Set MyWorksheet = ActiveWorkbook.Worksheets(“Sheet1”)
    Application_Path = “C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe”
    PDF_Path = “C:\Users\USER\Desktop\Durjoy\Comments\New folder\standardnormaltable.pdf”

    Shell_Path = Application_Path & ” “”” & PDF_Path & “”””
    Call Shell(pathname:=Shell_Path, windowstyle:=vbNormalFocus)

    Application.Wait Now + TimeValue(“0:00:03”)

    SendKeys “%vpc”
    SendKeys “^a”
    SendKeys “^c”

    On Error Resume Next
    Range(“A1”).PasteSpecial

    Call Shell(“TaskKill /F /IM Acrobat.exe”, vbHide)

    End Sub

    Here, the Application path and pdf path need to be defined accurately. Then, when you run the code, you will find the copied items in the clipboard.
    Then, click on Paste all to extract values.
    null

  7. Hello Helen,
    You can do it without using the find and replace command. To do this follow the steps carefully.
    1. First, go to the Formula tab on the ribbon.
    2. Then, Select Show Formulas from the Formula Auditing section.
    null

    3. As a result, it will show you the formulas that you use in the workbook.
    null

    4. Now, copy the formulas and paste them into a notepad.
    null

    5. Then, press Ctrl+A to select all and after that, press Ctrl+C to copy.
    6. In a new workbook, paste it by pressing Ctrl +V. As a consequence, you will see the formulas are copied accurately without a reference workbook.
    null

  8. Hello Mark,
    We have sent you a mail. Can you see it and give us a response?

  9. Hello Craig,
    We are not dealing with SharePoint at this moment. So, I can’t give you a proper solution to this problem. In the future, if we work with SharePoint, we will get back to you and provide you with a useful solution.
    Thanks.

  10. Hello Chris, in option one, you need to insert the VBA code first. After that, you can apply the ColorFunction. Otherwise, you will face difficulties.
    If you don’t get the solution yet, you can send your worksheet. We will take a look closely and find the required solution.

  11. Hi Henrik, to highlight rows in Excel, you need to apply VBA code in that certain worksheet like this article. Otherwise, you need to run the code every time when you go to the next row and highlight it. This is a major disadvantage of this method. If you need to apply in all the worksheets, then you need to utilize the following code.

    Sub Highlights_Active_Row()
    Static xRow
    If xRow <> “” Then
    With Rows(xRow).Interior
    .ColorIndex = xlNone
    End With
    End If
    Active_Row = Selection.Row
    xRow = Active_Row
    With Rows(Active_Row).Interior
    .ColorIndex = 7
    .Pattern = xlSolid
    End With
    End Sub

    Note: Remember every time you go to the next row, you have to run the code every single time.

  12. Hi Amber, You can’t undo anything after applying the VBA code. This is one of the drawbacks of Excel.

  13. Hello Brow, yes you are right. It should be Row(A1) = HighlightActiveRow

  14. Hey Luna, did you solve the problem using the VBA code? If you face further problems, feel free to share them in the comment box. We are eager to help you to get the desired result.

  15. Hey Max, try the following VBA code

    Sub Changing_Date_Format_Using_Condition()
    Dim i As Integer

    i = InputBox(“Enter a Number”)

    If i = 2 Then
    Range(“A1”).NumberFormat = “mm/dd/yyyy”
    ElseIf i = 10 Then
    Range(“A2”).NumberFormat = “dddd-dd-mmm-yy”
    Else
    Range(“A3”).NumberFormat = “mmm-yy”
    End If

    End Sub

    Is this your required solution? If not then explain more about it. We will help you to get the result.

  16. Hey Stacy, You can read the following article to get just the date to display in the linked cell

    https://www.exceldemy.com/insert-drop-down-calendar-in-excel/#Step_4_Link_Drop_Down_Calendar_to_a_Cell_in_Excel

    Otherwise, you can go to the developer tab on the ribbon. Then, select Add-ins from the Add-ins group. In that Add-ins, add Mini Calendar and Date Picker. From there, you can add only the date in the linked cell.
    Try this solution, I think you will get your desired result. If you have more problems inform us.

  17. ⇒OFFSET($B$2,ROUNDUP(ROWS($1:1)/3,0)-1,MOD(ROWS($1:1)-1,3)) : In the OFFSET formula, you need to put the cell reference, rows and cols number.
    ⇒OFFSET($B$2………): Here $B$2 is the cell reference of the OFFSET function.

    ⇒OFFSET($B$2, ROUNDUP(ROWS($1:1)/3,0)-1……): Here, the ROUNDUP function gives the specific number of rows down. ROWS function provides the number of rows in a given array. Here, the cell reference is $1:1. So, the Rows function returns 1. As we have three rows in our dataset. So, divide the return value of the Rows function by 3. It will return 0.333. Then, the ROUNDUP value will round the number into the nearest whole number. The whole number of 0.333 is 1. After that, subtract 1 from it. So, the final value is 0 which is the required rows down.

    ⇒OFFSET($B$2,ROUNDUP(ROWS($1:1)/3,0)-1,MOD(ROWS($1:1)-1,3)): To extract the columns right, we utilize the MOD function. It gives a reminder after a number is divided by the divisor. First, the Rows function provides the number of rows from the given array. Here, it returns 1. Then, subtract 1 from the return value. Then, divide the value by the total number of columns. The MOD function will return the reminder. Here, it returns 0.
    So, for cell reference $B$2 and rows 0 down and cols 0 right, it returns Arif as our answer. Do the same for other cases.
    Try this solution I think you will get your desired result. If you face any more problems, inform us.

  18. To extract color information of conditional formatting cells, you need to follow the steps

    1. First, use the conditional formattings in your dataset.
    2. Then, click on the Dialog Box Launcher (Small tilted arrow beside Clipboard) from the Clipboard group.
    3. After that, click on Clear All from there.
    4. Copy the conditionally formatted range.
    5. Now, select a new cell where you want to paste your conditional formatting range color only.
    6. After that, click on Paste All from the Clipboard group.
    7. Now, remove the numbers and only remains the conditional formatting colors.
    8. Now, use =Background or =getLeftColor or =getRightColor just like this article, you will get your desired color information.

    Try this solution I think you will get your desired result. If you face any more problems, inform us.

ExcelDemy
Logo