Previous Topic Next Topic

Excel: Auto fill cells in various locations

Closed
Martin - Jun 8, 2010 at 06:04 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 - Jun 8, 2010 at 07:54 AM
Hello,

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.



1 reply

Answer 1 / 1
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Jun 8, 2010 at 06:37 AM
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
0
Martin
Jun 8, 2010 at 06:58 AM
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!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Jun 8, 2010 at 07:24 AM
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"
"
0
Martin
Jun 8, 2010 at 07:34 AM
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
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Jun 8, 2010 at 07:54 AM
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

,
0