Data Validation For Dynamic Numbers
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
ADVERTISEMENT
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
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
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
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
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
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
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
View Related
Dec 2, 2013
Trying to set up a dynamic data validation, where each of the subsequent data validation boxes change based on the selection in the first data validation box (while maintaining 4 parameters within a cell, except for the last one).
I have used the IF-statements to determine the "Week's SUM" in H5.
What I'd like to do next is to determine a formula in the "Week's SUM" cells (H6,H7,H8) based on the choices in data validation boxes "Week #" (G6,G7,G8) with the following rules in place:
1. If I made a choice in G5 to be "1" which caused data validation in cell G6 to update, then in G6 I choose "2", the output in H6 should be just week 2's value of 20 (not the sum of week 1 and 2).
2. However, if I choose "3" in G6 (after I chose "1" in G5, as above) then I'd like the output in H6 to be the SUM of week's 2 AND 3, if that makes sense and so on. (e.g. If I chose "5" in G6, then output in H6 should be the SUM of week's 2 AND 3 AND 4 AND 5).
Same rules apply for H7 and H8. I have a feeling this may involve MATCH function and INDIRECT or SUMIFS but how to approach this.
I've attached a file : Dynamic DV and Dynamic SUMS.xlsx
View 3 Replies
View Related
Jun 23, 2014
I'm using Data Validation for various fields in a form and I'm trying to make it user friendly by adding Error Alert conditions and text explaining why the error has occurred. I also want the form to be translatable into other languages.
For some VBA message boxes that get displayed I can put the English text in some hidden cells and that can be translated easily by someone without access to the code. But I cannot figure out how to make the Data Validation Error Alert messages point to a cell so that they are also translatable without forcing the translator to open each Data Validation dialog.
View 1 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
Jul 8, 2013
I have attached a sample data sheet which i am working on . ITs a comparitive matrix trable with Input validation between 0&2 . I also want the table to be dynamic .If i want to increase or reduce no. of rows and clumns i should do it some how . More details are mentioned in the attached file .
View 1 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
Oct 2, 2007
how can I set a cell so that you can only type in 1 , 2 , 3, 4, 5
I know I can use the validation but people can just copy a cell and paste it over.
View 14 Replies
View Related
May 11, 2013
The numbers will be both negative and positive just need to catch characters.
So:
2500 is okay
-15 is okay
is not okay.
View 2 Replies
View Related
Sep 4, 2009
I currently have cell B3 in a worksheet - able to except 2 data entries.
The user should enter either OPQ or BOT followed by numbers. How do i ensure that they only enter OPQ (O - Letter Not Number) followed by numbers, or BOT (O - Letter Not Number) followed by numbers.
i.e. OPQ87654321 NOT 0PQ87654321 or BOT12345678 NOT B0T12345678. The information has to be entered manually and not via drop down list as each number is unique to the OPQ or BOT.
View 3 Replies
View Related
Apr 4, 2013
I want to create data validation so that the first 2 digits=PS and the next 4 digits will be numbers. I have tried a few things but I cant get anything to work?
View 8 Replies
View Related
Nov 22, 2006
Is it possible to force a cell entry (using Data Validation) to either a whole number or a value? In other words, I would like the data in a cell to be either a whole number between 10,000 and 20,000 or "N/A". I can do it with VBA, but Data Validation (if it's possible) would be better.
View 2 Replies
View Related
Jun 29, 2007
I want to use Data Validation on a cell (H1) to only allow the value entered to be values between values in E1 and D1. E1 is a negative number.I tried naming E1 & D1 as a range (valid) and then did custom validation formula is =valid, but that allows me to put in anything.
View 3 Replies
View Related
Feb 16, 2012
How can I require 2 numbers before a decimal in data validation? I have a formula to require at least to characters after.
I'd like to require the user to enter at least ##.##
View 9 Replies
View Related
Aug 28, 2007
to validate a cell for the following customer format, but cant get it to work:
XX00000
Where XX is any combination of letters (upper or lowercase) and
00000 is a combination of any 5 numbers.
It must always be in this format, with this number of digits in total (2 letters followed by 5 numbers)
View 9 Replies
View Related
Mar 15, 2012
I have created a template using data validations. I have a drop down in column O, which you can choose from Credit or Charge. In column V, I want:
- If selected Credit in column O, only negative numbers allowed in column V
- If selected Charge in column O, only positive numbers allowed in column V
View 2 Replies
View Related
Nov 8, 2008
I am using Excel 07 with Vista, and creating a spreadsheet in which users will be inputting a record into every row.
There is a row that requires a number to be entered, where some of the numbers will begin with 0 or 00 or even 0000. I want to make sure no-one incorrectly enters a letter, however with data validation set to whole numbers, it formats the cell to change an entry of "005667" into just "5667". There is no set length for the field.
The only way to have "005667" is the change the column to text, but then obviously that defeats the whole purpose!
View 10 Replies
View Related
Aug 19, 2009
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 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
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
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
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
