Relative references to named range

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

Lets say row 1 contains a range of values to be pointed to.

Highlight Row 1 (or the range of values in row 1) and name it "Row1".

Then use the Insert pull down menu to select Name > define to get to the named range list. Create a new named rage called "Variable". In its "Refers to:" formula box enter the formula "=indirect(address(row(Row1),column()))".

Now in any cell below the row 1 range you can enter the formula "=Variable" and the value returned will be the corresponding value in the row 1 range.

I think this does what you described. If not, it should be modifiable to do what you like.

Best Regards,
Sean

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?

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Pete" <jbircher@iafrica.com> wrote in message
news:csg39g$17d$1@ctb-nnrp2.saix.net...
> 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
>
>

Named ranges are not as hard and fast as you're thinking. In the case of the example I outlined, the named range "Variable" actually refers to the cell at a location relative to its own. So, if you copy the formula "=Variable" across the row each cell in adjacent columns will evaluate to a different value despite the fact the formula is exactly the same in each cell. Each cell contains the formula "=Variable" but evaluates according to the formula in the named range definition.

Good luck,
Sean

"Earl Kiosterud" <nothanks@nospam.com> wrote...
>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.

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

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:ejdNV8S$EHA.2540@TK2MSFTNGP09.phx.gbl...
> "Earl Kiosterud" <nothanks@nospam.com> wrote...
> >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.
>
>

I had the same issue. I named values to be used in calculations in different rows in the same column, then wanted to copy the column to repeat the calculations with other values to compare.

The trick is to keep the row absolute while letting the colunn be relative to the current column.

In Excel 2007, select a cell in the same column the names were originally defined in then select Formulas then Name Manager. Edit all the names to remove the $ before the column letter. $B$10 beccomes B$10, etc. Then you're good to go.