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 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?
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.