Named Range

[Gautam]

I've named few ranges through VBA by using this code

For i = 1 To 15

' Define the name for the variables
ActiveWorkbook.Names.Add Name:=Worksheets("ST&GT DATA").Cells(1, i),
RefersToR1C1:= _
"=OFFSET('ST&GT DATA'!R1C1,'ST&GT DATA'!R1C214," & (i - 1) &
",'ST&GT DATA'!R1C216,1)"
Next i

which is working fine, but when i try to refer it in a chart, an error
msg is popping up saying "Your formula contains an invalid external
reference to a worksheet, Verify that the path, workbook, and range
name or cell reference are correct, and try again"

Can any one solve this problem?

Thanks

Gautam VK

 

[Dave Peterson]

You have a response at your other post.

I've named few ranges through VBA by using this code

For i = 1 To 15

' Define the name for the variables
ActiveWorkbook.Names.Add Name:=Worksheets("ST&GT DATA").Cells(1, i),
RefersToR1C1:= _
"=OFFSET('ST&GT DATA'!R1C1,'ST&GT DATA'!R1C214," & (i - 1) &
",'ST&GT DATA'!R1C216,1)"
Next i

which is working fine, but when i try to refer it in a chart, an error
msg is popping up saying "Your formula contains an invalid external
reference to a worksheet, Verify that the path, workbook, and range
name or cell reference are correct, and try again"

Can any one solve this problem?

Thanks

Gautam VK

 

[Guest]

Hi,

Are you using the sheet name or the workbook name to reference the named
range in your chart? I think that it should be the workbook name.

If you edit the range in the interactive mode then Excel changes the sheet
name to the workbook name:-

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$18,Book1!Col_2,1)

I have not had any real success in editing the series range in the formula
bar in the interactive mode. However, if I right click the series and then
Select Data and then click on Edit then I have not had any problems. If I
insert the sheet name here with the named range then Excel changes it to the
workbook name.

If you are making the changes in VBA then perhaps it is the worksheet name
that is giving you grief. Try workbook name instead.

Regards,

OssieMac