14 On the June2015 worksheet , in cell E4 , use the INDEX
6 function to identify the transaction amount that aligns with the position in cell C4 . Type 5 for the column _ num and use the range A7 : E24 as the array argument .
15 On the June2015 worksheet , filter the data in the range
4
A6 : E24 using the criteria in the range A27 : E28 . Set the filter to copy the data to the range A31 : E31 . In cell I17 , use the DAVERAGE function to determine the average amount spent for transactions meeting the criteria in the range A27 : E28 .
16 Group the June2015 and JuneTotals worksheets
3 together . Fill the contents and formatting from cell A1 on the JuneTotals worksheet across the grouped worksheets . Ungroup the worksheets . In cell I19 on the June2015 worksheet , insert a reference to cell E26 on the JuneTotals worksheet .
17 On the June2015 worksheet , create a validation rule for
5 the range D7 : D24 to only allow values in the list from the range I21 : I24 . Create an error alert for the rule that will display after invalid data is entered . Using the Stop