Add Dynamic Named Range Via Code
Nov 30, 2007
I am using follwoing piece of code for offset function. For Some reason it is not working.
Reference = Cells(TableRowNumber, TableColumnNumber).Address
Col = Application.WorksheetFunction. CountA(rngF.Columns(TableRowNumber, TableColumnNumber).EntireColumn)
ActiveWorkbook.Names.Add Name:=TableName, RefersToR1C1:= _
"=OFFSET(Reference,0,0,Col-3,1)"
Col value is also being calculated to zero. TableRowNumber and TableColumnNumber are the variables which store the starting location of the column.
View 2 Replies
ADVERTISEMENT
Oct 13, 2007
I am employing code to label dynamic ranges that takes the form of,
ActiveWorkbook.Names.Add Name:="dms", RefersToR1C1:="=OFFSET(DMS!R10C5,0,0, COUNTA(DMS!C5),COUNTA(DMS!R10))"
'dms_j
ActiveWorkbook.Names.Add Name:="dms_j", RefersToR1C1:="=OFFSET(DMS!R11C10,0,0,MATCH("" * "",DMS!C10,-1),1)"
'dms_p
ActiveWorkbook.Names.Add Name:="dms_p", RefersToR1C1:="=OFFSET(DMS!R11C16,0,0,MATCH("" * "",DMS!C16,-1),1)"
'dms_r
ActiveWorkbook.Names.Add Name:="dms_r", RefersToR1C1:="=OFFSET(DMS!R11C18,0,0,MATCH("" * "",DMS!C18,-1),1)"
'dms_t
ActiveWorkbook.Names.Add Name:="dms_t", RefersToR1C1:="=OFFSET(DMS!R11C20,0,0,MATCH("" * "",DMS!C20,-1),1)"
The dynamic ranges are getting entered but the problem is that the range "dms" overshoots by six cells into blank cells at the bottom of the table, and the rest of the ranges overshoot by ten cells into blank cell area. I have deleted, cleared and destroyed everything around the table and re-sized the active area to no avail.
View 9 Replies
View Related
Apr 3, 2008
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:
=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)
When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....
View 9 Replies
View Related
Jul 15, 2014
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
View 1 Replies
View Related
Dec 30, 2008
I need to create a dynamic named range called "DIVLOC," that corresponds to a sheet called Divloc_List in the workbook. Then, I need to take this range and perform a vlookup against it. I tried to create the range in the sheet itself (it wasn't dynamic), and then used that in the vlookup in the vba code, but I got error 2402 (I think). I'm not sure how to define the dynamic range in vba code, and then use the range in the vlookup.
View 9 Replies
View Related
Jan 4, 2009
what would cause the formula for a dynamic named range to keep changing on its own? I enter the following formula:
OFFSET('Raw Data'!$D$2,0,0,COUNTA('Raw Data'!D:D),1)
Which I save and somehow gets changed to the following formula:
OFFSET('Raw Data'!$D$2,0,0,COUNTA('Raw Data'!XFD:XFD),1)
This is happening to several named ranges I created. I’ve tried to correct this by editing the formula which seems to work but changes again. I’ve also deleted the named range and recreated it with the same change process taking place.
In case it matters the named ranges are being created on a table imported into Excel via MS Query.
View 9 Replies
View Related
Nov 6, 2009
I am trying to create a dynamic named range to select a list of text entries from a number of different columns that I am using for Data Validation. The columns have entries of variable length, none more than 30. What I am seeking to achieve is that having selected a heading in cell C3 the available list in DV dropdowns elsewhere are governed by the contents of C3.
I have created a defined name, StartPos that uses the contents of C3 to evaluate to the cell reference (e,g $H$4) of the topmost cell in the relevant column. However when I try to use it in the usual formula
=OFFSET($A$1,0,0,COUNTA($A:$A),1) formula I try
=OFFSET(StartPos,0,0,COUNTA(StartPos:[ ],1)
I get stuck at what to put in the brackets. Ideally I would like to use the COUNTA to go down to the last entry in the column. However, even when I try to give the range a height of 30 rows:
=OFFSET(StartPos,0,0,30,1) I still get an error.
View 9 Replies
View Related
Sep 13, 2007
My searches have not produced anything that I could apply to this situation.
I'm trying to write VBA that would:
1. Search a Workbook for Dynamic Ranges.
2. When a Dynamic Range is found the code would:
A. Determine the current coordinates for the range.
B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row"
3. Save Changes.
4. Close File.
My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.
View 9 Replies
View Related
Jun 18, 2013
I want to create a charts on a pivot table, but I don't like the way Pivot charts function and the limitations they have.
So I want to create a normal charts that is dynamic and based on a pivot table.
In order to do so, I want to create dynamic named ranged for the data in the pivot table to use for the data series for the chart.
I can reference the week numbers (headers in the pivot table) for one data series and get the data by this formula:
=OFFSET(Weekly!$C$15,0,0,1,COUNTA(Weekly!$14:$14)-1)
$C$15 is the first cell that holds the data for the series.
Row 14 holds the week numbers (headers in the pviot)
However, I want to make that formula even more dynamic by having it reflect the changes done to the pivot table who's cells it references to.
So how do I do this using the GETPIVOTDATA function? (or some other function that makes it dynamic and reflect changes done to the pivot table and accounts for the row numbers with the data can change)
If I generate the GETPIVOTDATA formula and try to substistute the fixed cell reference for $c$15 with it, the OFFSET formula for the dynamic range gives an error.
And how can I grab the column headers (week number) dynamically?
View 1 Replies
View Related
Feb 25, 2014
I am doing an Advanced Filter, and I am using a named range in my VBA code that decides the Criteria range.
The dynamic named range is called 'Criteria' and this is the code:
VB:
=OFFSET(Sheet1!$A$2,0,0,Sheet1!$BZ$23,41)
However, when I run the VBA code, although it works perfectly, afterwards the named range has changed to:
VB:
=Sheet1!$A$2:$AO$3
I just want this to remain unchanged, as it works great. This is my VBA code:
VB:
'Filter and send data to new sheet
Sheets("Sheet2").Select
Cells.Select
[Code]....
View 4 Replies
View Related
Jul 23, 2014
I know how to create a dynamic named range so that no blanks appear in my data validation list but have only ever done this for a list in a column, i.e...
-----A-----
1--Apple
2--Orange
3--Pear
4--Bannana
5--
6--
A1:A6 would be named Fruit, then name manager, edit: =OFFSET($A$1,0,0,COUNT($A1:$A6),1)
The Data Validation list then shows Apple, Orange, Pear, Bannana. And if I add Peach into A5 later, that then appears in the list.
What I need though is to edit this as if the named range 'Fruit' covers cells A1:F1.
------A---------B-------C--------D-----------E-----------F
1--Apple---Orange---Pear---Bannana---
2--
View 7 Replies
View Related
Mar 4, 2014
create a dynamic named range with dates.
Example file. !
Dynamic-test-sheet.xlsx
I need an dynamic named range for column B(Project) with a date criteria.
so for example: I need a range with all records from Colomn B(Project) from year 2013.
I want to use this range to filter all unique projects by year.
I have already a filter/formula to collect a unique list of all projects.
View 9 Replies
View Related
Aug 16, 2013
I have a named range in a column for January data (non-contiguous cells). Every month I insert new data in the column to the right. Is there a way to have a dynamic range that grabs the same exact cells but in the new column but unselects the previous month data? Spreadsheet attached.
View 1 Replies
View Related
Dec 13, 2009
In the attached workbook, I have identical sets of data in column A separated by an blank cell. I need a macro for the user form that i need to build. The code needs to find the end of each named range, (Range1,Range2 and Range3), to insert blank row at the end of each range and to copy the value of the textbox from the user form in each row so to keep the sets of data identical.
View 9 Replies
View Related
Jan 13, 2010
My file has a code that refers to a dynamic named range. When I run the macro that range gets corrupted and I cannot figure it out why. before the macro the named range refers to:
=OFFSET(Manager!$A$1,0,0,MATCH("*",Manager!$A:$A,-1),3)
and right after I run the macro it turns to:
=OFFSET(Manager!#REF!,0,0,MATCH("*",Manager!$A:$A,-1),3)
View 2 Replies
View Related
Oct 20, 2011
I have dynamic named range that lists by columns. I can use this with data validation pull down on my worksheet and all is fine. But when I use this named range in the RowSource of a combo box in a userform, I only get the first column of data, or basically, the data in B1, but not C1, D1, ect. Here is the formula for the named range.
=OFFSET(INFO!$B$1,0,0,1,COUNTA(INFO!$1:$1)-1)
View 2 Replies
View Related
Feb 7, 2013
How can I retrieve the values from a dynamic named range with VBA?
Let's say we have created a dynamic list with the OFFSET function, how can I get the values of that dynamic range?
View 9 Replies
View Related
Jun 1, 2007
I have a sheet containing three drop down boxes which gives me a count of data in a table matching the criteria. (Between 2 dates and equals a depot name).
I am trying to create a chart to only show this data, but I don't want to do it using a pivot table as the end user has no understanding of them and just wants to fill in drop down boxes.
I am beginning to think that using a Dynamic Named Range might be the answer. I believe I am able to achieve what I want with this process but I am not entirely sure how to do it.
View 11 Replies
View Related
Sep 13, 2009
In the following line of code in my subroutine I name this range
which runs from A2:J2, the problem is later in the routine I delete certain unwanted columns which then shrinks this range. I always want it to be 10 colums wide. How do I make it dynamic with vba?
ActiveWorkbook.Names.Add Name:="TranslateShow", RefersTo:=Worksheets("TemplateTest").Range("$A$2:$J$2")
Can I also substitute the workbook actual name for ActiveWorkbook?
View 9 Replies
View Related
Oct 15, 2009
When I make a dynamic named range, I typically do something like:
=offset($A$1,,,counta(A:A),counta(1:1))
I remember awhile ago I saw (within some of the comments on Jon Peltier's site, I believe) a way to make a dynamic named range without using offset. Does anyone know how to do it?
View 9 Replies
View Related
Jan 4, 2010
Anyone run across this.....Over the past 1-2 years...I have setup a number of complex excel workbooks, that automate a lot of reporting for my employees. Suddenly, over the past couple months, I am finding that if I try to go back and edit many of my named ranges (created a while ago) that were created dynamically: "=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A$4:$A$65000),4)"...excel crashes. I can't figure out why.
All I have to do is simply CLICK in the "Refers to:" field of the "Define Name" window (for the dynamically named range)....and I get "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience."
If I drill down on "What is in the report", I see this "Error signature":..
View 9 Replies
View Related
Jul 19, 2007
How can I DISABLE dynamic named ranges ACROSS worksheets? Is there an option or code?
View 3 Replies
View Related
Feb 6, 2008
I have a Defined Dynamic Name which is used to select my data. How do I call that when I create a chart in excel within the data source, so that the data source always refers to this dynamic range.
I have data A B C D E
1 2 3 4 5
9 4 5 6 7
this is so the series names will be the row and for each series the rows represent the data within each of the columns. I have a dynamic range that always selects the whole range called chartdata. How do I put that in the data source when it asks me in Excel 2007 as there is no wizard for seprate x and y ranges in this version.
View 4 Replies
View Related
Jun 17, 2008
I need to populate a cell with a dropdown list via data validation. The source is a list that contains blanks both in between data and at the end of the list. Like this:
John
Peter
(BLANK)
Ann
(BLANK)
Carol
(BLANK)
(BLANK)
(BLANK)
etc.
The blank cells are actually not empty, but contains formulas (and I therefore assume I cannot use COUNTA for leaving them out).
Anyone knows how to create a source list for the data validation dropdown list that leaves out any blanks (containing formulas) in the middle and the end of the range?
View 4 Replies
View Related
Dec 30, 2013
I have a Dynamic Named Range (Entries) that shows in my Name Manager...but is not being recognized in formulas.
The range is defined with a standard DNR formula, and selecting the code in Name Manager highlights the Dynamic Range.
[Code].....
But, when I use the NAME in a simple formula, I get an error. (#NAME?)
[Code] .....
View 6 Replies
View Related
Jan 14, 2013
I have some raw data, and in that data are 2 columns. They are Manager and Role. I am trying to create 2 lists, that correspond to their role. I can create a dynamic named range from the column no problem using this formula:
Formula:
[Code] .....
Now, what I need to be able to do is, add a criteria option to the formula. So build the list off of column B, but only if their role (in column C) is Perm, or Temp.
See the attached spreadsheet : dynamic range criteria.xlsx‎
View 8 Replies
View Related
Oct 7, 2008
I used each of the following codes for dynamic name ranges.
View 4 Replies
View Related
Sep 9, 2011
I'm setting something that allows the user to compare bond yield curves on a graph. The setup involves a dropdown to select the final year of data the user wants to see and several date drop-downs.
For example, if the user wants to compare the current yield curve out to 2031 against how the same curve looked the end of Q1 2011 and Q2 2011, he would select "2031" in the first dropdown and then select "4/1/2011", "7/1/2011", and "9/8/2011" in the other three dropdowns.
The data is on a separate sheet, and mechanics of my named ranges are sound. However, Excel won't allow me to add my named ranges to my graph.
As is, the series looks like this:
=SERIES($L$5,'2011'!$B$3:$B$25,'2011'!$C$3:$C$25,1)
I'm trying to replace $B$3:$B$25 with CRV_DATES and $C$3:$C$25 with CRV_ONE (when I get this to work I'll add the other curve to the chart). When I try to add them, though, I can't get out of the series dialogue. When I hit enter, nothing happens--no error message, no nothing. The change simply won't "stick".
Here are my primary named ranges and the ranges/functions I use within them:
CRV_DATES: =OFFSET('2011'!$B$3,0,0,FindCurve,1)
CRV_ONE: =OFFSET('2011'!$C$2,1,MATCH(DATE_ONE,StartDateRange,0)-1,FindCurve,1)
FindCurve: =MATCH(CRV_END,MaturityRange,0)
StartDateRange: =OFFSET('2011'!$C$2,0,0,1,COUNTA('2011'!$2:$2))
MaturityRange: =OFFSET('2011'!$B$3,0,0,30,1)
CRV_END: =Sheet1!$L$3
View 2 Replies
View Related
Sep 25, 2011
I have a sheet containing 2 lists of categories for income and expenses. These lists are named inc and exp, respectively, and are contained in a sheet called "Lists." They are dynamic ranges, meaning every time a value is added, the named range adjusts itself to include the new value(s). [=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)]
I have 2 tables summarizing 12 months of data. The tables use sumif to find all occurrences of each category and sum them. There's 1 table for income, 1 for expenses. Each is a 2 column table, with all the categories for income in column A and their total for 12 months in column B. Same for Expenses in D & E.
To populate the categories in column A & D, I am currently using the simple =Lists!A1 =Lists!A2 (inc range), =Lists!B1 =Lists!B2 (exp range) and so on... the problem is when a value is added to either list, while the named range adjusts, I have to manually drag the formulas in Column A & B down 1 more cell to include the new value.
How can I use VBA to look for the inc and exp ranges (which will change in size), then populate each table with the most recent categories?
View 9 Replies
View Related
May 31, 2012
I have created a chart that provides a hisotry of events on a timeline. It relies on named ranges and allows me to change the upper and lower date ranges displayed on the x-axis of the chart by changing the date in cells that define the upper and lower dates. In order to display the events on the timeline I have a table that includes 3 columns... a date colmn, an event description column, and a event height column. To display the information I have used a line chart with data points that uses the "event height" data to determine how high up on the graph to display the event information. I have hidden the line, added a label for each event, and added an error bar that draws a line between each label and the bottom of the chart. By default the labels displayed the "event height" information (as expected), so I selected each label individually and added a formula that causes it to display information from the "event description" column instead of the default value. All of this works perfectly with no issues.
The problem comes when I change the date range displayed in the chart. Currently the chart displays event information for 1/1/2012 - 5/30/2012. I can modify 5/30/2012 to be any date I would like that is greater than 1/1/2012 with no problem. Everything displays correctly. However, if I modify the 1/1/2012 date the labels change to reflect the original value (event height data) instead of what I modified it to (event description).
[URL]
View 1 Replies
View Related
