Name a range
 

on sheet 2 cell J6 i have the text B7 and in J7 i have
the text L10. i need a macro to name range("B7:L10")
with the text that is in cell J8. J8's text
is "RangeName" i want it to name the range that the text
in the two cells specifies, because the value in cells J6
and J7 will change periodically, i just don't want to
have to keep re naming a bunch of ranges every month. TIA

Name a range
 

Do
insert=Name

Name: RangeName
RefersTo: =INDIRECT(Sheet1!$J$6&":"&Sheet1!$J$7)

If you change the values in J6 and/or J7, the definition of RangeName will
be changed as well.

Regards,
Tom Ogilvy


spence wrote in message
...
on sheet 2 cell J6 i have the text B7 and in J7 i have
the text L10. i need a macro to name range("B7:L10")
with the text that is in cell J8. J8's text
is "RangeName" i want it to name the range that the text
in the two cells specifies, because the value in cells J6
and J7 will change periodically, i just don't want to
have to keep re naming a bunch of ranges every month. TIA

Name a range
 

i have this in A1 'Sheet1'!B7:L10 when i Do
insert=Name=define
select the name from the list (that already exists as a
cell reference) i typed, =INDIRECT('Sheet1'!A1) and it
doesn't work.
-----Original Message-----
Do
insert=Name

Name: RangeName
RefersTo: =INDIRECT(Sheet1!$J$6&":"&Sheet1!$J$7)

If you change the values in J6 and/or J7, the definition
of RangeName will
be changed as well.

Regards,
Tom Ogilvy


spence wrote in message
...
on sheet 2 cell J6 i have the text B7 and in J7 i have
the text L10. i need a macro to name range("B7:L10")
with the text that is in cell J8. J8's text
is "RangeName" i want it to name the range that the
text
in the two cells specifies, because the value in cells
J6
and J7 will change periodically, i just don't want to
have to keep re naming a bunch of ranges every month.
TIA


.

Name a range
 

In A1 on sheet1 put

Sheet1!B7:L10 (no single quotes)

Insert=Name=define

Name: RangeName
RefersTo: =Indirect(Sheet1!$A$1)

include the dollar signs, no single quotes.

Works for me.

Regards,
Tom Ogilvy


spence wrote in message
...
i have this in A1 'Sheet1'!B7:L10 when i Do
insert=Name=define
select the name from the list (that already exists as a
cell reference) i typed, =INDIRECT('Sheet1'!A1) and it
doesn't work.
-----Original Message-----
Do
insert=Name

Name: RangeName
RefersTo: =INDIRECT(Sheet1!$J$6&":"&Sheet1!$J$7)

If you change the values in J6 and/or J7, the definition
of RangeName will
be changed as well.

Regards,
Tom Ogilvy


spence wrote in message
...
on sheet 2 cell J6 i have the text B7 and in J7 i have
the text L10. i need a macro to name range("B7:L10")
with the text that is in cell J8. J8's text
is "RangeName" i want it to name the range that the
text
in the two cells specifies, because the value in cells
J6
and J7 will change periodically, i just don't want to
have to keep re naming a bunch of ranges every month.
TIA


.

Name a range
 

my mistake on the last post, the ROR formula wasn't an
array
-----Original Message-----
In A1 on sheet1 put

Sheet1!B7:L10 (no single quotes)

Insert=Name=define

Name: RangeName
RefersTo: =Indirect(Sheet1!$A$1)

include the dollar signs, no single quotes.

Works for me.

Regards,
Tom Ogilvy


spence wrote in message
...
i have this in A1 'Sheet1'!B7:L10 when i Do
insert=Name=define
select the name from the list (that already exists as a
cell reference) i typed, =INDIRECT('Sheet1'!A1) and it
doesn't work.
-----Original Message-----
Do
insert=Name

Name: RangeName
RefersTo: =INDIRECT(Sheet1!$J$6&":"&Sheet1!$J$7)

If you change the values in J6 and/or J7, the
definition
of RangeName will
be changed as well.

Regards,
Tom Ogilvy


spence wrote in message
...
on sheet 2 cell J6 i have the text B7 and in J7 i
have
the text L10. i need a macro to name range
("B7:L10")
with the text that is in cell J8. J8's text
is "RangeName" i want it to name the range that the
text
in the two cells specifies, because the value in
cells
J6
and J7 will change periodically, i just don't want
to
have to keep re naming a bunch of ranges every
month.
TIA


