OFFSET and range addresses
 

I have a lot of analytical data values (say in column D) whose values I
calculate using calibration curve data which I reference in a range elsewhere
in the sheet (say range AA9:AG14). I use the calibration data multiple times
before I have to input a new range of calibration data, but sometimes I have
to use older calibration data. I have named the calibration data ranges, and
have hyperlinked the individual ranges to the analytical data value areas,
and use the curve parameters to calculate my analytical values.

It would be advantageous to be able to reference the range in the equations
by referring to the cell with the hyperlinked range name and use the OFFSET
function to perform the calculations; that way I don't have to individually
change and copy the multiple calculations in the analytical values area each
time the calibration changes.However, when I try to use the "ADDRESS"
function I get an error. To have to put the name of the range in all the
affected cells defeats the purpose.

Is there any way to use a referenced range in an Excel sheet within the
"OFFSET" function by referring to a cell that has the name of the range?

OFFSET and range addresses
 

Try

INDIRECT(range_name)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"chemist" wrote in message
...
I have a lot of analytical data values (say in column D) whose values I
calculate using calibration curve data which I reference in a range
elsewhere
in the sheet (say range AA9:AG14). I use the calibration data multiple
times
before I have to input a new range of calibration data, but sometimes I
have
to use older calibration data. I have named the calibration data ranges,
and
have hyperlinked the individual ranges to the analytical data value areas,
and use the curve parameters to calculate my analytical values.

It would be advantageous to be able to reference the range in the
equations
by referring to the cell with the hyperlinked range name and use the
OFFSET
function to perform the calculations; that way I don't have to
individually
change and copy the multiple calculations in the analytical values area
each
time the calibration changes.However, when I try to use the "ADDRESS"
function I get an error. To have to put the name of the range in all the
affected cells defeats the purpose.

Is there any way to use a referenced range in an Excel sheet within the
"OFFSET" function by referring to a cell that has the name of the range?