Relative references to named range

[Pete]

On behalf of my newphew, I received this query:

"Quick one - I have a spreadsheet with very long formulas that reference a
whole lot of variables in the same column. I tried naming the cells so that
writing of the formula is easier to do. However, I need to copy the column
across for other entities of varying nature, and these copied cells refer to
the same named cell. Can I name a cell and copy it sideways so that it
retains some sort of name with allusion to the column that it is in?

Andrew van Gruting"

I have tried copying a formula with a named range and, as expected, it
maintains an absolute reference. Is there a way that he might be able to
copy but drop the absolute reference and copy with relative references. Can
I place something in front - the equivalent to the $ sign, but rather a
relative sign.
Peter

 

[Earl Kiosterud]

Pete,

Named cells are inherently absolute references. If they weren't, what
should the next one be -- what name would it have? Or would it revert to
cell references?

 

[Harlan Grove]

Named cells are inherently absolute references. If they weren't, what
should the next one be -- what name would it have? Or would it revert to
cell references?

....

You've misunderstoond the OP.

Given C1:E6 each containing =1000*ROW()+COLUMN()

In cell C7 define the name foo referring to the formula

=C$1+C$3+C$5

Enter =foo in C7. It should evaluate to 9009. Fill C7 right into D7:E7. D7
should evaluate to 9012 and E7 to 9015.

Defined names can contain relative range references. Defined names can be
any formula that can fit in the max allowed number of characters. Relative
references in defined names depend on the cell that was active at the time
they were defined, so they're confusing in A1 terms. They're much easier to
cope with in R1C1 terms, in which case foo is just

=R1C+R3C+R5C

The OP just needs to use relative references in his defined names.

 

[Pete]

Thanks for the responses.

I think he named some cells rather than a set of computations so I imagine
that he is now stuck with the built in absoluteness of the named range -
I will pass on what was said.
Peter