Excel: Auto fill cells in various locations
Closed
Martin
-
Jun 8, 2010 at 06:04 AM
rizvisa1
rizvisa1
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Related:
- Excel: Auto fill cells in various locations
- Excel auto copy value to another cell - Guide
- Cell reference to locate /store data in excel ✓ - Forum - Excel
- Excel auto filter based on cell value ✓ - Forum - Excel
- Excel formula if cell contains text then return value in another cell ✓ - Forum - Excel
- Duplicate rows in excel based on cell value ✓ - Forum - Excel
1 reply
rizvisa1
Jun 8, 2010 at 06:37 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
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
Jun 8, 2010 at 06:58 AM
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!
Jun 8, 2010 at 07:24 AM
"
Jun 8, 2010 at 07:34 AM
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
Jun 8, 2010 at 07:54 AM
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
,