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 NamesCreate 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

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 InsertNameApply, 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.

Peter wrote:
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 NamesCreate 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
Contextures
http://www.contextures.com/tiptech.html

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
ApplyNames 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

"Debra Dalgleish" wrote:

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 InsertNameApply, 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.

Peter wrote:
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 NamesCreate 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
Contextures
http://www.contextures.com/tiptech.html