Can I use a name twice in a workbook, only in different sheets? That is to
say can I have Name "Area1" for "Sheet 1!$A$1:$F$10" and also have Name
"Area!" for "Sheet2!$A$1:$F$14"

I tried the above but it didn't work. I'm not sure if it can't be done or I
did something wrong.

Thanks for the help..

Yes.

When you name the range--either by typing in the namebox (to the left of the
formula bar) or by using the Insert|name dialog, make sure you include the name
of the sheet.

'Sheet 1'!Area1

(since "Sheet 1" (with the space character) requires the name be in single
quotes/apostrophes.)



"Bruce A. Julseth" wrote:
>
> Can I use a name twice in a workbook, only in different sheets? That is to
> say can I have Name "Area1" for "Sheet 1!$A$1:$F$10" and also have Name
> "Area!" for "Sheet2!$A$1:$F$14"
>
> I tried the above but it didn't work. I'm not sure if it can't be done or I
> did something wrong.
>
> Thanks for the help..

--

Dave Peterson

It has been my experience with xl2003 that when you type in the name in
either place, IF you are on the desired sheet, excel will FILL IN the sheet
name for you.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Yes.
>
> When you name the range--either by typing in the namebox (to the left of
> the
> formula bar) or by using the Insert|name dialog, make sure you include the
> name
> of the sheet.
>
> 'Sheet 1'!Area1
>
> (since "Sheet 1" (with the space character) requires the name be in single
> quotes/apostrophes.)
>
>
>
> "Bruce A. Julseth" wrote:
>>
>> Can I use a name twice in a workbook, only in different sheets? That is
>> to
>> say can I have Name "Area1" for "Sheet 1!$A$1:$F$10" and also have Name
>> "Area!" for "Sheet2!$A$1:$F$14"
>>
>> I tried the above but it didn't work. I'm not sure if it can't be done or
>> I
>> did something wrong.
>>
>> Thanks for the help..
>
> --
>
> Dave Peterson

I've never seen that behavior and I can't get xl2003 to do it now.

You aren't writing about the "refers to" area are you?

I meant the "Names in workbook" area.

Don Guillett wrote:
>
> It has been my experience with xl2003 that when you type in the name in
> either place, IF you are on the desired sheet, excel will FILL IN the sheet
> name for you.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Yes.
> >
> > When you name the range--either by typing in the namebox (to the left of
> > the
> > formula bar) or by using the Insert|name dialog, make sure you include the
> > name
> > of the sheet.
> >
> > 'Sheet 1'!Area1
> >
> > (since "Sheet 1" (with the space character) requires the name be in single
> > quotes/apostrophes.)
> >
> >
> >
> > "Bruce A. Julseth" wrote:
> >>
> >> Can I use a name twice in a workbook, only in different sheets? That is
> >> to
> >> say can I have Name "Area1" for "Sheet 1!$A$1:$F$10" and also have Name
> >> "Area!" for "Sheet2!$A$1:$F$14"
> >>
> >> I tried the above but it didn't work. I'm not sure if it can't be done or
> >> I
> >> did something wrong.
> >>
> >> Thanks for the help..
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

It only does it in the "Refers to" area, not in the "Names in Workbook"
area.



"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I've never seen that behavior and I can't get xl2003 to do it now.
>
> You aren't writing about the "refers to" area are you?
>
> I meant the "Names in workbook" area.
>
> Don Guillett wrote:
>>
>> It has been my experience with xl2003 that when you type in the name in
>> either place, IF you are on the desired sheet, excel will FILL IN the
>> sheet
>> name for you.
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Yes.
>> >
>> > When you name the range--either by typing in the namebox (to the left
>> > of
>> > the
>> > formula bar) or by using the Insert|name dialog, make sure you include
>> > the
>> > name
>> > of the sheet.
>> >
>> > 'Sheet 1'!Area1
>> >
>> > (since "Sheet 1" (with the space character) requires the name be in
>> > single
>> > quotes/apostrophes.)
>> >
>> >
>> >
>> > "Bruce A. Julseth" wrote:
>> >>
>> >> Can I use a name twice in a workbook, only in different sheets? That
>> >> is
>> >> to
>> >> say can I have Name "Area1" for "Sheet 1!$A$1:$F$10" and also have
>> >> Name
>> >> "Area!" for "Sheet2!$A$1:$F$14"
>> >>
>> >> I tried the above but it didn't work. I'm not sure if it can't be done
>> >> or
>> >> I
>> >> did something wrong.
>> >>
>> >> Thanks for the help..
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

