User Posts: Tanjim Reza
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, ...

0
How to Record Time of Data Entry in Excel (2 Effective Ways)
0

Excel provides us with a wide range of facilities to handle a large amount of data and work with them. When entering data into an Excel sheet, we might need ...

0
How to Design Form in Excel (2 Suitable Examples)
0

When working with Excel, sometimes, we need user inputs. Forms make this easy to take inputs from users and process them accordingly. In this article, I will ...

0
How to Create Table from Another Table with Criteria in Excel
0

When working with Excel tables, sometimes we need to create another table from the existing tables based on criteria. In this article, I will show you 3 ...

0
Excel VLOOKUP Returning Column Header Instead of Value
0

VLOOKUP function is a practical function to lookup specific values from a given dataset. When working with the VLOOKUP function, there are some sensitive ...

0
[Fixed!] Excel VLOOKUP Not Working Due to Format (2 Solutions)
0

Excel VLOOKUP function is a fantastic function to look up values vertically from a dataset. But, due to different types of errors, the VLOOKUP function does ...

0
[Fixed!] Excel Scrolling Too Many Rows (2 Easy Solutions)
0

When working with Excel, sometimes we have to work with very large datasets. In this case, scrolling gets too sensitive, as we move too many rows with a little ...

0
How to Copy from Excel and Paste to Google Sheets with Formulas
0

When working with Excel files, sometimes we need to copy data to google sheets for more people’s access. But when copying from Excel by default, the formulas ...

0
Formula to Copy and Paste Values in Excel Automatically
0

We find numerous values, formulas, and calculations when working with Excel. Sometimes, we need to copy and paste only the values. This will be more efficient ...

Browsing All Comments By: Tanjim Reza
  1. 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

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

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

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

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

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

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

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

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

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

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

    Regards,
    Tanjim Reza

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

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

  14. 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:
    https://www.exceldemy.com/top-20-excel-limitations-that-might-frustrate-you/#15_No_sumifscountifsaverageifs_equivalents_for_functions_such_as_max_or_median

    Regards,
    Tanjim Reza

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

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

  17. Thank you for your appreciation, JUSTIN!

  18. Thank you for your appreciation, FERREIRA!

  19. 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)
  20. 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

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

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

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

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

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

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

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

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

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

  30. 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!

  31. 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/copy-and-paste-thousands-of-rows/

    Have a nice day!

  32. 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!

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

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

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

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

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

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

ExcelDemy
Logo