create relative named formulas using Names.Add Name:= RefersTo:=

[Werner Rohrmoser]

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

 

[Charles Williams]

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

 

[Peter T]

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

 

[Peter T]

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

Regards,
Peter T

 

[Werner Rohrmoser]

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