When I select h1 and goto the name box (left of formula box) and put in joe
without sheet name or quotes the defined name.refers to box =Sheet7!$H$1
When I insert>name>define>type in Bill and enter $h$2 in the refers to box,
I get =Sheet7!$H$2
When I do the macro below I get the same result
Sub doname()
Range("h3").Name = "Sam"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I've never seen that behavior and I can't get xl2003 to do it now.
>
> You aren't writing about the "refers to" area are you?
>
> I meant the "Names in workbook" area.
>
> Don Guillett wrote:
>>
>> It has been my experience with xl2003 that when you type in the name in
>> either place, IF you are on the desired sheet, excel will FILL IN the
>> sheet
>> name for you.
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Yes.
>> >
>> > When you name the range--either by typing in the namebox (to the left
>> > of
>> > the
>> > formula bar) or by using the Insert|name dialog, make sure you include
>> > the
>> > name
>> > of the sheet.
>> >
>> > 'Sheet 1'!Area1
>> >
>> > (since "Sheet 1" (with the space character) requires the name be in
>> > single
>> > quotes/apostrophes.)
>> >
>> >
>> >
>> > "Bruce A. Julseth" wrote:
>> >>
>> >> Can I use a name twice in a workbook, only in different sheets? That
>> >> is
>> >> to
>> >> say can I have Name "Area1" for "Sheet 1!$A$1:$F$10" and also have
>> >> Name
>> >> "Area!" for "Sheet2!$A$1:$F$14"
>> >>
>> >> I tried the above but it didn't work. I'm not sure if it can't be done
>> >> or
>> >> I
>> >> did something wrong.
>> >>
>> >> Thanks for the help..
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

But the name itself does not include the sheet name--it's a global/workbook
level name unless you do the extra typing (of that sheet name) to make it a
local/sheet level name.

And for the OP, it was important to make the names local/sheet level so that the
names could be used on multiple sheets.

Don Guillett wrote:
>
> When I select h1 and goto the name box (left of formula box) and put in joe
> without sheet name or quotes the defined name.refers to box =Sheet7!$H$1
> When I insert>name>define>type in Bill and enter $h$2 in the refers to box,
> I get =Sheet7!$H$2
> When I do the macro below I get the same result
> Sub doname()
> Range("h3").Name = "Sam"
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I've never seen that behavior and I can't get xl2003 to do it now.
> >
> > You aren't writing about the "refers to" area are you?
> >
> > I meant the "Names in workbook" area.
> >
> > Don Guillett wrote:
> >>
> >> It has been my experience with xl2003 that when you type in the name in
> >> either place, IF you are on the desired sheet, excel will FILL IN the
> >> sheet
> >> name for you.
> >>
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "Dave Peterson" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > Yes.
> >> >
> >> > When you name the range--either by typing in the namebox (to the left
> >> > of
> >> > the
> >> > formula bar) or by using the Insert|name dialog, make sure you include
> >> > the
> >> > name
> >> > of the sheet.
> >> >
> >> > 'Sheet 1'!Area1
> >> >
> >> > (since "Sheet 1" (with the space character) requires the name be in
> >> > single
> >> > quotes/apostrophes.)
> >> >
> >> >
> >> >
> >> > "Bruce A. Julseth" wrote:
> >> >>
> >> >> Can I use a name twice in a workbook, only in different sheets? That
> >> >> is
> >> >> to
> >> >> say can I have Name "Area1" for "Sheet 1!$A$1:$F$10" and also have
> >> >> Name
> >> >> "Area!" for "Sheet2!$A$1:$F$14"
> >> >>
> >> >> I tried the above but it didn't work. I'm not sure if it can't be done
> >> >> or
> >> >> I
> >> >> did something wrong.
> >> >>
> >> >> Thanks for the help..
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

