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:

=MID(CELL(“address”,($A$1,SELECTION())),6,255)&TEXT(NOW(),””)

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

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

Hi jan Karel

thanks :-)

Re: Why use $A$1: Normaly then using the formula

=CELL(“address”,range)

you only get the upper left cell returnes. e.g.

=CELL(“address”,B1:C20)

returns

“$B$1?

and not

“$B$1:$C$20?

But if you use

=CELL(“address”,(A1,B1:C20))

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

Devious!

I could not, for the life of me, determine why

&TEXT(NOW(),””)needed to be included in theSel_Rangedefinition, so I erased that portion of the formula. It appears to work correctly without the extra text. Am I missing something?It makes the name volatile so it gets recalculated every time.

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

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.