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:
=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))

fk_selrange_1

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

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *