Excel: Auto fill cells in various locations

https://authentification.site/files/22853814/Sample.xls

So what I want is that by filling in the transactions sheet then the holdings sheet will fill in automatically too.

The price today and x-rate today and so on is not important as I will get that from external sources but if you could help me with the frame work on how to split the list up into different categories in the holdings sheet.

Thank you very much!

From what I see, the issue is how can one say when it comes to TYPE in transaction, would align with the types in Holdings. For example, in transaction "Government Bond", how can one say that it falls under "Country Bonds"
"

Sorry that was my mistake as I have been changing it from Danish into English.

But my question is more related to the process of how I can make Excel do the transfer of the data from the list of transactions into the various parts of the holdings sections automatically?

As you can see right now then its all typed in by hand manually and what I want is to automate it as this is the specific case for just one portfolio...I have hundreds of portfolios so if I could set up a general template then I could apply that one to the others ones as well

https://authentification.site/files/22854226/Sample.xls

On very high level, if you want to automate the process of transferring data from transfer to data source then the first thing you have to decide, how system would know if a row is ready to be copied. For that you can have a cell designated for copy or have one of the existing cell be used for that.

Then you would need to look up the TYPE on datasource and locate the row from where the label for that category exisits

once you have seen that, then you need to decide if you want to insert on top or to add to the bottom of the group. In either case now you have to locate the row where the insertion would occur

Now if you want to do on automate basis, then you need to use Sheet event called Worksheet_Change on Sheet Transaction, If you go to VBE and double click on transaction sheet, you would see the event ( you have to use dropdown to locate it)

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Target is the cell that has changed. This would be your cell that you have designated as flag that row is ready to be copied

Also while the code is running, you should disable event else you would get into cyclic loop
some thing like this


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
..... you code here

Application.EnableEvents = TRUE

End Sub

,