I'm working on an excel worksheet where I have 2 spreadsheets running. On one of them I have a list transactions with different categories such as date, name of product, category. amount, price, etc...
In the other one I have a list of the different categories of products so that I can track which products are selling better and so on.
My question is: How can you go from having a long list of transactions to making excel split these up according to the category and fill them in in different locations.
Any sort of help that can bring me closer to an answer is appreciated. Thank you in advance.
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://docs.google.com,http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
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.
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
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
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!
- Posts
-
4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020
768"
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
- Posts
-
4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020
768Then 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
,