How to Name cells with relative reference

Hi All,

Let me apologise at the outset as I could not put my question in a precise way.

I have 6 columns of data with 2000 rows and am writing some formulae in columns 7 to 12 on row2(Row 1 being the header row), referring to the first 6 columns( like a2+b2+c2+d2+e2+f2 in column 7, b2+c2+d2+e2+f2 in column 8, c2+d2+e2+f2 in column 9 and so on).

I want to give meaningful names to the cell references in the formulae,in
such a way,that when copied down until last row of data, the formulae should work relatively to each row and not absolute( If I "define a name "Överdue" for cell A2 and use it in formulae in columns 7 to 12, as above, and copy down to rows 3 and 4, it should refer to A3 and A4 respectively.

I have tried defining a column name, but it is referring to whole column
which is not serving my purpose.

Any help is very much appreciated.

Hi,

Adding names at this stage seems unwise, though there is a way:
1st recommendation:
Just put =sum(a2:f2) in the 7th column and copy it across, if you want to include a "note to self" in the formula you could do this:
=sum(a2:f2)+N("sums 6 cells to left in same row")
which would give the same result, but you can write anything you like inside N("...")

2nd option (not recommended)
Select G1
Insert->Name->Define
put a name in the top, like 'six_to_left' (NB can't include spaces)
in the formula box put:
=a1:f1
(no $s)

Now every =sum(six_to_left) will sum the six cells to the left of the cell in which the formula is.

HTH

Hi Charlie,

Thanks a lot. This served my purpose.


Best Regards,
Venkat