.



.

Name a range
 

so if i use indirect in the name i can't use indirect
anywhere else in the workbook to refer to that range in a
formula?
-----Original Message-----
Your trying to do multiple levels of indirection -
generally that doesn't
work.

the name won't appear in the Go To dialog or in the
names box - because
Excel doesn't assume it is a range - (defined names
don't have to be
ranges). You can type in the name, however.

=sum(rangename)

will work.

If you want
=row(dynamic range)
you will have to do

=row(Indirect(Sheet1!A1))


--
Regards,
Tom Ogilvy


spence wrote in message
...
it does work, but the name does not appear in the list.
in another sheet i have a formula referring to the
named
range. the ranges name is typed out in cell O6. in
P6 i
have the formula {=row(indirect(O6))} to return the
first
row number of the named range. when i do what you
posted, the ROW formula returns a #REF error in P6.
-----Original Message-----
In A1 on sheet1 put

Sheet1!B7:L10 (no single quotes)

Insert=Name=define

Name: RangeName
RefersTo: =Indirect(Sheet1!$A$1)

include the dollar signs, no single quotes.

Works for me.

Regards,
Tom Ogilvy


spence wrote in message
...
i have this in A1 'Sheet1'!B7:L10 when i Do
insert=Name=define
select the name from the list (that already exists
as a
cell reference) i typed, =INDIRECT('Sheet1'!A1) and
it
doesn't work.
-----Original Message-----
Do
insert=Name

Name: RangeName
RefersTo: =INDIRECT(Sheet1!$J$6&":"&Sheet1!$J$7)

If you change the values in J6 and/or J7, the
definition
of RangeName will
be changed as well.

Regards,
Tom Ogilvy


spence wrote in message
...
on sheet 2 cell J6 i have the text B7 and in J7 i
have
the text L10. i need a macro to name range
("B7:L10")
with the text that is in cell J8. J8's text
is "RangeName" i want it to name the range that
the
text
in the two cells specifies, because the value in
cells
J6
and J7 will change periodically, i just don't
want
to
have to keep re naming a bunch of ranges every
month.
TIA


.



.



.

Name a range
 

I would say that is true. Maybe someone else knows a workaround. Best way
to answer the question is to try it.

Regards,
Tom Ogilvy

spence wrote in message
...
so if i use indirect in the name i can't use indirect
anywhere else in the workbook to refer to that range in a
formula?
-----Original Message-----
Your trying to do multiple levels of indirection -
generally that doesn't
work.

the name won't appear in the Go To dialog or in the
names box - because
Excel doesn't assume it is a range - (defined names
don't have to be
ranges). You can type in the name, however.

=sum(rangename)

will work.

If you want
=row(dynamic range)
you will have to do

=row(Indirect(Sheet1!A1))


--
Regards,
Tom Ogilvy


spence wrote in message
...
it does work, but the name does not appear in the list.
in another sheet i have a formula referring to the
named
range. the ranges name is typed out in cell O6. in
P6 i
have the formula {=row(indirect(O6))} to return the
first
row number of the named range. when i do what you
posted, the ROW formula returns a #REF error in P6.
-----Original Message-----
In A1 on sheet1 put

Sheet1!B7:L10 (no single quotes)

Insert=Name=define

Name: RangeName
RefersTo: =Indirect(Sheet1!$A$1)

include the dollar signs, no single quotes.

Works for me.

Regards,
Tom Ogilvy


spence wrote in message
...
i have this in A1 'Sheet1'!B7:L10 when i Do
insert=Name=define
select the name from the list (that already exists
as a
cell reference) i typed, =INDIRECT('Sheet1'!A1) and
it
doesn't work.
-----Original Message-----
Do
insert=Name

Name: RangeName
RefersTo: =INDIRECT(Sheet1!$J$6&":"&Sheet1!$J$7)

If you change the values in J6 and/or J7, the
definition
of RangeName will
be changed as well.

Regards,
Tom Ogilvy


spence wrote in message
...
on sheet 2 cell J6 i have the text B7 and in J7 i
have
the text L10. i need a macro to name range
("B7:L10")
with the text that is in cell J8. J8's text
is "RangeName" i want it to name the range that
the
text
in the two cells specifies, because the value in
cells
J6
and J7 will change periodically, i just don't
want
to
have to keep re naming a bunch of ranges every
month.
TIA


.



.



.