Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected formula generation using named ranges
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? -- Al C |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unexpected formula generation using named ranges
Accidentally posted in the wrong group.
-- Al C "Al" wrote: 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? -- Al C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using named ranges in a formula | Excel Worksheet Functions | |||
named ranges in multiple criteria formula | Excel Discussion (Misc queries) | |||
Use named ranges in array formula | Excel Worksheet Functions | |||
using named ranges for formula abbreviations | Excel Worksheet Functions | |||
UPDATED - Referencing named Ranges within a Nested IF formula | Excel Worksheet Functions |