Selected Answer
Excel has made it very easy to crate named ranges in several ways but there is only one universal tool that can do everything. That is the Name Manager, available from the ribbon's Formulas tab. Beginners should start with familarising themselves with this tool.
To create a new named range, open the Name Manager and click New.
- Define a Name.
The name allows no spaces. Use underscores if you must. I get done using upper case characters to mark the beginning of words.
- Define the Scope.
Make the new range name available to the entire workbook, meaning you can use it in formulas on all sheets. If you know that you will use the name only on one sheet, limit the scope to that sheet. Formulas on other sheets will not have access to that name.
- Add a Comment (optional)
You may write a description of the purpose for which you created the range. That may be helpful when other people work with the workbook.
- Define the range (in the Refers to field).
Excel suggests the currently selected range by default. You can accept it or type another. Or you can enter a formula to define the range.
- Press OK to create the name.
Once created, the new range's name is listed by the Name Manager. Select it and click Edit ... or Delete.
To create a dynamic range use a formula like the one below in the Refers to field.
=OFFSET('Aged Data'!$A$2,0,0,COUNTA('Aged Data'!A:A)-1,14)
This formula defines a range starting 0 rows and 0 columns offset from A2 (effectively from A2). For some advanced uses you may want to calculate the offset, using more formulas. It's more useful to note that you can start your range at any cell.
The range has COUNTA('Aged Data'!A:A)-1 rows and 14 columns. Most ranges you may create will have only a single column. For the purpose of this tutorial I want to show how to handle a multi-column range.
COUNTA('Aged Data'!A:A)-1 is the formula which makes the range dynamic. As you add or delete a row the range will expand or shrink. The presumption is that column A has an entry in every used row, no blank rows. You can define another column to count entries in. The final -1 will not be required if A1 is blank (and therefore not counted by COUNTA). There are other formulas to use if you don't have a column without blanks.
In your worksheet, the above formula will create a range referring to $A$2:$N$33167 (33166 rows). I have named it AgeData. The formula COUNTA(AgeData) returns the number of used cells within the range. Observe that it will cause a circular reference (with a #REF error as result) if entered within the AgeData range.
In your AWD Weekly Dashboard, cell F5 you have this formula. =COUNTIFS('Aged Data'!$H:$H,">=30",'Aged Data'!$H:$H,"<60")+COUNTIF('Aged Data'!$N:$N,"blank"). Presuming that you had created two dynamic ranges, Ages (column H) and Unaged (column N), this formula would now look like this.
=COUNTIFS(Ages,">=30",Ages,"<60")+COUNTIF(Unaged,"blank")
However, if you prefer - for whatever reasons - to name only a single range, using the one I created in this example, you would need to specify individual columns of it and the formula would look like this.
=COUNTIFS(INDEX(AgeData,,8),">=30",INDEX(AgeData,,8),"<60")+COUNTIF(INDEX(AgeData,,14),"blank")
INDEX(AgeData,,8) specifies the 8th column of the range AgeData. In this case that is column H because the range starts in column A. Neddless to say, the column number could be calculated using another embedded formula. Note that within the context of the INDEX function the column is defined by the Area number argument, not the Column argument which serves, in conjunction with the Row argument, to define a single cell.
