Name a range

  • Thread starter Thread starter spence
  • Start date Start date
S

spence

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
 
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
 
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.
 
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
 
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?
 
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
 
Back
Top