Rodney Schmidt asks:

I am a convience store owner that is looking to make a spreadsheet formula. I want this formula to use information from one spreadsheet to auto-populate another spreadsheet on the next tab. I want the date the purchase was made, the consumer, and however many items the consumer purchased to equal one transaction on the other spreadsheet. Your help with this would be greatly appreciated.

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 data. I´ll show you how to setup 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.

Example sheet "Data"

Example sheet "Pivot table"

  1. Select all data on Sheet "Data"
  2. Go to tab "Insert"
  3. Click "Pivot table" button
  4. Click "Pivot table"
  5. See the remaining steps in this animated gif:

Vba code to refresh pivot table every time the pivot table sheet is activated.

Private Sub Worksheet_Activate()
    Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable
End Sub
  1. Right click on sheet name "Pivot table"
  2. Click "View Data"
  3. Paste code to code module
  4. Exit VB Editor

Example sheet "Array formula"

Array formula in cell A2:

=INDEX(Data!$B$2:$C$21, MATCH(0, COUNTIFS($B$1:B1, Data!$C$2:$C$21, $A$1:A1, Data!$B$2:$B$21), 0), COLUMN(A1))

How to create an array formula

  1. Select cell A2
  2. Paste formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

How to copy array formula

  1. Select cell A2
  2. Copy (Ctrl + c)
  3. Select cell B2
  4. Paste (Ctrl +v)
  5. Select cell range A2:B2
  6. Copy (Ctrl + c)
  7. Select cell range A3:B15
  8. Paste (Ctrl +c)

Formula in cell C2:

=COUNTIFS(Data!$C$2:$C$21, B2, Data!$B$2:$B$21, A2)

How to copy formula in cell C2

  1. Select cell C2
  2. Copy (Ctrl + c)
  3. Select cell C3:C15
  4. Paste (Ctrl + v)

Formula in cell D2:

=SUMIFS(Data!$E$2:$E$21, Data!$C$2:$C$21, B2, Data!$B$2:$B$21, A2)

How to copy formula in cell C2

  1. Select cell D2
  2. Copy (Ctrl + c)
  3. Select cell D3:D15
  4. Paste (Ctrl + v)

You can make the "Array formula" sheet auto populating using dynamic named ranges instead of cell references:
Create a dynamic named range in excel

Download excel *.xlsm file

Auto populate sheet.xlsm

Related posts:

Disable autofit column widths for all pivot tables in a sheet

Auto refresh a pivot table in excel

Copy each sheet in active workbook to new workbooks

Tracking a stock portfolio in excel (auto update)

Excel vba: Populate a combobox with values from a pivot table