Not sure how to go about doing this (or if it can be done). I have a spreadsheet in excel with 3 sheets. The third one is currently empty. The first 2 sheets have stock trades from the past 10 years. One with about 50 entries and the second with about 70. Each entry has columns like this: Stock Symbol, Company, Quantity, Purchase date, Purchase price, Total Buy $, Sell date, Sell price, total Sale $, Loss/Gain.
What I am looking to do is dynamically build the 3rd sheet (just for reporting/viewing) with only entries in the first 2 sheets that have a purchase date but no sell date. IE: just show current stock holdings. So as a stock is sold and a sell date is entered into one of the first 2 sheets, then it will disappear from the 3rd sheet (or with little effort; trying to remove the manual process of copy/paste/delete).
The holdings aren't all at the bottom of the list. For instance, of the 50 entries in sheet 1, rows 5, 21, 23, 43, 46, 47, and 48 have a purchase date but no sell date. Each row has just one purchase date and one sale date (no partial sales). Sheets 1 and 2 are essentially the same but different data (one is for one account and the second is for a separate account).
I would like to stay in Excel for the solution and have zero VB experience. I am not sure if this can be solved with array of IF and Lookup functions? I appreciate in advance any help offered!
What I am looking to do is dynamically build the 3rd sheet (just for reporting/viewing) with only entries in the first 2 sheets that have a purchase date but no sell date. IE: just show current stock holdings. So as a stock is sold and a sell date is entered into one of the first 2 sheets, then it will disappear from the 3rd sheet (or with little effort; trying to remove the manual process of copy/paste/delete).
The holdings aren't all at the bottom of the list. For instance, of the 50 entries in sheet 1, rows 5, 21, 23, 43, 46, 47, and 48 have a purchase date but no sell date. Each row has just one purchase date and one sale date (no partial sales). Sheets 1 and 2 are essentially the same but different data (one is for one account and the second is for a separate account).
I would like to stay in Excel for the solution and have zero VB experience. I am not sure if this can be solved with array of IF and Lookup functions? I appreciate in advance any help offered!
