Is there any way to give an dynamic name for a range?

I know we can define dynamic ranges. I am just curious about the names. Is
there anyway to give it a dynamic name?

For example, if I refer to another cell which contain the year when define
some tables. Is there any way to name a range so that when the year is 2005
then the name would be "Report2005" and next year the name would be
automatically changed to "Report2006"?

I am trying to put formulas in insert - > Name - > Define - >Name in
workbook. But looks like I am not allowed to do so. I hope there is a way
other than VBA.

Thanks a lot!

Dear salut,

You can give a name dynamicaly either,

1 - By using the sheet name
2 - Or by using a value in a cell

Consider,

Private Sub Workbook_Open()
ThisWorkbook.Names.Add Name:="AnyName", _
RefersTo:="=OFFSET(sht!$A$2,0,0,COUNTA(sht!$A$2:$A$501),1)",
Visible:=True
End Sub

1 - Sheet Name

Dim DynName()
DynName = ActiveSheet.Name
Private Sub Workbook_Open()
ThisWorkbook.Names.Add Name:=DynName, _
RefersTo:="=OFFSET(sht!$A$2,0,0,COUNTA(sht!$A$2:$A$501),1)",
Visible:=True
End Sub

Regards

"salut" wrote:

> I know we can define dynamic ranges. I am just curious about the names. Is
> there anyway to give it a dynamic name?
>
> For example, if I refer to another cell which contain the year when define
> some tables. Is there any way to name a range so that when the year is 2005
> then the name would be "Report2005" and next year the name would be
> automatically changed to "Report2006"?
>
> I am trying to put formulas in insert - > Name - > Define - >Name in
> workbook. But looks like I am not allowed to do so. I hope there is a way
> other than VBA.
>
> Thanks a lot!