Remote cell reference in formulae

[Graham]

Hi,
With a simple formula replicated over 12 columns, e.g. =SUM(C5:C10) can the
"C10" be referenced from a remote cell, whereby changing the value in the
remote cell to, say C12, will automatically adjust the formula in all 12
columns to recalculate as (C5:C12) to (N5:N12) ?

 

[Bernie Deitrick]

Put the 12 into cell A1, and use a formula like

=SUM(OFFSET(C5,0,0,$A$1-ROW(C5)+1,1))

HTH,
Bernie
MS Excel MVP

 

[Graham]

Dear Bernie
Thank you for your help, I'll see if I can make that work.
Regards
Graham

 

[challa prabhu]

Hi,

I have a cell A1 with value 12, if i want to use this value several times in
the worksheet, i must copy the absoulete address of the cell, which means if
I move this cell to any location, the abosulute Address should reflect in the
sum function.

You must copy this cell value and using the paste special-paste link, copy
this value to a different cell location. Then use this absoulue value in the
sum function.

Then try moving the cell from A1 to A10, you will notice that the value in
the sum function showing the modified cell address.

Challa prabhu.