This worked fine for me:

Sub Tester1()
For Each ws In Worksheets
ws.Names.Add Name:=ws.Name & "!EjectaX", _
RefersTo:="=OFFSET('" & ws.Name & "'!$J$1,'" & _
ws.Name & "'!$S$2,0,'" & ws.Name & "'!$S$7-'" _
& ws.Name & "'!$S$2,1)"
Next
End Sub


You need an equal sign in front of the Offset or your formula gets turned
into a text string. You were also missing a ! before $S$2

the name is Sheet1!EjectaX (as an example).

It is shown with the sheet name on the right (as you describe - but that is
the normal way to show it), but the name is as the above.

--
Regards,
Tom Ogilvy


"GerbilGod7 " wrote in message
...
After some tweaking, I've gotten it to work, sorta... Here's what I
use:

ws.Names.Add Name:=ws.Name & "!EjectaX", RefersTo:="OFFSET('" & ws.Name
& "'!$J$1,'" & ws.Name & "'$S$2,0,'" & ws.Name & "'!$S$7-'" & ws.Name &
"'!$S$2,1)"

In each worksheet, it creates a named range with this name:

EjectaX ...[bunch of spaces]... (worksheet name)

I'm not sure why it's doing this; I've tried:

"'" & ws.Name & "'!EjectaX" etc, but it doesn't make a difference.

How do I get it to name these ranges properly, and ultimately graph
them?


---
Message posted from http://www.ExcelForum.com/