PDA

View Full Version : Confused about RefersTo



shankar
04-22-2009, 04:31 AM
I am a bit confused about the correct syntax for the RefersTo parameter, esp when referencing with a different workbook.

Let's say I have a named range which is local to sheet1 of workbook1

workbook1.worksheets("sheet1").names.add name = "Name1", RefersTo:=workbook1.worksheets("sheet1").range("A1")
Now I want to refer to it in workbook2. Is the following correct?

workbook2.Names.Add Name:="NewName", RefersTo:="=" & workbook1.Name & "." & "sheet1!Name1"
Also is the first statement entirely equivalent to the following?

workbook1.names.add name:="sheet1!Name1", RefersTo:=workbook1.worksheets("sheet1").Range("A1")
Does this mean that if sheet1 of workbook1 is not the active sheet, then referring to "Name1" without "sheet1!" in front of it would cause an error in both cases?

I am a bit confused about the usage of the bang symbol. In RefersTo as well as in names, this comes in between the worksheet and the range (instead of a period, as otherwise). But between the workbook and the worksheet, is a bang used, or is it still the period?

Thanks.

Shankar

Bob Phillips
04-22-2009, 04:47 AM
You are not referring to anything, all of those statements are defining names.

What exactly are you trying to achieve?

shankar
04-22-2009, 04:54 AM
I just want to know if "NewName" refers to the A1 cell of the original worksheet and not some other range, irrespective of which worksheet is currently active. Hope my concern is clear. Thanks.

Bob Phillips
04-22-2009, 04:55 AM
It refers to whatever you have set it to. That is obvious is it not?

shankar
04-22-2009, 06:57 AM
I am still not sure about the syntax to refer to a sheet level named reference from another workbook. To give an example, take
Sub assign1()
With ThisWorkbook.Worksheets("sheet1")
.Range("A1").Value = "John" 'assigns "John" to A1 of sheet1 in macro workbook
.Names.Add Name:="Name1", RefersTo:=.Range("A1") 'Name1 is local to sheet1
End With
With ThisWorkbook.Worksheets("sheet2")
.Range("A1").Value = "Jane" 'assigns "Jane" to A1 of sheet2 in macro workbook
.Names.Add Name:="Name1", RefersTo:=.Range("A1") 'Name1 is local to sheet2
End With
End Sub
and

Sub assign2() 'run this in different workbook from macro workbook
Names.Add Name:="NewName", RefersTo:="=" & ThisWorkbook.Name & "!Name1"
MsgBox Range("NewName").Cells(1, 1).Value
End Sub
Now, when I run assign2 from a new workbook, the message box prints out "John". I am not able to make NewName point to Name1 in sheet2 of the first workbook. I tried
Names.Add Name:="NewName", RefersTo:="=" & ThisWorkbook.Name & ".Sheet2!Name1" but I get a run-time error. So my simple question is, what is the ccorrect syntax of referring to Name1 of sheet2 of the first workbook while having another workbook as the active one? Hope I am clear this time. Thanks.

Shankar

Bob Phillips
04-22-2009, 10:50 AM
The second one errors for me, you are using Thisworkbook so it cannot point to some other workbook, and there is no Name1 in thisworkbook.

shankar
04-23-2009, 08:20 PM
ok, I got the correct syntax- it should be-

Names.Add Name:="NewName", RefersTo:="=[" & ThisWorkbook.Name & "]sheet2!Name1"
In other words, if you are using both workbook and a local worksheet prefix, you have square brackets around the workbook name preceding the sheet name. The above prints "Jane"