1 Select the range of cells that you want to name.
2 Choose Name from the Insert menu, then choose Define. Excel displays
the Define Name dialog box.
3 In the Names in Workbook box, enter the name of the current
worksheet, an exclamation mark, and the name you want to define, as in
Sheet1!MyRange.
4 Click on Add.

-Andrew

One way:

In the name box, include the sheet name:

   Sheet1!MyName

And if your sheetname requires apostrophes:

'Sheet 1'!yournamehere

Since you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It has a feature that allows you to convert global names to local and local to
global.  (workbook level to worksheet level and vice versa.)

1 Select the range of cells that you want to name.
2 Choose Name from the Insert menu, then choose Define. Excel displays
the Define Name dialog box.
3 In the Names in Workbook box, enter the name of the current
worksheet, an exclamation mark, and the name you want to define, as in
Sheet1!MyRange.
4 Click on Add.

-Andrew