User Posts: Joyanta Mitra
How to Plot Quadratic Line of Best Fit in Excel (2 Easy Methods)

Excel is a versatile tool that can be used to analyze data and create charts and graphs. One common use of Excel is to plot the best-fit line through a set of ...

Pop Up Excel VBA MsgBox When Cell Meets Criteria

Looking for ways to pop up Excel VBA MsgBox when cell meets criteria? Then, this is the right place for you. In Microsoft Excel, VBA (Visual Basic for ...

How to Use Excel VBA MsgBox Title (5 Examples)

VBA is a programming language used to automate tasks in Excel. When creating VBA programs, it's important to display messages to the user, and giving these ...

How to Create Radar Chart with Radial Lines in Excel

In this article, we are going to represent our data with a Radar Chart with Radial lines in Excel to compare our goal vs our achievement meaning overall ...

How to Create Fuel Cost Calculator Using Excel Formula

In this article, we are going to create a Fuel Cost Calculator using Excel formula. Here, we are going to give you a very easy way to find out the cost of your ...

How to Interpret Pareto Chart in Excel (2 Practical Examples)

In this article, we will show you how to interpret Pareto Chart in Excel. Pareto chart is used for finding out what sectors are major contributions to any ...

How to Limit Decimal Places in Excel (5 Easy Ways)

It is very necessary to limit decimal places in Excel to progress with further calculations. Sometimes it is irritating to have so many digits after the ...

How to Use Excel LARGE Function with Duplicates in Excel

The LARGE function cannot identify the unique nth largest value if duplicates are available in an array. In this article, you will find the nth largest value ...

How to Perform Bilinear Interpolation in Excel (with Easy Steps)

Bilinear interpolation is a mathematical process to estimate the function value of two variables when you do not know the function values of the input ...

Browsing All Comments By: Joyanta Mitra
  1. Code is alright. Please inform your particular problem.

  2. Dear Prince,
    Thank you very much for reading our article.
    According to your query, 1st you wanted to know about a formula to create a drop-down list that will be used to select different types of leaves. You will get that in Step 3 of this article. In our Excel file, we selected leave using the drop-down list in Record sheet. Also when you move to any month leave information will be based on the Record sheet. So, information will not move from one month to another. Try this and hope you will get the solution.
    Otherwise, send your Excel file with what you want to get and we will try to provide a solution. You can mail us at [email protected].
    Joyanta Mitra

  3. Dear Karlyn Martinez,
    For your convenience, I have showed the task with following steps.
    ● First, you have to recognize the pattern in the formula
    Showing pattern
    ● You can use Format Painter or drag the row to add a new row or rows for editing new data.

    Modifying data
    ● Now add new data.

    Adding new data
    ● Inset new rows in the Summary sheet.
    Inserting new rows in Summary sheet
    ● Insert the Entire row.
    Inserting new row
    ● Edit the code according to the main dataset. As now in Jan worksheet, new data is added, and so the range will be changed to AH$15 and $B$15.
    Showing the formula change

    Hope, this will be helpful for you.

    Joyanta Mitra
    Excel & VBA Content Developer

  4. This code will solve your problem. The code has a condition checking values blank or not.

    Sub Duplicates_Dif_Colors()
    Dim RG As Range
    Dim TT As String
    Dim CL As Range
    Dim CR As String
    Dim CP As Range
    Dim CD As Long
    Dim Cltn As Collection
    Dim J As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
    TT = ActiveWindow.RangeSelection.AddressLocal
    TT = ActiveSheet.UsedRange.AddressLocal
    End If
    Set RG = Application.InputBox("Select range of data with duplicates:", "Duplicate values with Colors", TT, , , , , 8)
    If RG Is Nothing Then Exit Sub
    CD = 2
    Set Cltn = New Collection
    For Each CL In RG
    If CL.Value <> "" Then ' check if cell is not blank
    On Error Resume Next
    Cltn.Add CL, CL.Text
    If Err.Number = 457 Then
    CD = CD + 1
    Set CP = Cltn(CL.Text)
    If CP.Interior.ColorIndex = xlNone Then CP.Interior.ColorIndex = CD
    CL.Interior.ColorIndex = CP.Interior.ColorIndex
    ElseIf Err.Number = 9 Then
    MsgBox "Found excessive duplicates", vbCritical, "Duplicates with Colors"
    Exit Sub
    End If
    On Error GoTo 0
    End If
    End Sub


    Blank cells not colored

  5. Please write Ticker correctly both in your xlsx file and the code. Do not try to give invalid ticker or outdated ticker. Provide problems in detail for better service please.

  6. First, you have to create different power queries for each worksheet for other tickers and copy the same code. Otherwise, it triggers the first. Then for 10 different tickers,the code is

        TickerList = {"AAPL", "GOOG", "MSFT", "AMZN", "FB", "TSLA", "NVDA", "JPM", "BAC", "V"},
        StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content]{0}[Column1],
        EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content]{0}[Column1],
        #"Loop Through Tickers" = List.Transform(TickerList, each
                Ticker = _,
                Source = Csv.Document(Web.Contents(""&Ticker&"?period1"&StartDate&"&period2"&EndDate),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
                #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
                #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
                #"Changed Type"
        #"Loop Through Tickers1" = #"Loop Through Tickers"{8}
        #"Loop Through Tickers1"

    this code creates 10 tables for 10 different tickers. You have to select one table according to Ticker and hover over the query in Workbook Query Section and press View in Worksheet. You will get the table have to create 10 sheets separately.

Advanced Excel Exercises with Solutions PDF