Tanjim Reza

Hello, PREM! Hope you are doing well.
Thank you for your query.
Regarding your query, basically, the Balance Sheet comes after generating a journal, ledger, trial balance, income statement, and owner’s equity statement. So, to automate a balance sheet, you need to automate journal, ledger, trial balance, income statement, and owner’s equity statement first.
In this article, basically, we automated trial balance and income statements. An automated balance sheet will be uploaded soon too. Before that, you can go through our website to get individual automated sheets to generate your own balance sheet automatically.

With Regards,
Md. Tanjim Reza Tanim
Team Leader, ExcelDemy

Hello, Test. Thank you for this beautiful feedback.

Yes, you are right. This doesn’t seem right. We found the error as we were dividing the interest rate by 100 even though it was already in Percentage format. As a result, our answer got multiplied by 100 later. That is, when calculating the 1+Rate value, we had to divide cell C6 by only 12, not 100 and 12 both.
We have fixed the error now. You can go through the corrected article again and download our corrected workbook too.
However, this article is written on the basis of compound interest. You can also use the simple interest formula here to find your simple HELOC Payment.

With Regards,
Md. Tanjim Reza Tanim

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.



• Afterward, insert the following code in the code window and save the Excel file as .xlsm file.


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.

I hope this accomplishes your desired result.

Regards,
Tanjim Reza

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

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.
  
• 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.
  
• Thus, the Assign Macro window would appear.
• Here, choose a Macro name as you wish and click on the New button.
  
• 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
  
• Now, you need to create UserForm1.
• To do this, go to the Insert tab inside the VB Editor and choose the UserForm option.
  
• 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.
  
• After dragging the label, name it.
• Following, choose the TextBox option from the Toolbox window and drag it inside the form area.
  
• Following, name the text box and repeat the previous procedures to create another label.
  
• Continue to repeat these procedures to create all labels, textboxes, and the search button inside the form.
  
• Now, right-click on your Search button and choose the option View Code from the context menu.
  
• 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.
  
• Now, say, you want to find data for Craig Arvin.
• Insert the name in the first textbox. And, click on the Search button.
  

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

Regards,
Tanjim Reza

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

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

2. Following, drag the fill handle downward to accomplish your full result.

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

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.

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

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

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

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

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

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

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

Hi, VIJAY!
Thank you for your feedback!

Thank you for your appreciation, FERREIRA!

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

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/

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

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

Regards,
Tanjim Reza

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

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

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

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!

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!

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

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

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