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