Copy Data to Other Sheets' Columns Based on Criteria

Hi Rob,

In addition to PivotTable solution which Argelo suggested here are couple more. Which one to use depends on many factors, from Excel versions and do you use predefined forms or not; to personal preferences.

Anyway, first one using array formulas (Ctrl+Shift+Enter) to add. Let assume your data is located as

orders

month

The pattern for the formula which extract data from master list based on criteria is described here http://www.exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/4/ and in many other places.

In our case in monthly sheet in A2 let add

=IFERROR(
   INDEX(Orders!$A$2:$C$660,
      SMALL(
         IF((MONTH(Orders!$A$2:$A$660)=$H$1)*(Orders!$A$2:$A$660>1),
            ROW(Orders!$A$2:$A$660) - ROW(Orders!$A$2)+1
         ),ROW(1:1)
      ),1
   ),""
)

We take 660-rows range in Orders to have some gap. If fortunatelly you have more it is to be increased. Formula return first date in orders for defined in cell $H$1 month. Dates is first column in the range, in bold above in formula.

Copy this formula in B2 and change 1 on 3 to receive data from third column "Make".

To receive the cost by type for this record in C2 add

=IFERROR(
   INDEX(Orders!$F$2:$F$660,
      MATCH(1,
         (Orders!$A$2:$A$660=$A2)*
         (Orders!$C$2:$C$660=$B2)*
         (Orders!$B$2:$B$660=C$1),
      0)
   ),
0)

which finds the cost for the order for given Date and Make (A2 and B2), and Type for this column (C1). 

Copy this cell to the right till last column type (F2).

Select cells from C2 to F2 and apply cusom format (Ctrl+1) to them

[$$-en-US]#,##0.00;;;@

which 'hides' zeroes in the cells.

Now select all cells in row 2 and drag them down till out of your ordes in the month, better more. Finally select sheet tab and copy it for another months. Only H1 is enough to change for each given month.

In formulas above size of orders list could be defined dynamically, but bit easier if you use for orders Excel Table (that's only one of pros). Let name that table as Orders, when in monthly tabs alternatives to above formulas will be

=IFERROR(
   INDEX(Orders[[Order Date]:[Make]],
      SMALL(
         IF((MONTH(Orders[Order Date])=$H$1)*(Orders[Order Date]>1),
            ROW(Orders[Order Date]) - ROW(Orders[[#Headers],[Order Date]])
         ),ROW(1:1)
      ),1
   ),""
)

and

=IFERROR(
   INDEX(Orders[Cost],
      MATCH(1,
         (Orders[Order Date]=$A2)*
         (Orders[Make]=$B2)*
         (Orders[Type]=C$1),
      0)
   ),
0)

And finally the option with using Power Query (aka Get & Transform in Excel 2016). First, we load and pivot for types Orders table. Could be done in few clicks in user interface, here is code generated

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{
      {"Order Date", type date},
      {"Type", type text},
      {"Make", type text},
      {"Model", type text},
      {"Year", Int64.Type},
      {"Cost", type number}
    }),
    RemoveUnused = Table.RemoveColumns(ChangeType,{"Model", "Year"}),
    ReordereColumns = Table.ReorderColumns(RemoveUnused,
      {"Order Date", "Make", "Type", "Cost"}
   ),
    PivotType = Table.Pivot(ReordereColumns,
      List.Distinct(ReordereColumns[Type]), "Type", "Cost")
in
    PivotType

Load the query as connection. After that make the reference on that query fro each month adding filter for the monh

let
    Source = Orders,
    FilterMonth = Table.SelectRows(Source, each Date.Month([Order Date]) = 9)
in
    FilterMonth

and load each of them into Excel sheet

Fileas are attached