My old brain failed to see that......

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> But the name itself does not include the sheet name--it's a
> global/workbook
> level name unless you do the extra typing (of that sheet name) to make it
> a
> local/sheet level name.
>
> And for the OP, it was important to make the names local/sheet level so
> that the
> names could be used on multiple sheets.
>
> Don Guillett wrote:
>>
>> When I select h1 and goto the name box (left of formula box) and put in
>> joe
>> without sheet name or quotes the defined name.refers to box =Sheet7!$H$1
>> When I insert>name>define>type in Bill and enter $h$2 in the refers to
>> box,
>> I get =Sheet7!$H$2
>> When I do the macro below I get the same result
>> Sub doname()
>> Range("h3").Name = "Sam"
>> End Sub
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > I've never seen that behavior and I can't get xl2003 to do it now.
>> >
>> > You aren't writing about the "refers to" area are you?
>> >
>> > I meant the "Names in workbook" area.
>> >
>> > Don Guillett wrote:
>> >>
>> >> It has been my experience with xl2003 that when you type in the name
>> >> in
>> >> either place, IF you are on the desired sheet, excel will FILL IN the
>> >> sheet
>> >> name for you.
>> >>
>> >> --
>> >> Don Guillett
>> >> Microsoft MVP Excel
>> >> SalesAid Software
>> >> (E-Mail Removed)
>> >> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> > Yes.
>> >> >
>> >> > When you name the range--either by typing in the namebox (to the
>> >> > left
>> >> > of
>> >> > the
>> >> > formula bar) or by using the Insert|name dialog, make sure you
>> >> > include
>> >> > the
>> >> > name
>> >> > of the sheet.
>> >> >
>> >> > 'Sheet 1'!Area1
>> >> >
>> >> > (since "Sheet 1" (with the space character) requires the name be in
>> >> > single
>> >> > quotes/apostrophes.)
>> >> >
>> >> >
>> >> >
>> >> > "Bruce A. Julseth" wrote:
>> >> >>
>> >> >> Can I use a name twice in a workbook, only in different sheets?
>> >> >> That
>> >> >> is
>> >> >> to
>> >> >> say can I have Name "Area1" for "Sheet 1!$A$1:$F$10" and also have
>> >> >> Name
>> >> >> "Area!" for "Sheet2!$A$1:$F$14"
>> >> >>
>> >> >> I tried the above but it didn't work. I'm not sure if it can't be
>> >> >> done
>> >> >> or
>> >> >> I
>> >> >> did something wrong.
>> >> >>
>> >> >> Thanks for the help..
>> >> >
>> >> > --
>> >> >
>> >> > Dave Peterson
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

I understand (too well!).

Don Guillett wrote:
>
> My old brain failed to see that......
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > But the name itself does not include the sheet name--it's a
> > global/workbook
> > level name unless you do the extra typing (of that sheet name) to make it
> > a
> > local/sheet level name.
> >
> > And for the OP, it was important to make the names local/sheet level so
> > that the
> > names could be used on multiple sheets.
> >
> > Don Guillett wrote:
> >>
> >> When I select h1 and goto the name box (left of formula box) and put in
> >> joe
> >> without sheet name or quotes the defined name.refers to box =Sheet7!$H$1
> >> When I insert>name>define>type in Bill and enter $h$2 in the refers to
> >> box,
> >> I get =Sheet7!$H$2
> >> When I do the macro below I get the same result
> >> Sub doname()
> >> Range("h3").Name = "Sam"
> >> End Sub
> >>
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "Dave Peterson" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > I've never seen that behavior and I can't get xl2003 to do it now.
> >> >
> >> > You aren't writing about the "refers to" area are you?
> >> >
> >> > I meant the "Names in workbook" area.
> >> >
> >> > Don Guillett wrote:
> >> >>
> >> >> It has been my experience with xl2003 that when you type in the name
> >> >> in
> >> >> either place, IF you are on the desired sheet, excel will FILL IN the
> >> >> sheet
> >> >> name for you.
> >> >>
> >> >> --
> >> >> Don Guillett
> >> >> Microsoft MVP Excel
> >> >> SalesAid Software
> >> >> (E-Mail Removed)
> >> >> "Dave Peterson" <(E-Mail Removed)> wrote in message
> >> >> news:(E-Mail Removed)...
> >> >> > Yes.
> >> >> >
> >> >> > When you name the range--either by typing in the namebox (to the
> >> >> > left
> >> >> > of
> >> >> > the
> >> >> > formula bar) or by using the Insert|name dialog, make sure you
> >> >> > include
> >> >> > the
> >> >> > name
> >> >> > of the sheet.
> >> >> >
> >> >> > 'Sheet 1'!Area1
> >> >> >
> >> >> > (since "Sheet 1" (with the space character) requires the name be in
> >> >> > single
> >> >> > quotes/apostrophes.)
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Bruce A. Julseth" wrote:
> >> >> >>
> >> >> >> Can I use a name twice in a workbook, only in different sheets?
> >> >> >> That
> >> >> >> is
> >> >> >> to
> >> >> >> say can I have Name "Area1" for "Sheet 1!$A$1:$F$10" and also have
> >> >> >> Name
> >> >> >> "Area!" for "Sheet2!$A$1:$F$14"
> >> >> >>
> >> >> >> I tried the above but it didn't work. I'm not sure if it can't be
> >> >> >> done
> >> >> >> or
> >> >> >> I
> >> >> >> did something wrong.
> >> >> >>
> >> >> >> Thanks for the help..
> >> >> >
> >> >> > --
> >> >> >
> >> >> > Dave Peterson
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson