Range Names & Auto Fill

[Guest]

I am fighting with range names. I feel that my workbooks would be both more
robust and more readable if I used them consistently. To this end I would be
very greatful if anyone would help me with what is probably a silly question.

In the following example I have used Names>Create to create the range names
apples, pears, north, south etc. refering to b2:b5, c2:c5,b2:d2,b3:d3
respectivley.

A B C D
apples pears bananas
north 1 5 9
south 2 6 10
east 3 7 11
west 4 8 12

Say in column E I want to sum b2:d2 ie north then =sum(north) in e2 does the
trick - and is easy to read. But now if I use auto fill to pull the formula
down from e2 through to e5 I just get sum(north) in each cell. If I had used
=sum(b2:d2) then, being relative addresses, all would have been well. I
realise that for the example it would be easy to just enter the correct
furmula for each row but this would not be sensible on a larger sheet. I
know I can use INDIRECT eg =SUM(INDIRECT(A2)), which pulls down fine, but
then the readability disappears. Am I missing something or is this a
limitation I have to live with.

Sorry for such a long post. Many thanks in antcipation. Peter

 

[Debra Dalgleish]

You could create the first formula using the cell references, and copy
it down to the last row of data.
Then, select the cells with the SUM formulas
Choose Insert>Name>Apply, and click OK

The cell references will be changed to range names.
Note: this will work for named ranges on the same sheet as the formulas.

 

[Guest]

Debra,

Thanks for taking the time to reply. I will do as you suggest but it is
only a partial solution as the workbook contains some 18 sheets and the
Apply>Names will, as you say, only work if the ranges are on the same sheet
as the formulae and this will often not be the case. So, pushing my luck, -
are there any other approaches I can take? Or am I just trying to use range
names in a way not intended? It would seem that if a formula needs to be
extensively copied around a speadsheet, range names are best avoided.

Once again many thanks, Peter