Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there any way to give an dynamic name for a range?
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dynamic summed range based on a variable | Excel Worksheet Functions | |||
Dynamic range name problem | Excel Discussion (Misc queries) | |||
dynamic range | Excel Discussion (Misc queries) | |||
How to dynamically reference a dynamic named range | Excel Worksheet Functions | |||
Add a Dynamic Range with 2 Conditions Q | Excel Worksheet Functions |