Named Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gautam - 30 Sep 2007 06:03 GMT

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

Reply to this Message

Dave Peterson - 30 Sep 2007 12:29 GMT

You have a response at your other post.

> I've named few ranges through VBA by using this code
>
[quoted text clipped - 17 lines]
>
> Gautam VK

Signature

Dave Peterson

Reply to this Message

OssieMac - 30 Sep 2007 12:42 GMT

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

> I've named few ranges through VBA by using this code
>
[quoted text clipped - 17 lines]
>
> Gautam VK

Reply to this Message

Rate this thread: