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

Sheet2
Symbol               Quantity
F                    200
MSFT                 150
GOOG                 25```

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: