# Calculating with the selected range

Jan Karel inspired me with his use of Excel4 Macros to Refer to the previous sheet and to determine page numbers to use this “old” Excel feature for something else: Automatic calculations with the current selected range.

To start first define the name Sel_Range with the formula:

Sel_Range returns a string representing the current selected range. Using INDIRECT this string can be converted to a range reference and used in the following type of formulas on your sheet:
=SUM(INDIRECT(Sel_Range))
=AVERAGE(INDIRECT(Sel_Range))
=MIN(INDIRECT(Sel_Range))
=MAX(INDIRECT(Sel_Range)) Notes:

• After entering this formula you first get a circular reference error. Just hit F9 after leaving the cell to refresh the formula result
• To re-calculate the formula(s) simply press F9
• And as always for using Excel4 macros: In versions prior to Excel 2003 DON’T copy formulas containing names using these macros as this will crash Excel

Happy new year
Frank Kabel

Posted in Uncategorized

## 7 thoughts on “Calculating with the selected range”

1. jkpieterse says:

Nice one Frank. So tell me, what does the \$A\$1 do in the formula?

2. Frank Kabel says:

Hi jan Karel
thanks :-)
Re: Why use \$A\$1: Normaly then using the formula
you only get the upper left cell returnes. e.g.
returns
“\$B\$1?
and not
“\$B\$1:\$C\$20?

But if you use
you’ll get
\$A\$1,\$B\$1:\$C\$20
and now you only have to strip the first 5 characters to get the complete range string of the desired range

Frank

3. jkpieterse says:

Devious!

4. Keith says:

I could not, for the life of me, determine why &TEXT(NOW(),””) needed to be included in the Sel_Range definition, so I erased that portion of the formula. It appears to work correctly without the extra text. Am I missing something?

5. jkpieterse says:

It makes the name volatile so it gets recalculated every time.

6. G Mourmant says:

Thanks for this tip.

What will be the difference with something like that :

Define the name sel_range as
=offset(selection(),,)

then use
=sum(sel_range)

It seems to work pretty well

However, I still haven’t fix the problem of recalculation, even on the example given here.
So I have to use F9

7. jkpieterse says:

Your F9 is needed because Excel does not recalc on a cell movement. But by including the NOW clause you ensure that when Excel does recalc, that fomrula is included in the calculation.

Posting code? Use <pre> tags for VBA and <code> tags for inline.