Question:

I found a question here about tracking a stock portfolio. He would like to automatically create an overview table with a unique stock symbol per row.  He also wants the range extended down to include new rows as they become valid.

Example,

Sheet1 Symbol     Type    Shares    Price F          Buy     100       12 MSFT       Buy     100       25 MSFT       Buy     50        28 F          Buy     100       16 GOOG       Buy     25        550 Sheet2 Symbol Quantity F 200 MSFT 150 GOOG 25

Answer:

Sheet1

Sheet2

Create named ranges

  1. Select tab "Formulas" on the ribbon
  2. Click "New..." button
  3. Type
    Symbol

    in Name: bar

  4. Type formula:
    =Sheet1!$A$2:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A))

    in "Refers to:" bar

  5. Click Close button

Repeat above instructions with following names and formulas:

Type - =Sheet1!$B$2:INDEX(Sheet1!$B:$B, COUNTA(Sheet1!$B:$B))
Shares - =Sheet1!$C$2:INDEX(Sheet1!$C:$C, COUNTA(Sheet1!$C:$C))
Price - =Sheet1!$D$2:INDEX(Sheet1!$D:$D, COUNTA(Sheet1!$D:$D))

Array formulas

Array formula in cell A2, sheet 2:

=IFERROR(INDEX(Symbol, MATCH(0, COUNTIF($A$1:A1, Symbol), 0)), "") + CTRL + SHIFT + ENTER

Copy cell A2 and paste down as far as needed. This formula creates uniue distinct symbols. Read this post: How to extract a unique distinct list from a column for a formula explanation.

Array formula in cell B2, sheet 2:

=IF(A2<>"", SUMPRODUCT((A2=Symbol)*(Type="Buy")*Shares)-SUMPRODUCT((A2=Symbol)*(Type="Sell")*Shares), "") + Enter

Copy cell B2 and paste down as far as needed.

Download excel file

Tracking a stock portfolio.xlsx
(Excel 2007 -2010 Workbook *.xlsx)

Pivot table - Semi auto update

You can also convert the range in sheet1 to a table and then create a pivot table with data from table. The table includes new rows automatically. Unfortunately the pivot table does not. Right click anywhere on pivot table and click Refresh when new rows are added.

The question is how to subtract sold shares from bought shares in a pivot table?