Bishawajit Chakraborty, Author at ExcelDemy

User Posts: Bishawajit Chakraborty

How to Make an Excel Metadata Extractor (with Easy Steps)

When you develop, download, or upload a Microsoft Excel workbook, it may contain information that you do not want to make public online. The formal name for ...

Bishawajit Chakraborty Jan 18, 2023

READ MORE +

How to Find Largest Lookup Value in Excel (3 Easy Ways)

In this article, we will demonstrate 3 handy ways to find the largest lookup value using the combination of the VLOOKUP function with other functions in Excel. ...

Bishawajit Chakraborty Jan 16, 2023

READ MORE +

How to Use IF Statement with Yes or No in Excel (3 Examples)

Using this function, it is possible to analyze text, values, and even errors. It extends beyond simply determining whether two things are equal and then giving ...

Bishawajit Chakraborty Jan 22, 2023

READ MORE +

Refresh All Data Connections and Pivot Tables with Excel VBA

After you create a pivot table, you must use the change data source command to update or refresh the data in it. However, it is time-consuming and inconvenient ...

Bishawajit Chakraborty Jan 9, 2023

READ MORE +

How to Create a Bar Chart with Standard Deviation in Excel

If all the data were flawless, absolute, and comprehensive, that would be good. Excel, however, provides us with some helpful tools to communicate margins of ...

Bishawajit Chakraborty Jan 9, 2023

READ MORE +

How to Use DVAR Function in Excel (2 Suitable Examples)

You can employ the Excel database functions to help you when using an Excel dataset. Sometimes, you may need to find the variance due to some specific ...

Bishawajit Chakraborty Jan 5, 2023

READ MORE +

How to Calculate Tracking Error in Excel (with Detailed Steps)

Tracking error is one of the most crucial metrics for evaluating a portfolio's performance and a portfolio manager's potential for outperforming the market or ...

Bishawajit Chakraborty Jan 8, 2023

READ MORE +

How to Add SmartArt Graphics in Excel (7 Suitable Examples)

SmartArt mixes graphic and textual content to give information a more aesthetically pleasing presentation. You can make fantastic SmartArt with the help of ...

Bishawajit Chakraborty Jan 1, 2023

READ MORE +

How to Use IMSUB Function in Excel (2 Suitable Examples)

An imaginary coefficient with a real coefficient makes up a complex number. Real and imaginary coefficients can be transformed into complex numbers of the type ...

Bishawajit Chakraborty Jan 3, 2023

READ MORE +

How to Set Intercept Trendline in Excel (with Easy Steps)

Excel charts are a fantastic tool for displaying data. Excel charts can easily represent complex data and provide users with useful information. The majority ...

Bishawajit Chakraborty Jan 2, 2023

READ MORE +

How to Make Textbox Datepicker with Excel VBA (with Easy Steps)

Users frequently want to click a button and choose a date. The same holds true for Microsoft Excel programmers. Making use of InputBox and MessageBox while ...

Bishawajit Chakraborty Dec 22, 2022

READ MORE +

How to Generate or List All Possible Permutations in Excel

When the order of the arrangements is concerned, a permutation is a mathematical technique that establishes the total number of possible arrangements in a ...

Bishawajit Chakraborty Dec 14, 2022

READ MORE +

How to Do Web Scraping Without Browser with Excel VBA

Definitely, one of the most popular software programs utilized globally and in many fields is Microsoft Excel. Users can access web pages and extract data from ...

Bishawajit Chakraborty Dec 12, 2022

READ MORE +

How to Encode Data in Excel (with Easy Steps)

You have come to the right place if you're looking for instructions on how to encode data in Excel. Despite this being a necessity, there is currently no ...

Bishawajit Chakraborty Dec 15, 2022

READ MORE +

How to Remove Highlighted Rows in Excel (2 Easy Ways)

