Hello,

I'm using a For Next loop to create some named formulas.

*****************************************************************
For LoopCounter = 11 To 18
ThisWorkbook.Names.Add Name:=Service.Cells(LoopCounter, 1).Value, _
RefersTo:=Service.Cells(LoopCounter, 2).Value
Next LoopCounter
*****************************************************************

the RefersTo arguments looks like this:
"ABS(C$173-SUM(C$158,C$165,C$168))<1" (columns relative)

When I run my procedure having cell pointer on column"C" I get
named formulas, which looks like this:
"ABS(E$173-SUM(E$158,E$165,E$168))<1" (offset of two rows).

When I use column"A" in the RefersTo argument I get column "C" in my
named formulas.

CRAZY!

Any help is appreciated.
Excel XP SP3
WIN XP SP1

Best Regards
Werner

Hi Werner,

Using relative names with A1 notation can be very confusing.

I strongly recommend using R1C1 notation (ReferstoR1C1) when
creating/looking at relative names: it is much easier to work out whats
happening.

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Werner Rohrmoser" <werner-(E-Mail Removed)> wrote in message
news:8f72c6a3-11e9-4076-94cc-(E-Mail Removed)...
> Hello,
>
> I'm using a For Next loop to create some named formulas.
>
> *****************************************************************
> For LoopCounter = 11 To 18
> ThisWorkbook.Names.Add Name:=Service.Cells(LoopCounter, 1).Value, _
> RefersTo:=Service.Cells(LoopCounter, 2).Value
> Next LoopCounter
> *****************************************************************
>
> the RefersTo arguments looks like this:
> "ABS(C$173-SUM(C$158,C$165,C$168))<1" (columns relative)
>
> When I run my procedure having cell pointer on column"C" I get
> named formulas, which looks like this:
> "ABS(E$173-SUM(E$158,E$165,E$168))<1" (offset of two rows).
>
> When I use column"A" in the RefersTo argument I get column "C" in my
> named formulas.
>
> CRAZY!
>
> Any help is appreciated.
> Excel XP SP3
> WIN XP SP1
>
> Best Regards
> Werner

Not crazy, it's doing what you told it too

With cursor selecting A1, define these two names

BBB: =B1
TOP: =A$1

put =BBB and =TOP in a few random cells
BBB cells will return contents of cell offset one to right
TOP cells will return contents of cell in row-1 of same column

When you want to define names with relative addresses you may need to
activate an appropriate cell. There are other ways without doing that
involving RC notation and dual use of ConvertFormula (switching RelativeTo),
but simpler to activate the right cell first.

Regards,
Peter T


"Werner Rohrmoser" <werner-(E-Mail Removed)> wrote in message
news:8f72c6a3-11e9-4076-94cc-(E-Mail Removed)...
> Hello,
>
> I'm using a For Next loop to create some named formulas.
>
> *****************************************************************
> For LoopCounter = 11 To 18
> ThisWorkbook.Names.Add Name:=Service.Cells(LoopCounter, 1).Value, _
> RefersTo:=Service.Cells(LoopCounter, 2).Value
> Next LoopCounter
> *****************************************************************
>
> the RefersTo arguments looks like this:
> "ABS(C$173-SUM(C$158,C$165,C$168))<1" (columns relative)
>
> When I run my procedure having cell pointer on column"C" I get
> named formulas, which looks like this:
> "ABS(E$173-SUM(E$158,E$165,E$168))<1" (offset of two rows).
>
> When I use column"A" in the RefersTo argument I get column "C" in my
> named formulas.
>
> CRAZY!
>
> Any help is appreciated.
> Excel XP SP3
> WIN XP SP1
>
> Best Regards
> Werner

Of course, go with Charles Williams' suggestion and use R1C1 with
ReferstoR1C1.

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> Not crazy, it's doing what you told it too
>
> With cursor selecting A1, define these two names
>
> BBB: =B1
> TOP: =A$1
>
> put =BBB and =TOP in a few random cells
> BBB cells will return contents of cell offset one to right
> TOP cells will return contents of cell in row-1 of same column
>
> When you want to define names with relative addresses you may need to
> activate an appropriate cell. There are other ways without doing that
> involving RC notation and dual use of ConvertFormula (switching
RelativeTo),
> but simpler to activate the right cell first.
>
> Regards,
> Peter T
>
>
> "Werner Rohrmoser" <werner-(E-Mail Removed)> wrote in message
> news:8f72c6a3-11e9-4076-94cc-(E-Mail Removed)...
> > Hello,
> >
> > I'm using a For Next loop to create some named formulas.
> >
> > *****************************************************************
> > For LoopCounter = 11 To 18
> > ThisWorkbook.Names.Add Name:=Service.Cells(LoopCounter, 1).Value, _
> > RefersTo:=Service.Cells(LoopCounter, 2).Value
> > Next LoopCounter
> > *****************************************************************
> >
> > the RefersTo arguments looks like this:
> > "ABS(C$173-SUM(C$158,C$165,C$168))<1" (columns relative)
> >
> > When I run my procedure having cell pointer on column"C" I get
> > named formulas, which looks like this:
> > "ABS(E$173-SUM(E$158,E$165,E$168))<1" (offset of two rows).
> >
> > When I use column"A" in the RefersTo argument I get column "C" in my
> > named formulas.
> >
> > CRAZY!
> >
> > Any help is appreciated.
> > Excel XP SP3
> > WIN XP SP1
> >
> > Best Regards
> > Werner
>
>

Dear Peter,

manual input is not my problem, I'm pretty familiar with relative
names.
Today I tried to create some names via VBA using "RefersTo:=" instead
of "RefersToR1C1:=" which works as I have expected.

To get the effect I've described you should try it via vba and then it
seems to be not as clear
as it is doing it via the names dialog.

Regards
Werner