Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remote cell reference in formulae
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) ? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remote cell reference in formulae
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" wrote in message ... 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) ? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remote cell reference in formulae
Dear Bernie
Thank you for your help, I'll see if I can make that work. Regards Graham "Bernie Deitrick" wrote: 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" wrote in message ... 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) ? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remote cell reference in formulae
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. "Graham" wrote: 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) ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remote 3D reference array | Excel Discussion (Misc queries) | |||
Inability to create remote reference | Excel Discussion (Misc queries) | |||
to AND or OR remote reference? | Excel Discussion (Misc queries) | |||
Can you have fixed cell reference when dragging/copying formulae? | Excel Discussion (Misc queries) | |||
retain 'update remote reference' flag | Excel Worksheet Functions |