Named Ranges Problem

[mutrus]

How can I have a Named Range on one sheet to be the same on another sheet

e.g Sheet 1 Range A1:B3 is named TestRange

I need Sheet2 Range A1:B3 to have the same name 'TestRange'

Thanks for help

 

[PaddyD]

insert | name | define... set testrange to equal:

=!$A$1:$B$3

 

[mutrus]

Yes thanks - I may have not asked the question correctly but I found out that to do what I wanted I had to include the sheet name before each name

ie 'Sheet1'!TestRange as name, Refers to ='Sheet1'!$A$1:$B$3
and 'Sheet2'!TestRange as name, Refers to = 'Sheet2'!$A1$B3

Thanks for the reply

 

[PaddyD]

"...I had to include the sheet name before each name "


No you don't - did you try the suggestion?

 

[FastExcel]

I would strongly advise against using defined names using the !TestRange syntax.
Excel has a serious calculation bug with these types of name (always uses the active sheet) if calculate is called from VBA. They also crash excel97 if you try to trace dependents.

You can use =INDIRECT("TestRange") if you want a 'Universal' name.

 

[PaddyD]

"Excel has a serious calculation bug with these types of name (always uses the active sheet) if calculate is called from VBA..."

Thanks Charles - hadn't come across that before.

 

[mutrus]

I appreciate all the replies and do not dispute the logic

Paddy - I tried your suggestion. I could not manange to get it to do what I wanted. I use the named ranges in a drop down menu so that the user can goto the same place on whatever the active sheet is.

If I do not use the name prefix then the range only appears to apply to the sheet that the name was defined on. By using the name prefix then it works. I do not use calculate so do not have the 'bug'