## Auto populate a worksheet

**Answer:**

Your question is perfect for a Pivot Table. The problem is that you need to refresh the Pivot Table every time you change the source data, however, this can be done automatically with a few lines of event code.

I´ll show you how to set up the pivot table and how to make it auto refresh using VBA. I will also provide an answer for how to accomplish this using only array formulas.

**What you will learn in this article:**

- Create an Excel defined Table.
- Create a Pivot Table.
- How to set up a Pivot Table.
- Use VBA code in your workbook.
- Save your workbook as a macro-enabled workbook.
- Create array formulas and how they work.

*Example sheet "Data"*

### Create an Excel defined Table

Why convert the data set to an Excel defined Table? It expands automatically meaning you don't need to adjust cell references if you add or delete data.

- Select any cell in your data set.
- Go to tab "Insert".
- Press with left mouse button on "Table" button.

- Press with left mouse button on the checkbox "My Table has headers" if appropriate.
- Press with left mouse button on OK button.

The image below shows what the Excel defined Table probably will look like.

### Create a Pivot Table

- Select all data on Sheet "Data"
- Go to tab "Insert"
- Press with left mouse button on "Pivot Table" button
- Press with left mouse button on "Pivot table" and a dialog box appears.
- I chose to put the Pivot Table on a new Worksheet.
- Press and hold with left mouse button on fields in Field List.
- Drag to different areas below, now release left mouse button.
- The dates and items go to RowLabels.
- The Price goes to Values area.

Read more about Pivot Tables for more detailed instructions.

The following animated image shows the above steps in greater detail.

*Example sheet "Pivot table"*

Below is VBA code to refresh Pivot Table every time the Pivot Table Sheet is activated. You put the VBA code in a worksheet module, instructions below.

Private Sub Worksheet_Activate() Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable End Sub

### Where to put the code?

- Press with right mouse button on on sheet name "Pivot table"
- Press with left mouse button on "View Data".
- Paste code to the worksheet module.

- Exit VB Editor and return to Excel.

### Totals based on Name and Date

The formulas demonstrated here are not necessary if you use a Pivot Table, they simply show how to accomplish the same thing using only formulas. I highly recommend using a Pivot Table.

The image above shows three different formulas, the first formula in cell range B3:C15 extracts unique distinct records based on date and name.

The second formula counts records based on name and date, the third formula adds prices and returns a total also based on date and name.

Array formula in cell B3:

#### How to create an array formula

- Copy formula above.
- Select cell B3.
- Paste formula to cell.
- Press and hold Ctrl + Shift simultaneously.
- Press Enter once.
- Release all keyboard keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself, they appear automatically if you did the above steps correctly.

#### How to copy array formula

- Select cell B3
- Copy cell. (Short cut keys: Ctrl + c)
- Select cell range B3:C15
- Paste to cell range (Short cut keys: Ctrl +v).

#### Explaining formula in cell B3

The array formula contains cell references (structured references) to the Excel defined Table, there is now no need to adjust cell references if the data source expands or shrinks.

You can find an explanation to the formula here: Filter unique distinct records

### Count records based on conditions

Formula in cell D3:

Copy cell D3 and paste to cell range D3:D15.

#### Explaining formula in cell D3

This formula counts the number of records based on two conditions, name in the corresponding cell in column C and date in the corresponding cell in column B.

This article explains the formula in greater detail: Count unique distinct records

### Sum amounts based on conditions

Formula in cell E3:

This formula is simply a SUMIFS function, read more about it here: How to use the SUMIFS function

In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]

Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]

In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]

### 2 Responses to “Auto populate a worksheet”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

I may be complicating so if so I apologize up front! I want to autopopulate tab 2 with select information from tab 1 if "Yes" is inut on tab 2, column A.

I have a lot of data in tab 1 but the actionable items are in tab 2 only if "Yes". I may have 50 rows in tab 1 but only need 3 rows in tab 2 and I don't need all columns in tab 1, only a few.

What is the best way to automate this process? Thank you!

William J. Ryan

Can you provide some example data from both sheets?