When working with large data sets in Microsoft Excel, you may need to remove specific rows from the worksheet because of security purposes. This article will ...

Bishawajit Chakraborty Dec 14, 2022

READ MORE +

Browsing All Comments By: Bishawajit Chakraborty

Reply
Bishawajit Chakraborty Nov 16, 2022 at 11:28 AM

Thank you so much Sharath for your response. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Regards,

Bishawajit Chakraborty.
Reply
Bishawajit Chakraborty Jan 17, 2023 at 4:31 PM

Thank you BRIAN for your wonderful question

If you want to import stock prices on a weekly basis, you can visit this website link, from which we scraped our live data in accordance with this article.

Then, click on the Frequency option and select Weekly for your desired frequency.

Finally, apply the rest procedures mentioned in the article. I hope this may solve your issue.

Bishawajit, on behalf of ExcelDemy
Reply
Bishawajit Chakraborty Jan 17, 2023 at 4:18 PM

Thank you, Fred, for your wonderful question.

Firstly, go to the Home tab.

You can keep the decimal places of any number at any place by using the Increase Decimal and the Decrease Decimal icons here.

Bishawajit, on behalf of ExcelDemy

Bishawajit Chakraborty Jan 17, 2023 at 4:13 PM

Thank you, SALWA for your wonderful question.

First off, you cannot change the time for a scheduled email; however, you can add the remaining date in the email using the VBA code. When you apply the VBA code, the emails will pop up for sending. And on the Outlook email section, you can customize emails with scheduled time

Public Sub SendEmail02()
    Dim Date_Range As Range
    Dim Mail_Recipient As Range
    Dim Email_Text As Range
    Dim Outlook_App_Create As Object
    Dim Mail_Item As Object
    Dim Last_Row As Long
    Dim VB_CR_LF, Email_Body, Date_Range_Value, Send_Value, Subject As String
    Dim i As Long
    On Error Resume Next
    Set Date_Range = Application.InputBox("Please choose the date range:", "Message Box", , , , , , 8)
    If Date_Range Is Nothing Then Exit Sub
    Set Mail_Recipient = Application.InputBox("Please select the Email addresses:", "Message Box", , , , , , 8)
    If Mail_Recipient Is Nothing Then Exit Sub
    Set Email_Text = Application.InputBox("Select the Email Text:", "Message Box", , , , , , 8)
    If Email_Text Is Nothing Then Exit Sub
    Last_Row = Date_Range.Rows.Count
    Set Date_Range = Date_Range(1)
    Set Mail_Recipient = Mail_Recipient(1)
    Set Email_Text = Email_Text(1)
    Set Outlook_App_Create = CreateObject("Outlook.Application")
    For i = 1 To Last_Row
        Date_Range_Value = ""
        Date_Range_Value = Date_Range.Offset(i - 1).Value
        If Date_Range_Value <> "" Then
        If CDate(Date_Range_Value) - Date <= 7 And CDate(Date_Range_Value) - Date > 0 Then
            Send_Value = Mail_Recipient.Offset(i - 1).Value
            Subject = Email_Text.Offset(i - 1).Value & " on " & Date_Range_Value
            VB_CR_LF = "<br><br>"
            Email_Body = "<HTML><BODY>"
            Email_Body = Email_Body & "Dear " & Send_Value & VB_CR_LF
            Email_Body = Email_Body & "Text : " & Email_Text.Offset(i - 1).Value & VB_CR_LF
            Email_Body = Email_Body & "</BODY></HTML>"
            Set Mail_Item = Outlook_App_Create.CreateItem(0)
            With Mail_Item
                .Subject = Subject
                .To = Send_Value
                .HTMLBody = Email_Body
                .Display
            End With
            Set Mail_Item = Nothing
        End If
    End If
    Next
    Set Outlook_App_Create = Nothing
End Sub

Then, using your Outlook account, you can set it up for a scheduled email. I hope this may solve your issue.

Bishawajit, on behalf of ExcelDemy

Reply
Bishawajit Chakraborty Dec 21, 2022 at 4:34 PM

Thank you, JOHN SCURAS, for your comment.

You can use the below formula to determine your commission of the model.

=IF(H36>C33,SUMPRODUCT(–(H36>$C$33:$C$56),(H36-$C$33:$C$56),$D$33:$D$56)+C33*E33,C33*E33)

Here, H36 is the sales amount, C33 is your first Deal Size Range (High), C33:C56 is all values for Deal Size Range (High) column, D33:D56 is all values from the % incremental commission column, and E33 is the first value of the incremental commission (5%).

Best Regards,
Bishwajit
Team ExcelDemy
Reply
Bishawajit Chakraborty Dec 18, 2022 at 10:56 AM
Thank you so much JOHN JOYCE for your comment. To set all the columns into text, you can follow the below steps accordingly.
- Firstly, click on the General option from the Home tab.
- Then, choose the Text option after selecting your entire data set. After that, you will get your desired result.
After changing the format save the file and re-open it to check whether the data shows in the proper format.

Best Regards,
Bishwajit
Team ExcelDemy
Reply
Bishawajit Chakraborty Dec 18, 2022 at 12:21 AM

Greetings AND74,
Thanks for noticing the error.
It will be OR(C5QUARTILE($C$5:$C$14,3)+1.5*$D$16) instead of OR(C5QUARTILE($C$5:$C$14,1)+1.5*$D$16).
We will make the corrections shortly.

Best Regards,
Bishwajit
Team ExcelDemy
Reply
Bishawajit Chakraborty Dec 17, 2022 at 11:51 PM

Thank you, Edward Vinieratos, for your comment. According to your formula, your data seems quite large. It is hard to identify where the error lies. Can you please kindly share your excel file with us? We will create another Excel file with your desired result. We will reply to you back as soon as possible. Email Address: [email protected].

Bishawajit Chakraborty Dec 12, 2022 at 2:37 PM

Thank you Edijs for your wonderful question. To edit the cell range follow these steps:

Firstly, enable the Microsoft Forms 2.0.

Secondly, type this code on to sheet 2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("D5:D10"), Target) Is Nothing Then
    Dim xDataObj As New MSForms.DataObject
    Dim rowNumber As Integer
    rowNumber = Split(Target.Address, "$")(2)
    xDataObj.SetText rowNumber
    xDataObj.PutInClipboard
        If IsNumeric(Target.Value) And Target.Value > 700 Then
            Call Send_Email_Condition_Cell_Value_Change
        End If
    End If
End Sub

Thirdly, type change the code from Module 1.

Sub Send_Email_Condition_Cell_Value_Change()
    Dim pApp As Object
    Dim pMail As Object
    Dim pBody As String
    Dim xDataObj As New MSForms.DataObject
    xDataObj.GetFromClipboard
    Set pApp = CreateObject("Outlook.Application")
    Set pMail = pApp.CreateItem(0)
    pBody = "Hello, " & Cells(xDataObj.GetText, 2).Value & vbNewLine & _
              "You've Payment Due." & vbNewLine & _
              "Please Pay it to avoid extra fees."
    On Error Resume Next
    With pMail
        .To = Cells(xDataObj.GetText, 3).Value
        .CC = ""
        .BCC = ""
        .Subject = "Request For Payment"
        .Body = pBody
        .Display  'We can use .Send to Send the Email
    End With
    On Error GoTo 0
    Set pMail = Nothing
    Set pApp = Nothing
End Sub

This should solve your problem. You can see the output from the following animated image.

If you have any further question, please let us know.

Regards

Bishawajit, on behalf of ExcelDemy

Bishawajit Chakraborty Dec 12, 2022 at 2:36 PM

