I figured it out. :)

if I name them (the ranges) like this =$A$1 instead of =!$A$1 then I can
reference them by '<sheet'!<range_name

I guess excel does a literal substitution and as a result the two !'s were
confusing it.
bubba assigned to !$A$1
'sheet 1'!bubba gets translated to 'sheet 1'!!$A$1
so excel starts looking for a spreadsheet named "sheet 1" I guess. It seems
to make some sort of logical sense; to me....

This opens up so many possibilities for me. I should be able to eliminate
several hundred VLOOKUP() formulas from my workbook.

THANK YOU for pointing me in the right direction.

"Gord Dibben" <gorddibbATshawDOTca@ wrote in message
...
Adam

Apologies. Misunderstood

I thought you wanted rn1hp to refer to same cell $C$5 on each sheet.

To reference Sheet1!rn1hp from another sheet, you have to make the name
sheet-specific when you create the name.

You're back to adding the refers to: =sheet1!$C$5 as you create the name.


Gord

On Sun, 11 Dec 2005 04:03:02 -0600, "Adam Kroger"
wrote:

Works well within the active sheet, but I can not figure out how to
reference it from another sheet.

EXAMPLE
active sheet is pilot2

rn1hp refers to !$C$5

I want to refer to r1hp on sheet1

=sheet1!rn1hp returns #NAME? and brings up a file dialog box
&
=INDIRECT(sheet1!rn1hp) returns #REF! and changes the formula to
='TESTBED -
Combat Tracker v3.0.4.xls'!rn1hp

thanks


"Gord Dibben" <gorddibbATshawDOTca@ wrote in message
. ..
Adam

Try this..........

On sheet1 InsertNameDefine.

Type in "globalgame"(no quotes)

In refers to box type in =!$A$1:$K$22 and OK your way out.

Go to any sheet and F5. In the reference: dialog Type in
"globalgame"(no
quotes) and OK.


Gord Dibben Excel MVP

On Sat, 10 Dec 2005 14:16:21 -0600, "Adam Kroger"
wrote:

a further clarification, can you name a range of cells say A1:K22 game,
and
then specify sheet1!game, sheet2!game without haveing to name then on
sheet1
and sthhet2 and sheet3...?

"Bob Phillips" wrote in message
. ..
Yes. Select sheet1,

InsertNameDefine...

then in the 'Names in workbook' put Sheet1!game, then add the range in
the
Refersto box.

Repeat for sheet2, etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
Can you have sheet specific named ranges?

ie
sheet1!game, sheet2!game, sheet3!game


so they can be referenced in a lookup, pulling values from the
correct
sheet.

thanks