Data Validation List With Changing Data
Jun 3, 2009
I would like to utilize data validation to create a drop down list from a list of 100 rows that changes monthly. Currently the list is set to draw data from A1:A100, however depending on the month, different cells will have data in them. What I don't like is currently the blank cells create gaps in the data validation drop down list and make it longer than it needs to be. Is there any way for excel to automatically just display cells with data in them in the drop down?
View 9 Replies
ADVERTISEMENT
Nov 28, 2012
I would like to find out how can I create a chart, where it is interacting with the selection of 3 drop down list (created from data validation-list option). It will show up to 12 months of data.
First list would have only Male & Female, 2nd list would have 1st Class, 2nd Upper, 2nd Lower, 3rd Class and Pass Only, 3rd list would have all the months of 3 years.
My data is from J9:U22. These data needs to be changed accordingly, reading from D45:U58.
For the graph, if I select Male, 2nd Upper, and Jun-2012, I want it to show only this selection, where the period will be from Jul-2011 till Jun-2012.
Previously I have done one, with only 1 selection of drop down list, where I used Vlookup. Now involves 3 criterias.
Or is there an easier way to do it?
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
Mar 28, 2009
how i am able to change scenarios using data validation. e.g I have 3 scenarios, i want to be able to show different scenarios using the data validation button. Also in the 2003 excel version i am able to have a scenario toolbar shortcut, how can i have this toolbar shorcut in excel 2007?
2008 Jan Feb Mar.... 2009 Jan Feb Mar..... 2010 Jan Feb Mar.....................
eggs 15 25 35......... eggs 8 11 15............ eggs 96 15 6......................
milk 10 15 20............ milk 65 32 36........... milk 100 36 36......................
butter 5 20 1............ butter 8 18 15......... butter 8 5 45.....................
i want to be able to change values for each year using data validation/drop down list?
1 last question i also want to know how i can circle certain data for training purposes.
View 9 Replies
View Related
Sep 26, 2013
I'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.
For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?
View 5 Replies
View Related
May 28, 2014
I want to load the actual data exist in the rows by selection value from the data validation list.
E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.
On the second sheet, i made a data validation list of all the person names
Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.
View 13 Replies
View Related
Jan 12, 2010
Is there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?
View 3 Replies
View Related
Oct 27, 2009
I am trying to learn the concepts, then apply them to a set of actual worksheets. I am trying to use named ranges from one worksheet in another worksheet. The named ranges need to expand. Although I can get the add data to a dummy worksheet to work within the same worksheet and I can even get a named range to work in the foreign sheet.
My problem is, when I can end up adding the additional data to the named range, it will only add one name. I have a number of named ranges that are referenced on various worksheets. The idea is, certain named ranges should be able to expand so I donft have to type them all in. (There could be hundreds of manufacturers or models.) I would also like them to be able to sort themselves after additional names are added to the named ranges. I would also like them to auto populate as well. I am using Excel 2003 SP3 (11.8307.8221). I have enclosed the file. Drop down lists (named ranges) are on the worksheet called DROP DOWN LISTS (LOCKED) **ITfS NOT CURRENTLY LOCKED**.
The data validation issues are on the worksheet called Rev7.0 Wood (Basic Sheet.) There are other instances to use these, but I hope to learn to do them myself on the other worksheets. Of particular interest is Column f.................
View 3 Replies
View Related
Nov 1, 2006
In column A I have a list of text. There are blank lines in between the cell that actually contain text. What I am trying to accomplish is create a validation list that will give me only the cells with text in them and ignore the blanks. For example in column A1:A7 I have the following text:
John
Mike
Tony
Jake
My validation list will return those names but will also give me the blanks in between the names.
Is there any way to ignore the blanks?
View 4 Replies
View Related
Jun 28, 2009
have created a list obx using data validation. My question is, is there a way to limit the data available on the list.
Example: sheet 1
A1 A2 A3
Address A
address p
address s Date
Sheet 2
has data validation list that consists of all of A1 a named range called "address". Everything works great.
I now want to be able to limit the available choices in the list depending on what cell the user is in
example: if user is in sheet2 a1, i only want them to be able to pick address that have an "A" or "p" in cell A2 on sheet 1
I tried to acomplish this using conditional formatting, I format my named range with different colors, but the colors do not carry over to the data validation list on sheet2.
View 9 Replies
View Related
Jun 20, 2008
I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.
View 10 Replies
View Related
Jun 15, 2007
I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
View 4 Replies
View Related
Oct 28, 2011
I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?
View 5 Replies
View Related
Apr 29, 2012
I have a List of Different Fruits in Cells A1 to A5
Apple
Banana
Orange
Strawberry
Cherry
And I use data validation list in 5 different cells from Cells C1 to C5 then in every cell the list will show all the fruits,
But I want that if I select Any Fruit in cell C1 that should not be included in the remaining 4 cells, and the fruits selected in Cells C1 and Cell C2 should not be included in the remaining 3 cells and so on....
I Used the formula
=IF(C1=A1,OFFSET(A2,,,COUNTA($A$2:$A$5),1),0)
But this works fine if I select Apple in the Cell C1, then the List of C2 Shows all Fruits other than Apple, But if in Cell C1 I select any fruit other than Apple it does not work... (Using Excel2007 & Win XP)
View 4 Replies
View Related
May 12, 2004
dear....can i set width in list box data validation (width in cell i set short).thanks
View 9 Replies
View Related
Oct 18, 2006
is there a way to force so that a cell value can be only of what a validation droplist offers, hence users can not enter their own values and have to use the droplist for cell content selection.
View 2 Replies
View Related
May 15, 2009
SOLVED
Attached is my example.
I want to have the data in a Named List display automatically in a certain column, and the data be dependent on the choice in a cell with a List Validation.
Choose "option 1" in the Cell's drop-down, and in the specified area appears all of the data in the "option 1" referenced List.
View 4 Replies
View Related
Jul 16, 2009
I am using Data Validation on some fields to create a drop down list from a named range! These fields however allow you to enter values that are not in the list.
Is there a way to make the cell have to be an entry from the data validation list?
View 6 Replies
View Related
Mar 18, 2009
The idea is to create a liquor order sheet which takes items and prices from a liquor inventory workbook, which is part of the same file. i'm sure that you can do it all fancy with a macro, but unfortunately i'm not super experienced with excel. so i helped myself with what i know a bit -> drop down lists. I created an extra sheet which has 4 ranges on there... the liquor type (liquor, beer, wine), and the 3 sub classes for each type (red/white/blush/sparkling for wine, draft/import for beer and so on and so forth). On the inventory i named the range of all white wines "white", of all red wines "red" ..
On my order sheet i created a drop down list for type and created 2 more via "indirect" .. so when you select "wine" in the first one, the 2nd one will ask you for white/red/blush/sparkling and the last drop down then for the exact item according to the range on the inventory.
Hope what i did so far is understandable ^.^
Now my problem is that i want the unit cost to be copied when an item is selected. So you select "beer" in A1, "import" in A2, "corona" in A3... and the price for corona (which is written in a cell on the inventory sheet) should show up in A4.
I attached a screen of the inventory sheet so that you can see how it is structured.
View 12 Replies
View Related
Sep 9, 2009
Hello, could some one please help me with the following:
I have created a drop-down list for the range : C3:C65000 using Excel 2003 and Windows XP Pro. I did this using Excel's Data Validation Tool. The settings I used are as follows:
Allow: List
Source: =GROUPS
Check Box Ignore Blank is Ticked
Check Box In-cell dropdown is Ticked
GROUPS = GROUPS!$A$2:$A$29
Now all this works well, such that when I click in the cell range : C3:C65000, then a dropdown list appears - showing a list of all my groups.
However, when I click on any of the dropdown lists in this range, the width of the dropdown list is only as wide as the column. Column C has a width of 20.
What I need please is for the dropdown list to be as wide as the list of information showing in the dropdown list. If some one could please explain on how I can achieve this - that would be great.
View 7 Replies
View Related
Aug 29, 2008
I have a workbook that uses data validation from a list.
The same list is used in several of the individual sheets.
Can one use just one list for different sheets, as I'm having to create separate list for each sheet, which when an overall change needs to be made it is easy to miss one.
View 9 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
Jun 26, 2014
I have a cell "project titles" entered in sheet1 that populates into sheet 2 - column "Project titles".
I need to keep the values in this column unique. So can I create a data validation such a way that I won't be able to enter values into this cell that are already on the list. This way I can avoid entering duplicates.
View 3 Replies
View Related
Apr 8, 2014
I try to find a solution (with or without macro) to assign different ranges named "Failure_C..." in a data validation source depending on the value of another data validation list (32 values called)
I started in the Data Validation List Source to fill in IF function but 32 inputs are not accepted and too much caracters in the source.
The formula is starting like that:
=IF(A57=$M$78,Failure_C01,IF(A57=$M$79,Failure_C02,IF(A57=$M$80,Failure_C03,
IF(A57=$M$81,Failure_C04,IF(A57=$M$82,Failure_C05,
IF(A57=$M$83,Failure_C06,IF(A57=$M$84,Failure_C07............
View 2 Replies
View Related
Jun 29, 2014
I am trying to make a simple planning sheet.
I have a workbook with 2 sheets.
1.sheet contains Columns : DayDateTimeClassTeacherCovered
2. sheet contains the availability of teachers. so all columns start with a date and the rows contain the name.
A row therefore looks like this.
7/1
Harley
Lidia
Livia
Thais
Salete
Carla
My issue: The 1.sheet has a column Teacher, now in this column I want to have a drop down list. (E2, E3, E4, E5 ... etc..) This list is depended on the date which is in column B.
Essentially I want the list to look up date in its row go to the second sheet and give me all the possible names in it. That is all.
View 11 Replies
View Related
Feb 25, 2014
I have a list on one worksheet and I use a dropdown list from Data Validation to select the name required.
My list is so long that I would like to be able to type a letter or two to access to the name quicker instead of scrolling down.
I copied a formula that worked for someone else that put this formula in the data validation
(IF(I36<>"",OFFSET(Player,MATCH(I36&"*",Player,0)-1,,SUMPRODUCT((MID(Player,1,LEN(I36))=TEXT(I36,"0"))*1)),Player))
I do not wish to use VBA is possible.
View 1 Replies
View Related
Mar 11, 2009
I am trying to create a data validation list with a bar graph. I have tried using the IF and Vlookup formula, but its not working for me. I would like to be able to select classroom # from the drop down menu and the bar graph update selected with students name and selected data information. I have attached the file.
View 5 Replies
View Related
Feb 19, 2010
I am trying to set the below table up as a validation list. I am having issues with putting it in as list as it creates a new line every time a , (comma) occurs. I know I can use the custom and pull the list from a table, but I cant use this option (the reason I cant use the table, if interested, is because the single sheet with the validation contains about ~35,000 line items that are being split up into ~200 different sheets. Each sheet is then sent as a separate emailthe problem with using the table is that it would on a different spreadsheet to begin with. The email only sends one sheet. As such, it would no longer be able to reference the table).
A - Less than $25,000
B - Between $25,000 and $49,999
C - Between $50,000 and $99,999
D - Between $100,000 and $249,999
E - Between $250,000 and $499,999
F - Between $500,000 and $999,999
G - Over $1,000,000
View 5 Replies
View Related
May 24, 2012
I have two workbooks.
Workbook1 contains validated list which is defined by a Range name and also its data is pulled from workbook2. I am unable to change the value of the validated list from drop down when i close workbook2, infact the validation drop down list do not drop at all so that can select values from it. But if i open the workbook2 i am able to change the values of the validation list.
I want the dropdown list to be active when i close the second workbook.
View 12 Replies
View Related
Mar 6, 2013
I have a query with data validation.....
Can I have a cell rage.. say A2:E20...and every cell in it has a drop down data validation list, from data inputted into L2:L27 (For examples lets say L2 is A, L3 is B, L4 is C, L5 is D and so on).
if somewhere in the range A2:E20, say B6 I was to select D from the drop down list, I want the remainder of the cells in range A2:E20, to have the drop down list without the value D in it, and so on, so the more cells in the range I fill from the list, the list for empty cells gets smaller and smaller until there are none left.
View 4 Replies
View Related
