How to use Range Names Containing Formulas

We’ve seen in our earlier post on Range Naming how you can give a cell or range of cells a range name, and then use that in formulas. However, there is a powerful yet little-known feature (little-known to me until recently, at least!) that you can create a range name that instead uses a formula. Not just a cell address.

Such formulas will not appear in the Name Box dropdown list:

You can list all your Range Names, including ones that are formulas.  Go to a blank area of the workbook.  Use Formulas (ribbon) > Defined Names (group) > Use in Formula > Paste Names… (F3) > Paste List (a truly well-hidden option).

You can also see the formulas in Formulas (ribbon) > Defined Names (group) > Name Manager (Ctrl+F3).

1.     Why Use Range Name Formulas instead of Formulas in Cells?

Good question!

In an earlier post, we discussed a Gantt Chart Project Planner using Cell Shading.  Pieces of it are shown above.  You can ask us to email it to you.

That spreadsheet uses clever conditional formatting of cells to produce a horizontal bar chart without using Excel charts.  The conditional shading of cells can be overlaid.  There are six different shadings, each based on a different condition.  If that were based on formulas in the shaded cells, such formulas would have to be horrendously complicated.

Instead, using Range Name Formulas, each shading can have its own (comparatively simple) formula.

We discussed all the formulas in that earlier post. Here we will focus on how to create just two of the Range Name formulas.  We look at one that uses cell values, and one that relies on the previous formula as well.

2.     Creating a Range Name Formula

If you are using the Gantt Chart Project Planner mentioned above, save it with a different name for this exercise.  Open Formulas (ribbon) > Defined Names (group) > Name Manager. There, delete the range names PeriodInPlan and Plan, which we will create anew.

The easiest way to create such a formula is to put it into a cell first.  Make sure it works.  Then copy it and paste it into the Define Name dialog box.

As with any formula that uses relative references, it is important to put it into the correct cell.  In this case, we will be looking at formulas for the shaded cells that form the “bar graph”, cells H5:BO30.

Click on cell H5:

Enter the formula =H$4=MEDIAN(H$4, $C5, $C5+$D5-1)

You can enter it in parts to see how they work.

How does this work?  MEDIAN of three values will always give us the value that is arithmetically in the middle. H$4 (current column, always row 4) is the period number.  $C5 (always column C, current row) is the starting period for the activity. $D5 is the plan duration, hence $C5+$D5-1 is the end period for the activity.  Now take the formula as a whole.  If MEDIAN places the period number between the start and end of the activity, then H$4=MEDIAN will return TRUE. Otherwise, this formula will return FALSE.

In the Formula Box, select the whole formula, and cut it.

Go to Formulas (ribbon) > Defined Names (group) > Define Name.  In the “Refers to” box, paste the formula you just cut:  Use the name PeriodInPlan:

Click OK.

3.     Creating a Range Name Formula using Another Range Name

Click on cell H5 again:

Enter the formula =PeriodInPlan*($C5>0)

How does this work? 

As we have just seen, PeriodInPlan is a True or False value.  ($C5>0) will return True if the starting period for the activity on the current row is greater than zero. That is, if there actually is an activity.  TRUE has a value of 1, FALSE has a value of zero.  Hence if both PeriodInPlan and ($C5>0) are true, their product is TRUE.  Otherwise, Plan will be FALSE.  This is equivalent to the AND() function.

At this point, it is good to check if your highlighted cells match those shown above.  If not, chances are that you entered this or the previous formula into the wrong cell.

In the Formula Box, select the whole formula, and cut it.

Go to Formulas (ribbon) > Defined Names (group) > Define Name.  In the “Refers to” box, paste the formula you cut:  Use the name Plan:

Click OK.

4.     Notes

Click on a different cell and go into Formulas (ribbon) > Defined Names (group) > Name Manager.

Look at the relative references in the formulas used by the range names.  Are they different to the ones you entered?  This is like any formula you copy to another cell.  Relative references –not prefixed with a $– adjust to the new position.

Will you be using any non-absolute cell references (without the $ prefix)?   Then it is critical to select the correct cell before you enter the formula. Parts that apply to the current cell (column and/or row) will adjust to match whichever cell uses the formula. This is a powerful feature and can be a serious pitfall.

That then, is how Range Name work using Formulas.  What uses have you found for them?  Send us an email and tell us!