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

[Guest]

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!

 

[Guest]

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