User Posts: Tanjim Reza
0
Excel VBA ComboBox Value (Add, Remove or Set Default Value)
0

When working with Excel, ComboBox is a great tool to select and process data. You can easily select values from given ComboBox values. Now, it is a dire need ...

0
Delta Neutral Strategy in Excel (2 Practical Examples)
0

When evaluating assets or stock options, we have to think dynamically as we can take many options, as well as the prices, can change at any time. In this ...

0
How to Calculate Annualized Volatility in Excel (with Quick Steps)
0

When investing in a business or stock market, we need to know how much take we risk or how much profit we can make through this asset. In this regard, the ...

0
How to Perform Union Query in Excel (with Detailed Steps)
0

When working with excel, sometimes we get multiple tables with one common column or row. As a result, it is needed sometimes to merge the tables for better ...

0
How to Use PERMUT Function in Excel (with an Alternative)
0

Excel is extremely useful to perform extensive calculations and solve advanced mathematical problems. Now, in mathematics or real life, sometimes we need to ...

0
Mortgage Break-Even Analysis in Excel (with Easy Steps)
0

Break-even analysis is one of the most effective tools to know when an institute or a person has covered all costs or loans. When it comes to mortgages, the ...

0
How to Calculate Confidence Interval for Slope in Excel
0

We can perform many extensive statistical calculations with Excel easily. The confidence interval is one of the most important calculations in statistics. This ...

0
Excel VBA: How to Divide Without Remainder (2 Easy Ways)
0

The division is one of the most basic and frequent arithmetic operations in our daily life. At times, we need only the integer part from a division result ...

0
How to Calculate Sigma Level in Excel (2 Suitable Ways)
0

When thinking of industries, we are required to calculate our product variability, total defects, defect percentages, and so on. These calculations are a ...

0
How to Create a Drop Down Checklist in Excel (with Quick Steps)
0

Excel is a fantastic tool for working with extensive data and user inputs. When working with user inputs, it is a frequent scenario that the users are needed ...

0
How to Add Text Suffix with Custom Format in Excel (5 Examples)
0

When working with Excel, we come across numerous types of data. Now, for large calculations, it would be easier to understand if we could get the units with ...

0
How to Remove HTML Tags from Text in Excel (2 Suitable Ways)
0

HTML tags are pretty common when working with raw data. Generally, when we copy data from somewhere else, the code inside the data gets copied too. But, as ...

0
How to Convert Hex to Decimal in Excel (2 Practical Examples)
0

When working with numbering systems, Hexadecimal and Decimal both systems are significant from their own perspectives. So, it is a frequent need for us to ...

0
How to Update Data Model in Excel (2 Suitable Ways)
0

When working with an interlinked, large, and complex dataset in Excel, creating data models is a great approach to solving multiple calculations easily. Now, ...

0
How to Mail Merge from Excel to Powerpoint (with Quick Steps)
0

Excel is a fantastic tool to work with when it comes to data analysis and interpretation. But, when it comes to presenting the data in a glamorous way, ...