Thank you, Brian for the comment. You can use the following code to insert timestamp when value from a specific row change:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellRow, TimeRow, Row, Col As Integer
Dim DpRng, Rng As Range
Dim Timestamp As Variant
CellRow = 8
TimeRow = 9
Row = Target.Row
Col = Target.Column
If Row <= 7 Then Exit Sub
Timestamp = Format(Now(), "dd-mm-yy hh:mm:ss AM/PM")
If Target.Text <> "" Then
If Row = CellRow Then
Cells(TimeRow, Col) = Timestamp
Else
On Error Resume Next
Set DpRng = Target.Dependents
For Each Rng In DpRng
If Rng.Row = CellRow Then
Cells(Rng.Row, TimeRow) = Timestamp
End If
Next
End If
End If
End Sub

The following shows it working.

Now, for your second part of the comment. You can use this code for multiple rows:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellRow, CellRow1, TimeRow, Row, Col As Integer
Dim DpRng, Rng As Range
Dim Timestamp As Variant
CellRow1 = 7
CellRow = 8
TimeRow = 9
Row = Target.Row
Col = Target.Column
If Row <= 6 And Row >= 9 Then Exit Sub
Timestamp = Format(Now(), "dd-mm-yy hh:mm:ss AM/PM")
If Target.Text <> "" Then
If Row = CellRow1 Then
Cells(TimeRow, Col) = Timestamp
ElseIf Row = CellRow Then
Cells(TimeRow, Col) = Timestamp
Else
On Error Resume Next
Set DpRng = Target.Dependents
For Each Rng In DpRng
If Rng.Row = CellRow Then
Cells(Rng.Row, TimeRow) = Timestamp
End If
Next
End If
End If
End Sub

The animated image shows it is working for two rows.

Regards

Bishawajit, on behalf of ExcelDemy

Reply
Bishawajit Chakraborty Dec 12, 2022 at 2:34 PM
Thank you Prachi Davade for your wonderful question. You can change the
```
Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
```
to
```
Set XDueDate = Range("D5:D7")
```
This will do what you desire. If you have further queries, let us know.

Regards

Bishawajit, on behalf of ExcelDemy
Reply
Bishawajit Chakraborty Nov 23, 2022 at 9:57 AM

Thank you, Madan, for your comment. In our Excel file, this formula is working. Can you please share your excel file with us? Email Address: [email protected]. We will try to solve your problem as soon as possible.
Reply
Bishawajit Chakraborty Nov 13, 2022 at 1:07 PM

Thank you so much, Peter, for your comment.
You will find your solution in the below steps. Please follow the steps accordingly.

Steps:
• Firstly, go to the Data tab.
• And, select the group option.

• Now, you will see here the plus (+) sign.
• Here, we have grouped the 5th, 6th, and 7th rows.
• Then, go to the Review tab.
• And, select the Allow Edit Ranges option.
• After that, choose the ranges.
• And, type the password and click OK.

• Retype the password and click OK.

• Now remove this window.

• Firstly, go to the Review tab.
• Secondly, select the Protect Sheet option.

• Now, write your password.
• And, click OK.

• Then, retype your password and click OK.

• So, if you click on the plus (+) sign, it will show you the given result you want.
Reply
Bishawajit Chakraborty Oct 20, 2022 at 1:50 PM

Hello Rash,
Thank you for your comment. If you want to send a table within the email body for any range, then you have to create a table in any worksheet. For this, you will follow our Method No.3 where we have used a VBA code to send an active sheet within the email body. Firstly, you can create a table in this active sheet and send this sheet with your table within the email body. Can you please share your Excel file with us? We will customize the file according to your requirements. Email address [email protected].

Regards,
Bishawajit Chakraborty (Exceldemy Team)
Reply
Bishawajit Chakraborty Oct 19, 2022 at 3:13 PM

Thank you, Dhroov, for your comment. In our Excel file, this formula is working. Can you please share your excel file with us? Email Address: [email protected]. We will try to solve your problem as soon as possible.