Excel -- Names -- Use Range Names in Formulas

Create a Table  
Create the first Dynamic Range
Create the remaining Dynamic Ranges  
Create the Date Range selection cells  
Create the Summary formulas 
Create the Chart  
Test the Chart 

You can use a dynamic formula to define a range. As new items are added, the range will automatically expand.

In this example, dynamic range names are used in formulas that summarize a list of sales records, and a chart is based on the result. When new records are added, or a different date range is selected, the chart is immediately updated.

 

Download the zipped sample file
Create a Table
  1. In a new workbook, delete all except two worksheets.
  2. Rename Sheet1 as SalesData
  3. Rename Sheet2 as Chart
  4. On the SalesData sheet, in cells A1:D1, add the headings, Date, Item, Colour, Units, as shown at the right.
  5. Enter sample data in each column, or use the data in the zipped sample file.

Create the first Dynamic Range

A dynamic range will be created for the Date column.

  1. Choose Insert | Name | Define
  2. Type a name for the first range -- DateList
  3. In the Refers To box, enter an Offset formula that defines the range size, based on the count of numbers in the Date column:
    =OFFSET(SalesData!$A$2,0,0,COUNT(SalesData!$A:$A),1)
    The arguments used in this Offset function are:
    1. Reference cell: SalesData!$A$2
    2. Rows to offset: 0
    3. Columns to offset: 0
    4. Number of Rows: COUNT(SalesData!$A:$A)
    5. Number of Columns: 1
  4. Click the Add button

Create the remaining Dynamic Ranges

The remaining dynamic ranges will be based on the DateList range.

  1. Type a name for the next range -- ColourList
  2. In the Refers To box, enter an Offset formula that refers to the DateList range:
    =OFFSET(DateList,0,2)
    The arguments used in this Offset function are:
    1. Reference range: DateList
    2. Rows to offset: 0
    3. Columns to offset: 2
  3. Click the Add button
  4. Type a name for the next range -- UnitsList
  5. In the Refers To box, enter an Offset formula that refers to the DateList range:
    =OFFSET(DateList,0,3)
    The arguments used in this Offset function are:
    1. Reference range: DateList
    2. Rows to offset: 0
    3. Columns to offset: 3
  6. Click the OK button

 

Create the Date Range selection cells

Use Data Validation to create dropdown lists for the chart start and end dates.

  1. On the Chart sheet, select cells C2 and E2.
  2. Choose Data | Validation
  3. From the Allow dropdown, choose List
  4. In the Source box, type:   =DateList
  5. Click OK
  6. Add a border to the cells, to highlight them.
  7. Select a sample date in each list, to be used in the Summary formulas.
  8. Format the dates as desired (Format | Cells, Number tab)

 

Create the Summary formulas

Use the SUMPRODUCT function to total the sales for each colour.

  1. On the Chart sheet, in cells B15:B18, type the list of colours sold.
  2. In cell C15, enter the following formula:
    =SUMPRODUCT(--(ColourList=B15),(UnitsList),--(DateList>=$C$2),--(DateList<=$E$2))
  3. Copy the formula down to row 18.

 

Create the Chart

Create a chart from the summary list.

  1. On the Chart sheet, select cells B15:C18.
  2. On the toolbar, click the Chart Wizard button
  3. Create a column chart from the data
  4. Remove the chart legend, and adjust the chart size so it fits in the space between the date selection cells, and the summary list.
  5. Click on the series to select it
  6. Click on the Red column, to select it.
  7. Double-click on the Red column, to open the Format Data Point dialog box
  8. For Area, select the red colour, then click the OK button.
  9. Double-click on each of the remaining columns, and colour them.

 

 

 

 

Test the Chart

  1. From the Date Range selection cells, select different dates.
  2. The chart should update immediately, to reflect the new date range.

For more information on Excel Charts, see Jon Peltier's site.

 

 

 

Download the zipped sample file  
   
1. Names -- Naming Ranges  
2. Names -- Using Names in Formulas  
 

       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 18, 2008 11:42 PM