Browsing All Comments By: Tanjim Reza
  1. Hello, HMD!
    Thank you for your query. As far as I have understood your query, you want to change the copied values later and thus save it. Now, it is simple. You can do this normally. And, the values will be changed and saved automatically. But, to make this more effective and dynamic, you might need to have a confirmation window for this. To accomplish this, follow the steps below.

    Steps:

    • Go to your desired sheet first where you want to accomplish this.
    • Following, right click on your sheet name and choose the option View Code from the context menu.
    • View Code Option

    • Afterward, insert the following code in the code window and save the Excel file as .xlsm file.
    • VBA Code
      Code:
      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim KeyCells As Range
      ' The variable KeyCells contains the cells that will
      ' cause an alert when they are changed.
      Set KeyCells = Range("A1:C10")
      If Not Application.Intersect(KeyCells, Range(Target.Address)) _
      Is Nothing Then
      ' Display a message when one of the designated cells has been
      ' changed.
      ' Place your code here.
      MsgBox "Cell " & Target.Address & " has changed."
      End If
      End Sub

      This code is taken from https://learn.microsoft.com/en-us/office/troubleshoot/excel/run-macro-cells-change

    Now, if you insert any new value or remove any value from the cells A1:C10, there will be a Microsoft Excel window informing you about a change in the values. You can change this range inside the code as per your requirements.
    Microsoft Excel Window

    I hope this accomplishes your desired result.

    Regards,
    Tanjim Reza

  2. Hello, AHMED!
    Thank you for your query. Regarding your query, you can use an array formula to accomplish this result.

    • To accomplish this, first, press Ctrl + Shift + Down arrow to select all required cells in a column.
    • Following put an equal sign (=) and insert any of the formulas that are given in this article.
    • Subsequently, press Ctrl + Enter.
    • Thus, all the cell values will be copied to all the desired cells in an instant.

    I hope this solves your problem.

    Regards,
    Tanjim Reza

  3. Hello, KASHIF!
    Thank you for your query. Regarding your query, if you want to return value for meeting date condition only, you can use the INDEX-MATCH combination with the following formula.

    =IFERROR(INDEX($E$5:$E$13,MATCH(H5,$D$5:$D$13,0)),"Not Available")

    If you want to meet the Product criteria and also the Date criteria, you can use the formula below.

    =IFERROR(INDEX($E$5:$E$13,MATCH(1,(($B$5:$B$13=G5)*($D$5:$D$13=H5)),0)),"Not Available")

    I hope your problem is resolved now.

    Regards,
    Tanjim Reza

  4. Hello, CRYSTAL!

    Thank you for your query.
    As far as I understood your question, you want to apply date criteria and multiple Category criteria in your formula to find the average of a different column.
    You can accomplish this using the nested FILTER function along with the AVERAGE, EOMONTH, and DAY functions and you have to use the plus sign between the categories to enable the OR criteria. The final formula would look like this.

    =AVERAGE((FILTER('ONLINE MASTER'!$D$2:$D$1000,(('ONLINE MASTER'!$F$2:$F$1000="COMPUTER")+('ONLINE MASTER'!$F$2:$F$1000="TELEPHONE"))*('ONLINE MASTER'!$A$2:$A$1000>=('2022'!$A63-DAY('2022'!$A63)+1))*('ONLINE MASTER'!$A$2:$A$1000<=EOMONTH(('2022'!$A63-DAY('2022'!$A63)+1),0)))))

    I hope this solves your problem.

    With Regards,
    Md. Tanjim Reza Tanim

  5. Hey, YIANNIS ZOUGANELIS!
    Thank you for your query. Hope you are doing well. You have asked some thoughtful questions. I am answering all your queries one by one below.

    Q1: First of all, you have asked if it is possible to have the ENTER NEW DATA button on a different worksheet.
    Yes, this is very much possible. In this regard, you will have to follow the same procedures of the article to create the forms and buttons for everything in the worksheet just where you want the button to appear.

    Q2: Second, you want the selected worksheet not to become active. In this regard, you have to change the code a little bit. Say, you have set the button in the MainSheet worksheet. Now, you want to be active in this sheet all along. You don’t want to activate any other selected worksheet.
    In this regard, create the button and form in the MainSheet worksheet and then write the code below inside the Code window of the Command_Button1.
    Code:
    Private Sub CommandButton1_Click()
    TargetSheet = ListBox1.Value
    If TargetSheet = "" Then
    Exit Sub
    End If
    Worksheets(TargetSheet).Activate
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.Cells(lastRow + 1, 2).Value = TextBox1.Value
    ActiveSheet.Cells(lastRow + 1, 3).Value = TextBox2.Value
    ActiveSheet.Cells(lastRow + 1, 4).Value = TextBox3.Value
    ActiveSheet.Cells(lastRow + 1, 5).Value = TextBox4.Value
    Worksheets("MainSheet").Activate
    End Sub

    Q3: Thirdly, you want to search for present values rather than entering values. This is a different thing. Say, you are given the same dataset as per the article. Now, you want to enter only the customer’s name and want to get the contact address, age, and gender. Go through the steps below to achieve this.

    Steps:

    • First, you will need to create a new user form for this.
    • To create a new user form, go to the Developer tab >> Insert tool >> Button (Form Control option) from the Form Controls group.
      Insert Form Control Button
    • As a result, a button would appear.
    • Now, name the button as you like (I have named SEARCH for DATA)and right-click on the button.
    • Following, choose the Assign Macro… option from the context menu.
      Assign Macro to the Button
    • Thus, the Assign Macro window would appear.
    • Here, choose a Macro name as you wish and click on the New button.
      Macro Window
    • Afterward, a new module would appear in the VB Editor.
    • Following, write the following code in the code window.
      Sub Search_Data() UserForm1.Show End Sub
      Code for Form Button
    • Now, you need to create UserForm1.
    • To do this, go to the Insert tab inside the VB Editor and choose the UserForm option.
      Insert UserForm
    • Consequently, the Toolbox window would appear.
    • Now, choose the option Label from the window and drag it inside the form area to create a label.
      Create a Label
    • After dragging the label, name it.
    • Following, choose the TextBox option from the Toolbox window and drag it inside the form area.
      Add a Text Box
    • Following, name the text box and repeat the previous procedures to create another label.
      Add another Label
    • Continue to repeat these procedures to create all labels, textboxes, and the search button inside the form.
      UserForm1
    • Now, right-click on your Search button and choose the option View Code from the context menu.
      View Code of the Search Button
    • Afterward, name the Command Button as SearchButton and write the following code in the VB Editor.
      Code:
      Sub SearchButton_Click()
      Dim sh As Worksheet
      Set sh = ThisWorkbook.Sheets("Search")
      Dim lr As Long
      lr = sh.Range("B" & Rows.Count).End(xlUp).Row
      Dim i As Long
      If Application.WorksheetFunction.CountIf(sh.Range("B:E"), Me.TextBox1.Text) = 0 Then
      MsgBox "No match found!", vbOKOnly + vbInformation
      Call Reset
      Exit Sub
      End If
      For i = 2 To lr
      If sh.Cells(i, "B").Value = Me.TextBox1.Text Then
      TextBox1 = sh.Cells(i, "B").Value
      TextBox2 = sh.Cells(i, "C").Value
      TextBox3 = sh.Cells(i, "D").Value
      TextBox4 = sh.Cells(i, "E").Value
      End If
      Next i
      End Sub
      Function Reset()
      TextBox1.Value = ""
      TextBox2.Value = ""
      TextBox3.Value = ""
      TextBox4.Value = ""
      End Function
    • Afterward, save the Excel file as .xlsm file to enable the macro.
    • Now, click on your first created button SEARCH for DATA. Thus, the user form will appear.
      SEARCH for DATA Button
    • Now, say, you want to find data for Craig Arvin.
    • Insert the name in the first textbox. And, click on the Search button.
      Search an Entry

    Finally, you will be able to get your desired automated search result.
    Search Result

    Regards,
    Tanjim Reza

  6. Hello, PJ!
    Hope you are doing well. Thank you for your query.
    Cooling time mainly refers to the time elapsed from the end of holding pressure to the opening of the mold. This value depends on various factors like the wall thickness, product shape, mold temperature, melt properties, etc. To get a better product, the cooling time should be as low as possible.
    You can search google thoroughly or visit industrial yards to learn about the actual cooling time values.

    Regards,
    Md. Tanjim Reza Tanim

  7. Hello, NICK!
    Hope you are doing well. Thank you for your query.
    As we have used different functions in the two methods, the answer varied a little bit, it’s true. But as you can see, this variation is minimal. The final answer is almost equal in both methods. Moreover, the little variation is after the decimal part in a percentage format. So you can neglect this small difference most of the time to calculate the following approximate result.
    But if you need a 100% accurate answer, I would suggest you use the manual method.

    Regards,
    Tanjim Reza

  8. Hello, DJ!
    Thank you for your query.
    To analyze your problem, I would say it would be impossible to automate the sheet names completely without saving the file as an xlsm file.
    As far as I understood you want to create as many sheets as you want and then extract values from them in other sheets automatically whenever you want. In this regard, you want to address the sheets automatically without writing their names in the formulas.
    Now, In this process, VBA codes are the most appropriate to use. Without VBA codes, you have another way to do this by using the GET.WORKBOOK() function. But, you will need to save the file as an xlsm file even in this process. But, as your VBA is locked down, I am afraid you would not be able to work with xlsm file.
    So, I would say this would be quite impossible to solve your problem with VBA locked down.

    Regards,
    Tanjim Reza

  9. Hello, ADIL M!
    Thank you for your appreciation and query.
    Regarding your query, I have thought of two cases.
    Case 1: Stacking all data in a single column but different rows with your desired order (A1, A2, B1, B2, and so on).
    Case 2: Combining data of columns into separate cells, i.e. A1:A4 range in cell D1, B1:B4 range in cell D2, C1:C4 range in cell D3, and so on.

    Download Link:
    The file is attached here for you.
    Solution.xlsx

    ⊕ 1st Case: Stacking All Data in a Single Column
    1. Use the following formula in cell G5.
    =INDEX(Data,(MOD(ROW(A1)-1,3)+1),ROUNDUP(ROW(A1)/3,0))
    Formula with INDEX Function
    2. Following, drag the fill handle downward to accomplish your full result.
    Full result with INDEX Function
    Now, this formula may look like a complex one. So, for your better understanding, I am breaking down the formula below.

    Formula Breakdown:
    >> MOD(ROW(A1)-1,3)

    This will give you the argument of the row number in the INDEX function. If you drag the fill handle it would give you the next row numbers as per your data range.
    Result: 1,2,3,1,2,3,1,2,3,1,2,3 (As our range has 3 rows and 4 columns)

    >> ROUNDUP(ROW(A1)/3,0)

    This part will give you the sequence of column numbers for your data range. If you drag the fill handle below you would get all the column numbers individually for all the values of your range.
    Result: 1,1,1,2,2,2,3,3,3,4,4,4

    >> INDEX(Data,(MOD(ROW(A1)-1,3)+1),ROUNDUP(ROW(A1)/3,0))

    This will take your lookup array and generate the values individually from the lookup as per the row numbers and column numbers that you have got in the upper processes. So after dragging the fill handle below, you will get the whole data range in a single column in your desired format.
    Result: 1, Andy, 6-Jan, 2, Miller, 8-Apr, 3, Keith, 12-Jun, 4, Robert, 19-Aug

    ⊕ 2nd Case: Combining Data of Columns into Separate Cells
    You can also use the TEXTJOIN function in this regard.
    1. Insert the following formula in cell E5 now.
    =TEXTJOIN(" ",TRUE,INDEX($B$5:$E$9,ROW($A$1),ROW(A1)):INDEX($B$5:$E$9,ROW($A$5),ROW(A1)))
    TEXTJOIN Function to Get Desired Result
    Note that the TEXTJOIN function can join an array of text. We have used the INDEX function as cell reference in the formula, to specify the array.
    2. Following, drag the fill handle below to get all results.
    All Results with TEXTJOIN Function

    Formula Breakdown:
    >> INDEX($B$5:$E$9,ROW($A$1),ROW(A1))

    This will return you the first row and first column data from your array. Here, you must reference your row_num argument with absolute reference.
    Result: 26. 26 is situated in cell B5.

    >> INDEX($B$5:$E$9,ROW($A$5),ROW(A1))

    This will return you the last row of the following column data of your array.
    Result: 18. 18 is situated in cell B9.

    >> INDEX($B$5:$E$9,ROW($A$1),ROW(A1)):INDEX($B$5:$E$9,ROW($A$5),ROW(A1))
    This refers to array B5:B9. To get this clearly, evaluate the formula from the Formulas tab.

    >> TEXTJOIN(” “,TRUE,INDEX($B$5:$E$9,ROW($A$1),ROW(A1)):INDEX($B$5:$E$9,ROW($A$5),ROW(A1)))

    This will join the texts from the first row to the last row of the first column of data with a space between each row of data.
    Result: 26 44 15 25 18

    Regards,
    Tanjim Reza

  10. Hello, John!
    I hope you are doing well.
    About your problem, I think your windows list separator is set to semicolon instead of comma. In this regard, follow the steps below to solve your problem.
    1. Go to your Windows menu and search for the Run window.
    2. Afterward, open the Run window.
    Open Run Window
    3. Now, write intl.cpl in the Open: text box.
    4. Subsequently, click on the OK button.
    Run Window
    5. As a result, the Region window will appear.
    6. Following, click on the Additional settings… button.
    Region Window
    7. Consequently, the Customize Format window will appear. Here, you would see the List separator: option is selected as semicolon (;).
    Customize Format Window
    8. Following, change the List separator: option to comma (,) >> click on the Apply button >> click on the OK button.
    Customize Format Window
    9. Now close your Excel app and reopen the file. The formula will work fine.
    Thus, I hope your problem would be solved now. If it doesn’t, please let us know in the comment section again.

    Thanks,
    Tanjim Reza

  11. Hi Jay!
    Thank you for your query!
    Our formula here is correct.
    Because, if you use =IF(J3<>"",NOW(),"") in Cell K3, and copy it down column K, whenever you write something in column J, the NOW function will return the same timestamp in all cells of column K (K3, K4, K5,….). You will see no difference in the timestamps and will lose the original entry time.
    To fix this for each entry, you have to type =IF(J3<>"",IF(K3="",NOW(),K3),"") in Cell K3, and copy it down the cells of column K. (You have to copy the formula first, even though there is no input in column J yet.)
    This formula is designed in such a way that the NOW function will not return the current time when there is already a timestamp recorded. Look closely, IF(K3="",NOW(),K3) part ensures that the IF function will return what is already in column K cells if there is any priorly.
    Look at the following GIF image for more clarification.

    Regards,
    Tanjim Reza

  12. Hi, MICHAEL V BERNOT!

    Thank you for your query.
    You have pointed out a good problem. To solve your problem remove the last bracket from the given Custom column formula in the article above.
    And, another thing, choose the All Rows option in the Operation option list instead of Count Rows in the Group By window.
    Grop By Window

    I hope this solves your problem. Stay with ExcelDemy for more Excel tips, tricks, formulas, and solutions.

    Regards,
    Tanjim Reza

  13. Hi, Megan!
    Thank you for your query.
    You can definitely track change for multiple columns. If you want to track change for the A and B columns for row 5, then you can apply the following formula.
    =IF(AND(A5="",B5=""),"",TODAY())
    Then you can use the fill handle feature for all the other rows.

    Regards,
    Tanjim Reza

  14. Hi, Bill!
    Thank you for your query.
    Regarding your query, just make a dataset table and use the SUM function or the addition functionality of Excel. In our article, we have used the subtraction functionality; in your requirements, you just need to utilize the addition functionality of Excel.

    Regards,
    Tanjim Reza

  15. Hi, GILBERT BECHTOL!
    Thank you for your query.
    In your appeared problem, I would suggest you use the MONTH function to get individual months from each date record. Then, sort the order from smallest to largest. As a result, you’ll get the billing months of the project in sequential order and thus you can use the INDEX function to achieve your target.
    If your problem still doesn’t fix, please send us your Excel sheet with clearer feedback on your target in this regard.

    Regards,
    Tanjim Reza

  16. Hi, BIJAN!
    Thank you for your query.
    You can achieve your desired result following the workflow below:

    • First, sort the numbers. To do this, select the columns individually >> go to the Home tab >> Editing group >> Sort & Filter tool >> Sort Smallest to Largest option.
    • Sort the Columns

    • After sorting both columns, select the columns >> go to the Home tab >> Conditional Formatting tool >> Highlight Cells Rules option >> Duplicate Values… option.
    • Apply Conditional Formatting

    • Now, the Duplicate Values window will appear. Choose your desired format and click on the OK button.
    • Format Duplicate Cells

    • Then you will get the duplicate values highlighted.
    • Formatted Duplicate Cells

    • Now, hold the Ctrl key >> select the duplicate cells >> press the Delete key.
    • Delete Duplicated Cells

    I hope, it helps.

    Regards,
    Tanjim Reza

  17. Hi, KRISHNA!
    Thank you for your query.
    About your query, please check if you have put an equal sign (=) in the cell before inserting the formula. Because, on my end, your formula looks perfect. And, I checked it in my Excel sheet and it worked! If it still doesn’t work, please send us your excel file.

    Regards,
    Tanjim Reza

  18. Hi, JAMAL!
    You have pointed out the correct thing in response to BHANU’s problem.
    Thank you for your valuable feedback!

    Regards,
    Tanjim Reza

  19. Hi, BHANU!
    Thank you for your query.
    As the problem discussed here is sorted from smallest to largest, you would also need to sort your numbers before applying this article’s approach.

    Regards,
    Tanjim Reza

  20. Hi, BHANU!
    Thank you for your query.
    As the problem discussed here is sorted from smallest to largest, you would also need to sort your numbers before applying this article’s approach.

    Regards,
    Tanjim Reza

  21. Hi, JEFF!
    Thank you for your query.
    I am afraid you can not incorporate this article’s formula into the SUMIFS formula to perform a loop because we have used the MAX function in this article. But, it is a limitation of Excel to use the SUMIFS function with the MAX function.
    To know about some other Excel limitations, you can follow the link below:
    22 Limitations of Excel That Might Frustrate You

    Regards,
    Tanjim Reza

  22. Hi, BANDEET POUDEL!
    Thank you for your query.
    I am a little bit confused about your query. Are you asking if you can jump to a row upon writing the row number at an instant?
    If you are asking this, then the solution is:
    You can write the preferred cell’s reference number in the name box and press the Enter button to jump there in an instant.
    Please let us know the feedback if your problem is solved or if you meant some other things in your query.

    Regards,
    Tanjim Reza

  23. Hi, VIJAY!
    Thank you for your feedback!

  24. Thank you for your appreciation, JUSTIN!

  25. Thank you for your appreciation, FERREIRA!

  26. Hi, WILL!
    Thank you for your query!
    You can definitely point to a cell containing the desired date rather than typing it inside the formula. Say, you have placed the required date in the C15 cell.
    In that case, you can use the following formula:

    =SUMIFS(D4:D12, C4:C12,">="&$C$15-60, C4:C12,"<="&$C$15)
  27. Hello, KANHAIYALAL NEWASKAR!
    Thank you so much for your appreciation.
    We also hope to learn and share more and more knowledge with everyone!

    Regards,
    Tanjim Reza

  28. Hi, ARUN!
    Thank you for your query.
    According to our dataset, we have 9 values that are repeated twice or thrice. Other values are unique and have come only once in the dataset. As the formula is about finding the frequent numbers, it is returning those repeated 9 numbers only.

    Regards,
    Tanjim Reza

  29. Hi, GEOFF BARTLETT! We appreciate your thoughtful query.
    Workaround 1:
    For your first problem (Surname, forename2 forename1), you can use the formula below:
    =SUBSTITUTE((RIGHT($B7,LEN($B7)-FIND("^",SUBSTITUTE($B7," ","^",LEN($B7)-LEN(SUBSTITUTE($B7," ",""))))))&" "&(MID($B7,SEARCH(" ",$B7)+1,SEARCH(" ",$B7,SEARCH(" ",$B7)+1)-(SEARCH(" ",$B7)+1)))&" "&(LEFT($B7,SEARCH(" ",$B7)-1)),",", "")

    And, for your second problem (Surname, forename2 forename3 forename1) you can use the formula below:
    =SUBSTITUTE((RIGHT($B5,LEN($B5)-FIND("^",SUBSTITUTE($B5," ","^",LEN($B5)-LEN(SUBSTITUTE($B5," ",""))))))&" "&(MID($B5,SEARCH(" ",$B5)+1,SEARCH(" ",$B5,SEARCH(" ",$B5)+1)-(SEARCH(" ",$B5)+1)))&" "&"("&(MID($B5,SEARCH(" ",$B5,SEARCH(" ",$B5,1)+1)+1,SEARCH(" ",$B5,SEARCH(" ",$B5,SEARCH(" ",$B5,1)+1)+1)-(SEARCH(" ",$B5,SEARCH(" ",$B5,1)+1)+1)))&")"&" "&(LEFT($B5,SEARCH(" ",$B5)-1)),",", "")

    Workaround 2:
    Besides, another workflow you can use in this regard. That is:
    Step 1: Use the Text to Columns tool from the Data tab for splitting every name.
    https://www.exceldemy.com/text-to-columns-excel/
    Switch Text to Columns

    Convert Text to Columns Wizard- Step 1 of 3

    Convert Text to Columns Wizard- Step 2 of 3

    Convert Text to Columns Wizard- Step 3 of 3

    Step 2: Sort them according to your desired sequence. Use a helper row for that. Use and finally >>
    Access the Sort Feature

    Sort Options Window

    Sort by Helper Row

    Last Step: Use the CONCATENATE function to combine them in a cell.
    https://www.exceldemy.com/excel-concatenate-function/
    CONCATENATE Function to Combine Sorted Results

    Regards,
    Tanjim Reza

  30. Hi, JUSTIN!
    Thank you for your query.
    You can accomplish your desired result by using the formula below:

    =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&" "&LEFT(A2,1)

    Regards,
    Tanjim Reza

  31. Hi, A!
    Thank you for your query.
    The FV formula that you need for your solution will be like this:
    =FV([(Interest Rate-Inflation Rate)/Frequency of Payment Per Year],[Frequency of Payment Per Year * Total Years],[-Payment Per Period], Present Value,0)
    So, with your given values, the formula would be:
    =FV(3%,41,-1000,0,0)

    Regards,
    Tanjim Reza

  32. Hi, ADRIAAN!
    Thank you for your query.
    The FV formula that you need for your solution will be like this:
    =FV([(Interest Rate-Inflation Rate)/Frequency of Payment Per Year],[Frequency of Payment Per Year * Total Years],[-Payment Per Period], Present Value,0)
    So, with your given values, the formula would be:
    =FV(3%,41,-1000,0,0)

    Regards,
    Tanjim Reza

  33. Hi, ERIC!
    Thank you for your valuable feedback.
    You have pointed out a nice thing. When the #VALUE! error happens in this regard, it is better to use the find and replace feature to replace ” ” with CHAR(160) before applying the formula.

    Regards,
    Tanjim Reza

  34. Hi, JO JONES!
    Thank you for your query.
    Can you please tell me what you meant by the multiple strawberries in the criteria range? Does it mean that you are putting multiple strawberry categories in the range? If yes, then I would suggest you put the full name of the strawberries along with their categories in the cells. As a result, every strawberry name will be unique as per dates. Moreover, if a category has multiple data at multiple dates, then you can look up your value through the unique dates.
    If your problem still exists, please let us know your feedback and help us understand your question better.

    Regards,
    Tanjim Reza

  35. Hi, BRENT!
    You have asked a fantastic question.
    You can change the fill/text color automatically by using conditional formatting. You can apply multiple rules to a cell using this. To learn about conditional formatting in this regard, you can go to the following link.
    https://www.exceldemy.com/excel-conditional-formatting-greater-than-another-cell/

    Regards,
    Tanjim Reza

  36. Hello, KEITH FROST!
    Thank you for your query.
    There is no error in your code. You just need to add “filename:=filename” at the ActiveSheet.ExportAsFixedFormat command.
    So, just write the export format command line as:

    ActiveSheet.ExportAsFixedFormat Type:=TypePDF, filename:=filename

    Regards,
    Tanjim Reza

  37. Hi, PAOLO!
    Thank you for your query.
    You have asked about a fantastic thing. If we want to compare the first 6 numbers/match, rather than the full cell value, we have to use the LEFT function in addition to the formulas.
    So, I would suggest you use the second and third methods shown in this article and use the nested LEFT function inside the VLOOKUP function for the 2nd method and inside the MATCH function for the 3rd method.
    So, for the second method, the formula would be:
    =IF(ISNA(VLOOKUP(LEFT(A2,4),LEFT($B$2:$B$8,4),1,FALSE)),"NO","YES")
    And, for the third method, the formula would be:
    =IF((ISERROR(MATCH(LEFT(A2,4),LEFT($B$2:$B$8,4),0))),A2,"")

    Have a nice day!

  38. Hi, LAURA!
    Thank you for your query.
    Your query’s solution is very easy, but a little bit tricky. For the solution, at the name box of a cell, write the cell references of your desired cells where you want to put the same value by copying.
    Let, you want to put the number 10 to B2:B160002 cells. Now, write B2:B160002 in the name box. As a result, your desired 160000 cells will be selected. Now, write 10 and press Ctrl + Shift + Enter.
    Moreover, you can follow one of our articles named “How to Copy and Paste Thousands of Rows in Excel” to know more. The link to this article is given below.
    https://www.exceldemy.com/how-to-copy-and-paste-thousands-of-rows-in-excel/

    Have a nice day!

  39. Hi, AHMAD AKAR!
    Thank you for your query.
    You have encountered an interesting problem. Regarding your query, we have a different article already posted on our website. Please follow the link below to learn about the solution to your query.
    https://www.exceldemy.com/copy-and-paste-in-excel-when-filter-is-on/

    Have a good day!

  40. Hi, BELLA!
    Thank you for your appreciation!

  41. Hello, MARK EVANS!
    You have pointed out a fantastic thing.
    Your reference is correct. We had an unfortunate mistake in the reference to this formula. There will be L8 at the reference in the place of the C10 reference.
    Thank you for your valuable feedback. We appreciate it so much.
    So, the formula should be:
    =SUMPRODUCT(D5:I14,(C5:C14=L8)*(D4:I4=L9)*(B5:B14=L7))

    Regards,
    Tanjim Reza

  42. Hi PAUL R HARTLEY! Thank you for your query.

    You can fix these links easily using the following process.
    Go to the Data tab >> Queries & Connections group >> Edit Links tool.
    Afterward, all the links used in this workbook will be shown to you in the Edit Links window. Select individual links and click on the Check Status button for each of them.
    If you see an Error: Source not found in any status, click on the Change Source button. Subsequently, browse your “form” Excel sheet >> click on the OK button >> click on the Close button of the Edit Links window.

    Regards,
    Tanjim Reza

  43. Hello, W BREKVELD!
    Thank you for your query.

    Yes, you can multiply a value with the looked-up percentage corresponding with a year. Just use the VLOOKUP function properly. Then write your desired multiplier in the formula bar before the used VLOOKUP function and then insert an asterisk (*) symbol.
    Thus, the value will be multiplied by the looked-up percentage. And, to do this until the current year, use your fill handle feature to copy the same formula.

    Regards,
    Tanjim Reza

  44. Hello, TEMITOPE!

    You have asked a nice thing. Using VLOOKUP with dates is a complex thing sometimes and may result in errors easily. In this regard, you need to use the TEXT function.
    Suppose, you have 5 columns in your table. You want the fifth column value for the C16 text and C15 date value. The date column is in the C5:C13 cells. In this situation, you can use the following formula for using VLOOKUP with text and date.

    =VLOOKUP(C16,IF(TEXT(C5:C13,"mm/dd/yy")=TEXT(C15,"mm/dd/yy"), B5:F13, ""), 5, FALSE)

    Thank you for your query. We appreciate it so much.
    Regards,
    Tanjim Reza

  45. Hello, NIEFER!
    You have pointed out a fantastic thing.
    It is correct that you don’t need to merge the cells B:D here. You can just enlarge the B column as much as you need. I just wanted to keep the column size closer to each other. That’s why I merged. But, it is not necessary. Rather, it is better to enlarge the B column as the references will be simpler that way.
    Thank you NIEFER for your valuable feedback. I appreciate it so much.

    Regards,
    Tanjim Reza

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo