Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 12th 06, 09:47 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2006
Posts: 13
Default 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   Report Post  
Old February 12th 06, 10:39 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2003
Posts: 11,272
Default 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   Report Post  
Old February 13th 06, 03:25 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2006
Posts: 13
Default 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   Report Post  
Old February 13th 06, 03:31 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 27,285
Default 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   Report Post  
Old February 13th 06, 03:52 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2006
Posts: 13
Default 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   Report Post  
Old February 13th 06, 04:01 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2006
Posts: 13
Default 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   Report Post  
Old February 13th 06, 04:18 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 27,285
Default 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   Report Post  
Old February 13th 06, 06:10 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2006
Posts: 24
Default 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   Report Post  
Old February 13th 06, 10:53 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 129
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating dynamic ranges which are named. U0107 Excel Worksheet Functions 1 January 5th 10 11:46 PM
Named Range in Customer Header Rebecca_SUNY Excel Discussion (Misc queries) 1 April 13th 09 10:37 PM
Multi-Column Dynamic Named Range...Is there an easier way? Ken Johnson Excel Worksheet Functions 6 April 23rd 06 01:54 AM
Using a named range in a header with VBA? [email protected] Excel Discussion (Misc queries) 5 October 27th 05 10:52 AM
Named Range in Center Header Joel Mills Excel Programming 6 January 5th 05 08:03 PM


All times are GMT +1. The time now is 05:22 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017