help with Excel programming

[purplesky]

suppose I want to write a formula in a cell that say "= this cell + the cell above it" Could this be done. Is there a way to to reference the cells relative to where the formula is located.


Thanks,

Purplesky

 

[Loomah]

in essence, unless you create an absolute reference the references are relative. If you enter a formula in cell A2 =A1 this will always refer to the cell directly above. If you were to COPY this formula to, say F28 it will read =F27.

If you entered =$A$1 and copied it anywhere in the sheet it will still refer to A1. This is an absolute reference.

However to the best of my knowledge you cannot do what you are actually asking here, that is "=thiscell + abovecell" as this would create a circular reference. The bottom line is you you cannot enter a formula into a cell that refers to itself.

As far as I'm aware you would hace to do it by code - something along the lines of

ActiveCell.Value = ActiveCell.Value + ActiveCell.Offset(-1, 0)

;-)
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[cheerio]

You can do this with a named range. Although by default a named range is an absolute reference you can make it a relative reference. So you can create a named range called CellAbove and use that in formulas. It is particularly useful in a formula like
= SUM(B1:CellAbove).
If you insert a new row in the row above the formula that new row will be included in the SUM.

Ken