List Data Validation Fails For Dynamic Ranges
Oct 1, 2009
I'm working on trying to maintain an inventory management sheet where i basically enter all the spare parts issued to vehicles. Now there are more than 700 parts dat form the components of a vehicle, so i decided to split this list into 12 spare groups(Gear, Engine, Body, Axle, etc etc..) and then based on wotever spare group being selected my sheet wud pull dat specific spare group list.
Since i've worked with indirect function before i thought this was a piece of cake!
However when i actually sat working on getting my sheet together i realised its not gonna be dat easy! Basically my spare group lists are a work in progress and so new items would/may be added to these lists as we start working on this sheet. So i decided to make them dynamic by using the offset function. But list validation fails when i use Indirect function on dynamic ranges!
View 2 Replies
ADVERTISEMENT
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
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
Mar 25, 2014
Can you create a Pull-down List that contains data from Multiple Ranges.
If I have a List of Names Running down column A (A2:A10) another List of Names Running Down B (B2:B25) and a Third down C (C1:C15) each of them named Ranges ("List1", "List2" & "List3"), can I create a Pull-down list in Cell A1 that would include the names from all three ranges?
View 4 Replies
View Related
Feb 26, 2008
is it possible to refer to more then one named range in my validated list's source field? If not, how can i make more than 1 named range to be the source of my validated list.
View 9 Replies
View Related
Apr 24, 2014
Is it possible to have a dependent validation drop box that under certain conditions automatically produces an actual value in the cell instead of a drop-down list to choose from? For example, if I choose “Holy Avenger” in the first drop box, and the value for that choice in the second drop box is always a “2”, can you get it to auto-populate in the cell occupied by that 2nd drop box (cell B5 in the attachment)? Or, are you restricted to using Lists only, meaning that you could create a List that only has the value “2” in it, and the user would be required to choose the “2” in the drop box?
View 2 Replies
View Related
Jul 21, 2007
I am looking for a way to define a 'Data Validated' Cell to hold a List of Dynamic Sequential Numbers - from 1 to the Value of cell A4. For example:
If cell A4 has a value of 5 the 'Data Validated' List will be 1,2,3,4,5
I have tried to 'name' the Array-Formula {=ROW(INDIRECT("1:"&A4))} and put the 'name' as the List source but without success.
View 8 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
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
Oct 27, 2013
I've set up a Named Range that refers to an array of constants, e.g. MyRange ={1,2,3,4,5}. This appears to be fine since I can output the named range to a range of cells in a row (e.g. A1:E1) and see 1,2,3,4,5 (one value in each column).
I'd like to use MyRange as the source for a Data Validation list in a cell.
Normally data validation list sources must be vertical, so I've set MyRange =TRANSPOSE({1,2,3,4,5}). This appears to be fine since I can output the named range to a range of cells in a column (e.g. A1:A5) and see 1,2,3,4,5 (one value in each row).
When I set MyRange as the source for data validation however, it resolves to an error.
why this setup doesn't work?
Workbook detailing the problem attached. Attachment 273788 NamedRangeDataValidationArrayConstansts.xlsx
View 3 Replies
View Related
Jan 30, 2007
I have a sheet (database) which has dymamic validation lists based on a another sheet called "look ups". The problem I have is When I choose any of the validation drop downs there are two blank entries at the bottom and the cursor is on the first one of these. I would like the cursor to be on the first entry in the validation list
View 7 Replies
View Related
Jan 29, 2007
i want to create dynamic validation list in excel. The logic shd be like
this. i m fetching data from SAP on the click of execute button in sheet2. at this point of time i want to create dynamic validation list in sheet2 in row "E". and data to be filled in the list will be fetched from sheet1. i have added new named range in sheet1 called"mbe"(in sheet1!A) when user will execute excel i will feel data in sheet1 cloumn A. and i have written this code in the click of execute button :
With ActiveSheet.Range("myrange")
.Validation.Delete
.Validation.Add xlValidAlertStop, , "=mbe"
.Validation.InCellDropdown = True
.Validation.IgnoreBlank = True
End With
its giving runtime error "1004" (object defined error)
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
Sep 18, 2006
I have a dynamic validation list (see attached) which will not accept the indirect function as a formula in the listbox.
Using the named range on its own works fine, but using the indirect generates an error.
View 3 Replies
View Related
Mar 2, 2010
I want to start a new thread about this issue - it was previously listed on an old thread but slightly different topic: refer[url].
The problem is I have lots of Dynamic Named Ranges using formulae such as the following:
=OFFSET(RptPg!$F$9,0,0,MATCH("*",RptPg!$B$9:$B$200,-1),1)
. These named ranges are created in the Name Manager box and the code inserted in the RefersTo: box.
The ranges seem to work well and are dynamic as required. When I click on the range selector button against the RefersTo formula fo rthe range the range is correctly highlighted.
However, the range is not selectable from the F5(GoTo) command.
This then means for certain VBA operations, the range cannot be found and returns an error (Object not defined etc)
Is there anyway I can correct this or work around this - dynamic ranges are at the heart of my large workbook and fundamental to its performance>
View 9 Replies
View Related
Aug 13, 2014
I've converted to a dynamic validation list which allows for a selection of data to be made available based on the selection of the two previous columns.
Unfortunately since creating the new validation a circulating error occurs when tabbing through the table (B) to create a new line or entry? Additionally I've found the following to occur also;
1. The payments and PO# columns converts a red shade colour?
2. The validation on Table B description column do not work while in excel online edit mode?
View 1 Replies
View Related
Apr 12, 2008
I'm trying to create a user request form. In one section of the form, the client will enter in a list of "Allowed Roles". These are roles that they have rec'd approval to use - these roles will span multiple applications. In a tab I've named Metadata, I've got defined ranges indicating the valid roles for particular applications. Want I'm trying to do is attempt to define a cell in the user form for one application that, using data validation, will present them with a drop down list of valid roles to choose. The stumbling block I'm encountering is that I need it to validate against "Allowed Roles" and the valid roles for that app.
For instance:
Allowed Roles range name could contain:
B1 B2 C1 C2 P2 P3 D1 D3 D5 M1 O1
Payroll Roles:
C1 C2 C3 C4 C5 M1 O1
The Payroll Roles (on the metadata tab) are static, not user alterable in any way, and in fact Metadata will be hidden. Allowed Roles will be entered into the form by the client themselves.
Can I use data validation to create a drop down to only allow the user to pick a value that appears in both ranges? So they couldn't select C1 (since it's an Allowed Role, and a Payroll Role), but not C3 (not an allowed roled), or D3 (not a Payroll role).
View 9 Replies
View Related
Apr 27, 2009
As part of a UserForm I am Validating and Formatting in one pass. The problem I'm having is that when the Validation fails, I get the error message, and the focus moves on to the next text box.
I want to trap the user in a loop until they have acceptable data
View 6 Replies
View Related
Feb 6, 2014
In the attached workbook I have hard-pasted data in columns A:M that's designated as an Excel Table to make it dynamic.
In the adjacent columns O:AA I have formulae to extract data from A:M based on the "Frequency" stipulated in cell AD1,
I would like to be able easily to sort the data in the variable sized range in columns O to AA in descending order of the calculated values in column Z.
Best way to do this so that the formula don't get jumbled up or the blank cells in column Z end up at the top of the sorted data?
View 2 Replies
View Related
Jun 8, 2014
I currently have two tables in one worksheet showing the sales of different region.
The problem is, when I sort the data in the table (I can't used the named ranges as it should exclude the first row which is the header), is there a way I can make it dynamic too??
**Attached, please find the example spreadsheet, I have only written the code for the first table.
View 3 Replies
View Related
Feb 15, 2014
I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
First Name
Surname
Paul
Smith
Paul
Jones
Tony
Phillips
View 1 Replies
View Related
May 8, 2007
I want to validate data in a cell, using a list of two named ranges, so that in dropdown box the items from first range and then the second one are shown.
View 7 Replies
View Related
Aug 4, 2014
Sheet A - The user input form. There is a column which needs data validation. I want it to look at Sheet B and ONLY accept values based on a column value from the other sheet
Sheet B- Contains the list we want the data validation from. One column is considered the lookup value, while the other is what we want the user to be selecting from.
Here is an example list:
Predecessor Entity Key---- Item ID
2659407------------------- SHELL - SOUTH TOWER
2659407------------------- MARKING / LABELING
2659407------------------- FOUNDATIONS & SUPPORTS
2659410------------------- UPPER HEAD - NORTH TOWER
2659410------------------- FOUNDATIONS & SUPPORTS
2659410------------------- LOWER HEAD - SOUTH TOWER
So, in Sheet 1, if one of the triggering values is 2659407, I want my data validation to ONLY Allow the first 3 values from the Item ID column.
The issue I am having is using a pretty messy formula to try and produce a dynamic range for the Data Validation to use.
Here is what works:
=INDIRECT(CONCATENATE(ADDRESS(MATCH(J4,A:A,0),2),":",ADDRESS(ROW(OFFSET(INDIRECT(ADDRESS(MATCH(J4,A:A,0),2)),COUNTIF(A:A,J4)-1,0)),2)))
where J4 is the lookup value/1st column depicted in our simple example
This is done on the SAME sheet. So I figure, just add "'Sheet Name'!" for the first argument in CONCATENATE. Unfortunately, this does not work. How to get this to work on an outside sheet?
Update:
Also tried:
=INDIRECT(CONCATENATE("'Profile Item Library'!",ADDRESS(MATCH(J4,'Profile Item Library'!A:A,0),2),":",ADDRESS(ROW(OFFSET(INDIRECT(ADDRESS(MATCH(J4,'Profile Item Library'!A:A,0),2)),COUNTIF('Profile Item Library'!A:A,J4)-1,0)),2)))
The error I get is that I cannot Reference other worksheets. However, If I type in ='Profile Item Library'!B1:B18 for the Data Validation argument, it works.
View 2 Replies
View Related
Dec 17, 2013
I need to setup a dropdown list (I already have the dropdown created) so that once a value in the list is used, it disappears from the list. Now for the monkey wrench...It need to completely re-populate once the list that is being generated from it is cleared.
I also need to copy a value from one sheet to another sheet, however, the location needs to be matched with a corresponding value from the first sheet. Hopefully it will be easier to understand within the workbook (I feel like I'm not explaining it well).
I have attached a sample copy of the workbook and what I am trying to accomplish.
Mens_Dorm_Sample.xlsm
View 2 Replies
View Related
Dec 2, 2006
I am looking for a solution for my problem: I work for a bus company, I need a spreadsheet where I will record the bus numbers into a sheet but I only want to be able to add the number once ie. error if duplicate bus entered. Also the numbers will change from time to time, old buses sold when new ones arrive. I have the following code that someone helped me with wich works fine provided you don't have to change the numbers.
=And(Or(And(E42>=121,E42<=125),And(E42>=149,E42<=156),And(E42>=320,E42<=363),And(E42>=700,E42<=799),And(E42>=800,E42<=991)), COUNTIF($B$3:$E$46,E42)<2)
View 2 Replies
View Related
May 19, 2009
I have a blank template spreadsheet that is copied every time I need to work on some data exports from another system (the data needs to be kept, hence the copying of the original blank template first).
I have set up conditional formatting using;
=MATCH($A1,range,0)
This formula is always in the entire column of row A in Worksheet1 (for the sake of argument)
Problem is in Worksheet2 where the range it's using to compare is always a different number of rows deep...it can be 70, it can be 2000. I always call the range 'interest'.
Is there anyway (in the blank template) of setting column A as a dynamic range called 'interest' so that after data import it automatically sets the range at the desired size?
View 2 Replies
View Related
Feb 4, 2010
I am using OFFSET to make dynamic named ranges- the only problem is that I'm having trouble using it with linked data-
The Situation:
-Cells A1:H500 are linked to another sheet, with each cell containing a formula like =IF(ISBLANK('C:...[workbook.xlsx]worksheet'!A1),"",'C:...[workbook.xlsx]worksheet'!A1)
-Currently only cells A1:H100 have data, but this is dynamic and will change
-I want a named range that will only select the cells with data in them...the problem is that COUNTA counts the link formula even if the cell is "blank" (because it isn't blank).
-I hate hard-coding ranges...the solution for the named range =OFFSET($A$1,0,0,500-countblank($A$1:$A$500),8) is ugly!
Any solution to making dynamic named ranges with linked data that may or may not be "" ?
View 1 Replies
View Related
Jan 24, 2008
I have a spreadsheet that I use to store my rock climbing records.
how to create Dependent Validation for this spreadsheet.
The spreadsheet has three worksheets:
1. Climbing Area
This is a unique list of various rock climbing areas.
Data in column A only
2. Climb Details
This is where all the details of the climb e.g. pitch lengths, grade, etc are recorded. Column called Climbing Area uses a named range validation from the Climbing Areas) sheet.
Climbing Area data validation in column A and Climb Name in column B, grade, pitch lengths in other columns.
3. Dates & Pitches
This is where I record the date I did the actual climb. Once again the Climbing Area uses data validation from the Climbing Area worksheet.
What I am trying to work out is how I can get the next column Climb Name to be dependent upon the previous column. For example if I select a climbing area of Mt Arapiles, I only want those climbs from the Climb Details worksheet that a recorded as being at Mt Arapiles.
Column A is date when climb done. Column B is data validation from Climbing Area. Column C is Climb Name (would like this dependent upon column B.
View 9 Replies
View Related
Feb 2, 2010
I am a personal trainer/strength coach, and i use excel to write my workout programs.
To save time in writing programs i have made lists of all of my exercises in a separate workbook. I have them all linked via data validation in drop down lists to different templates for different populations ;weight loss, injury prevention, sports performance, ect. And it has been working great!
However here is my problem, I would like to be able to return an entire list of an exercise circuit with one drop down list and have it input all the exercises in cells below the drop down list.
So for example i have three columns on a separate sheet.
Column one
Circuit A
exercise 1
exercise 2
exercise 3
Column two
Circuit A
exercise 4
exercise 5
exercise 6
Column Three
Circuit A
exercise 7
exercise 8
exercise 9
In cell A1 i want a drop down list that that has circuit A, B, C,
using the drop down list if i pick circuit A it will return exercise 1, 2, 3 in the cells A2, A3, A4, receptively.
View 9 Replies
View Related
