Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Named Range Using Sheet Name and Column Header
Is their a simplified method of creating a dynamic range using the
column header and the sheet name. For example suppose that in cell A2 I have a column header such as Date Machined, then beneath A2 I have a number of cells containing dates, obviously creating a dynamic range using the Insert Name Define from cell A2 would be as written below =OFFSET(Sheet1!$A$2,1,0,COUNTA(Sheet1!$A:$A)-1,1), the range name in this case would be Date_Machined I have a number of columns with column headers that I'd like to apply a dynamic range but also using the sheet name, let's say the above example would be Date_Machined_Sheet1 To simply things if I select the appropriate column then run the macro it would suffice nicely. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Named Range Using Sheet Name and Column Header
Dim sName As String
sName = Cells(1, ActiveCell.Column).Value ThisWorkbook.Names.Add Name:=sName, _ RefersTo:="OFFSET(" & sName & "_Sheet1!" & Cells(2, ActiveCell.Column).Address & _ ",1,0,COUNTA(" & sName & "_Sheet1!" & _ ActiveCell.EntireColumn.Address & ")-1,1)" -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "burl_rfc_h" wrote in message oups.com... Is their a simplified method of creating a dynamic range using the column header and the sheet name. For example suppose that in cell A2 I have a column header such as Date Machined, then beneath A2 I have a number of cells containing dates, obviously creating a dynamic range using the Insert Name Define from cell A2 would be as written below =OFFSET(Sheet1!$A$2,1,0,COUNTA(Sheet1!$A:$A)-1,1), the range name in this case would be Date_Machined I have a number of columns with column headers that I'd like to apply a dynamic range but also using the sheet name, let's say the above example would be Date_Machined_Sheet1 To simply things if I select the appropriate column then run the macro it would suffice nicely. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Named Range Using Sheet Name and Column Header
Bob,
I tried your solution, I think it's really close with a few tweaks (sorry).... Firstly, ThisWorkbook.Names.Add Name:=sName, _ how can I get it to include the sheet name for example suppose A2 has Date Machined as the column header and the sheet name is Sheet1 then the range name would be Date_Machined_Sheet1 as a suggestion, do you think it would be best to set the worksheet name to a variable, then call this out with sName. Secondly, when I run the macro as written it includes the quotation marks around the formula ="OFFSET(Sheet1!$A$2,1,0,COUNTA(Sheet1!$A:$A)-1,1)" only after removing the quoation marks does the dynmanic range work. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Named Range Using Sheet Name and Column Header
Dim sName As String
sName = Cells(1, ActiveCell.Column).Value ThisWorkbook.Names.Add Name:=sName & "_Sheet1", _ RefersTo:="=OFFSET(" & sName & "1!" & Cells(2, _ ActiveCell.Column).Address & _ ",1,0,COUNTA(" & sName & "!" & _ ActiveCell.EntireColumn.Address & ")-1,1)" -- Regards, Tom Ogilvy "burl_rfc_h" wrote in message oups.com... Bob, I tried your solution, I think it's really close with a few tweaks (sorry).... Firstly, ThisWorkbook.Names.Add Name:=sName, _ how can I get it to include the sheet name for example suppose A2 has Date Machined as the column header and the sheet name is Sheet1 then the range name would be Date_Machined_Sheet1 as a suggestion, do you think it would be best to set the worksheet name to a variable, then call this out with sName. Secondly, when I run the macro as written it includes the quotation marks around the formula ="OFFSET(Sheet1!$A$2,1,0,COUNTA(Sheet1!$A:$A)-1,1)" only after removing the quoation marks does the dynmanic range work. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Named Range Using Sheet Name and Column Header
tom,
The formula is now correct, thank you. However, the range name is still not right. In the following code you can see that my sheet name is "MSG", in cell A2 I have "test_here", for the actual range name the macro named it "_MSG, it should have been "test_here_MSG", what do you think is wrong? Dim sName As String sName = Cells(1, ActiveCell.Column).Value ThisWorkbook.Names.Add Name:=sName & "_MSG", _ RefersTo:="=OFFSET(" & sName & "MSG!" & Cells(2, _ ActiveCell.Column).Address & _ ",1,0,COUNTA(" & sName & "MSG!" & _ ActiveCell.EntireColumn.Address & ")-1,1)" Regards Burl |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Named Range Using Sheet Name and Column Header
Tom,
I substituted the actual sheet name in the following code, the formula is now correct, but the range name only came up with "_MSG", in cell A2 I had the following text "test here", so therefore the range name should have been "test_here_MSG", (I used to quotation marks in the preceeding example just to hightlight the area of concern) Dim sName As String sName = Cells(1, ActiveCell.Column).Value ThisWorkbook.Names.Add Name:=sName & "_MSG", _ RefersTo:="=OFFSET(" & sName & "MSG!" & Cells(2, _ ActiveCell.Column).Address & _ ",1,0,COUNTA(" & sName & "MSG!" & _ ActiveCell.EntireColumn.Address & ")-1,1)" Thanks Burl |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Named Range Using Sheet Name and Column Header
If you want it dynamic based on the name of the active sheet
If I run this Sub abc() Dim s1 As String Dim s2 As String Dim sName As String s1 = ActiveSheet.Name s2 = "'" & s1 & "'" sName = Cells(2, 1).Value ThisWorkbook.Names.Add Name:=sName & "_" & s1, _ RefersTo:="=OFFSET(" & s2 & "!" & Cells(2, _ ActiveCell.Column).Address & _ ",1,0,COUNTA(" & s2 & "!" & _ ActiveCell.EntireColumn.Address & ")-1,1)" Debug.Print sName & "_" & s1, ThisWorkbook.Names( _ sName & "_" & s1).RefersTo End Sub I get Test_Here_MSG =OFFSET(MSG!$E$2,1,0,COUNTA(MSG!$E:$E)-1,1) A2 contains "Test_Here (you can't have a space in the name) The activecell was in Column E In your original request, I believe you said you wanted to the beginning of the name from the cell in the first row of the column containing the activecell. This modification looks specifically at A2 as you now state. -- Regards, Tom Ogilvy "burl_rfc_h" wrote in message oups.com... Tom, I substituted the actual sheet name in the following code, the formula is now correct, but the range name only came up with "_MSG", in cell A2 I had the following text "test here", so therefore the range name should have been "test_here_MSG", (I used to quotation marks in the preceeding example just to hightlight the area of concern) Dim sName As String sName = Cells(1, ActiveCell.Column).Value ThisWorkbook.Names.Add Name:=sName & "_MSG", _ RefersTo:="=OFFSET(" & sName & "MSG!" & Cells(2, _ ActiveCell.Column).Address & _ ",1,0,COUNTA(" & sName & "MSG!" & _ ActiveCell.EntireColumn.Address & ")-1,1)" Thanks Burl |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Named Range Using Sheet Name and Column Header
Tom,
I ended up changing the sName callout from sName = Cells(2, 1).Value to sName = Cells(2, ActiveCell.Column).Value Then everything worked fine so long as no spaces existed in the column header, lastly I modified the dynamic formula a little, see code below. Many thanks for hepling me out on this one, it will save a great deal of time. Regards Burl Dim s1 As String Dim s2 As String Dim sName As String s1 = ActiveSheet.Name s2 = "'" & s1 & "'" sName = Cells(2, ActiveCell.Column).Value ThisWorkbook.Names.Add Name:=sName & "_" & s1, _ RefersTo:="=OFFSET(" & s2 & "!" & Cells(2, _ ActiveCell.Column).Address & _ ",1,0,COUNT(" & s2 & "!" & _ ActiveCell.EntireColumn.Address & "),1)" Debug.Print sName & "_" & s1, ThisWorkbook.Names( _ sName & "_" & s1).RefersTo |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Dynamic Named Range Using Sheet Name and Column Header
Just thought I'd mention what everyone probably already knows - but it
hasn't been said. Once you create a range you can extend it by adding a new interior row or column. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating dynamic ranges which are named. | Excel Worksheet Functions | |||
Named Range in Customer Header | Excel Discussion (Misc queries) | |||
Multi-Column Dynamic Named Range...Is there an easier way? | Excel Worksheet Functions | |||
Using a named range in a header with VBA? | Excel Discussion (Misc queries) | |||
Named Range in Center Header | Excel Programming |