Unexpected formula generation using named ranges

[Al]

This is a repost that was originally submitted to the wrong group.

I have a macro that creates user workbooks from library worksheets that are
copied and renamed as needed to set up the file. The library worksheets have
a number of named ranges, so when I generate formulas on a summary worksheet
in this workbook to reference a particular cell, they take the general format
“=WorkSheetName!RangeNameâ€. Occasionally, I notice that the actual formula
resulting from this turns out as “=WorkBookName!RangeNameâ€. Obviously that
reference invokes a range on only one worksheet, and references to the same
range name on other worksheets show up as I would expect. Functionally it
behaves perfectly well. The only pattern I see is that it will happen only
on the first row of my summary (first instance of the specific formula), but
it will happen in the middle of a row or not at all

My typical code (tempvalue is a lookup worksheet name) -

With Range("A7")
..Offset(k, 8) = "=" & tempvalue & "!YD1"
..Offset(k, 9) = "=" & tempvalue & "!YDD"
..Offset(k, 11) = "=" & tempvalue & "!MATL"
End With

Can anyone explain why this would happen?

 

[Charles Williams]

Looks like a confusion between Local names and Global Names in this
workbook and local and Global names in an external workbook, this is what
you should be getting:

Thisworkbook:
local name- WorkSheetName!RangeName
Global Name- RangeName

Open External Workbook:
local name- [WorkbookName]WorksheetName!RangeName
global name- WorkbookName!RangeName

Closed External Workbook:
local name- 'C:\Excel\[WorkbookName]WorksheetName'!RangeName
global name- 'C:\Excel\WorkbookName'!RangeName


It sounds as though you may have duplicate global-local names (a RangeName
that has both Global (workbook) scope and local(one or more worksheets)
scope). This can cause significant problems and IMHO should be avoided.

I would suggest that you download Name Manager from
http://www.decisionmodels.com/downloads.htm

It has filters which make it easy to find Loacl names, global names and one
specifically to find duplicate global-local Names.


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