Dynamic Named Range As Validation Lists
Nov 13, 2006
In the attached example I have a column of data on worksheet 2. Extra rows will frequently be added and removed from the column. I want to use that column of data to populate a validation drop down box for a whole column of cells on sheet 1. (Target cells coloured yellow for demonstration purposes)
I don’t want the validation drop down to be full of blank rows so I set up a dynamic named range to refer to the column on Sheet2. It all works really well...but...only for cell D6 on sheet 2. It doesn’t work on any of the other target cells on sheet 2 or on sheet 1. I have been looking at this for far too long and it has begun hurting my brain.
View 2 Replies
ADVERTISEMENT
Jun 18, 2008
in a sheet I have two cells using data validation and dropdowns - the source for the first cell is a static named range - no problems. The source of the second cell is dependent on the value in the first cell and the sources are dynamic named ranges.
The dynamic ranges in cell#2 is named according to the value in cell#1 and I therefore have referenced the ranges using the INDIRECT function - but this only works with static ranges.
In the data validation source field for cell#2 I have the following formula:
=IF($A$1="",the_full_range,INDIRECT($A$1))
View 7 Replies
View Related
Jun 24, 2009
I have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-
1) Offset and match, cavet being the data must be sorted a-z
2) Have lists for each potential selection
Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?
View 5 Replies
View Related
Jan 21, 2008
I have a spreadsheet where I keep all records of all the rock climbing I have done.
I am after a way of choosing a climbing area either by data validation or combo box (already set up as a named range) and then the climb names available in another validation list shows only those from that particular climbing area.
From what I have seen from various web sites it seems the climbing area names need to be in a unique list (is this correct). My sheet looks like the following:
Column A | Column B (climbing area) | Column C (climb name)
Date | Mt Arapiles | Agent Orange
Date | Mt Arapiles | Auto Da Fe
Date | Morialta | Asgard
Date | Morialta | Al Sirrat
Date | Buckaringa Gorge | Agent Orange
The name in the climbing area column will be repeated often.
The climb name should be unique to each climbing area, but the climb name my be repeated in another climbing area.
The climbing areas are anamed range on a separate worksheet.
The details of the climbs has all the details of the climb i.e. the Climb Name; Climbing Area (VLookup); Grade ; Distance ; Single or Multi Pitch ; Lengths of each Pitch.
I already have a working example of the spreadsheet (220KB) but it currently works backwards i.e. I use validation to find the climb name and then this uses the INDEX function to return the climbing area.
I would like to be able to choose the climbing area and then the next available choices would be limited to that climbing area.
View 3 Replies
View Related
Jul 28, 2014
Is there a way to add cascading lists (from data validation or form/activex controls) to my excel spreadsheet WITHOUT using named ranges? Maybe structured references?
I need to avoid the named ranges because it will cause my workbook to have duplicate named range titles which I cannot avoid.
As a general example my issue arises because I have something like this where the titles are the same but they map to slightly different data. These also have to be cascading because the titles align to another list which I do not show in the example. I also considered using pivot tables, but the issue there is that the data validation lists repeat in the same worksheet. So I would have 3 cascading lists in row1 dependent on each other, but the same 3 lists in row2 dependent on row2 but not the previous row.
[Code] .....
View 3 Replies
View Related
Nov 10, 2008
I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is
Public Sub assignDVList(WSD As Worksheet, sListName As String)
Dim DVListName As String
DVListName = "DV" & sListName
Application.Goto Reference:=sListName
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & DVListName
It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.
View 4 Replies
View Related
Nov 24, 2008
I have a question regarding dynamic validation. I have attached a sample spreadsheet to clarify if my explanation doesn't. I have a list of codes that map to a list of other codes that are not one to one mapping. I would like for the other users of this code to populate what the correct code on our side is but without having to go thru the entire list in a validation field. (if that makes since)
Using validation before in a similar instance i used indirect to grab a named range to help shorten the list for them. However in this case that would be a ton of ranges i would have to set up! I've tried Match, countif and index in an offset formula in the validation list but can never get it to work!
View 2 Replies
View Related
Aug 7, 2014
I am unable to use dependent data validation lists using the INDIRECT function when the initial named range is a dynamic one.
See attached.
The named ranges 'Men','Women','Children' are all dynamic based on number of entries in each column order to accommodate a growing list, whilst also not having blanks in the dropdown (hence I haven't used entire column ranges). The named range 'test' is a static one.
Column H has a dependent data validation based on entry in Col G. This works for the static list, but not the dynamic one!
View 4 Replies
View Related
Sep 25, 2009
I can use a dynamic named range and a direct reference to the name and the DV works.
I can use a standard named range (not dynamic) and an INDIRECT() reference to a cell with the name of the range in it and the DV works.
But if I try to use a dynamic named range and an indirect reference to a cell with the name of the range in it, the DV fails.
Any workarounds out there? This project includes a LOT of DV lists, getting them to self-maintain is important if I can do it.
View 12 Replies
View Related
Dec 22, 2009
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
View 9 Replies
View Related
Dec 21, 2007
There are 4 lists containing accounting data and within each list the data rows beneath the headers are named ranges.
The parent input list is on the Payments worksheet with headings in A3:R3 and the data rows underneath are a named range entitled Payments.
The child lists contain formulas to pull the required information from the parent Payments list. These lists are identically sized and headed with headers in A1:K1. The data rows underneath the headings are named ranges entited Debits, Credits and VAT. The worksheets on which each resides have the same names.
I am looking for a way to automatically contract / expand the child lists as each row is added / deleted from the parent Payments list, ie so each list always contains the same number of rows.
The formulas already within the child lists will then ensure that the apprpriate data is copies to them from the parent Payments list.
the named range Payments will always contain at least a single row to ensure the retention of the required formulas and validation.
Note also please that there is a macro which automatically resizes the range Payments to a single row when a command button is pressed.
View 3 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
Apr 19, 2013
I have data that resembles (I have also attached an example workbook as well if that is easier)
Column A.................Column B................Column C..................Column D.............Column E................Column F
Year.......................Quarter...................Month..................Product-Code...........[data A]................[data B]
2013.........................Q1.........................Jan........................SD-21...................13%......................0.05%
2013.........................Q2..........................Apr........................NV-12...................7%.......................6%
etc
I am hoping to have, on another sheet, a series of dependent drop boxes- Year, Quarter, Month, SKU- which after selecting then displays 'dataA' & 'dataB'.
So you could select- List 1: 2013 ----> List 2: Q1 ----> List 3: Jan ----> SD-21.................and then 13% and 0.05% are displayed.
The data will be continuously added to and so needs to be based on a dynamic data range.
I know that a pivot table is the perfect way to do this, however my bosses have requested that I do not use that format so it is easy for all staff to access.
So I think I need to construct some sort of dashboard sheet, I have experimented with OFFSET from other posts, but so far have had no joy.
View 14 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
