Multiple Dependencies For Data Validation Dropdown List In Excel - NO VBA
Feb 7, 2012
So, I need to figure out how to make the populated contents of a single dropdown box dependent upon the selected results in two other drop downs.
As there are ~35K lines in the sheet, creating Lists is impractical, and VBA is out due to client security settings.
My data sort order is: Region Name > Area # > Facility Name
I have a drop down to select the Region I want to work in.
The drop-down for Area # populates based on the selection from Region Name.
The hang-up is that the area numbers are 1-4 for each Region.
So, I can't simply populate another drop-down for Facility from the result from Area #, but it has to be based upon the results of both of the former results.
View 4 Replies
ADVERTISEMENT
Jan 2, 2012
I'm using Microsoft Excel for Mac 2011. I'm creating a drop down box from List within Data Validation. It's only letting my create a list of 17 names. My longest list is 63 names. What do I need to do to create a list that will support that?
View 3 Replies
View Related
Dec 12, 2011
I've found a nice looking formula for looking up multiple values from a Data Validation List which you can find here.
Unfortunately I dont know why im getting a #REF! error for one of my spreadsheets. Here is my Formula {=SUM(VLOOKUP(A2,D:D,{6,7,8},0))}
A2 is the Data Validation drop down list that has most of the Values (Letters & Numbers) I want to look up. D:D is where it will find the Values (Letters & Numbers) and {6,7,8} is the currency I want added up and displayed.
I've tried this formula on other spreadsheets with success, but no success with the spreadsheet im working on currently.
View 3 Replies
View Related
Jun 7, 2014
I watched a video on youtbe. And the women had three or four items in drop down box. She was using vba. When i emailed her to ask her how she did this, she kept telling me to go to her website and i was supose to figure it out on my own. I am trying to do a start up construction company. the link to her site is as follows Select Multiple Items from Excel Data Validation List - YouTube the idea of what i want to do, is how she clicks on a word and it follows in the coloumn to the right going down. This is what i need to know how to do. It is probably pretty elementary to many of you. But i build, i know how to read prints, to cut footprints for new homes etc. But when it comes to excel i am so green.
View 2 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
Aug 8, 2012
I have a validation list drop down box set up to reference data in a defined name list. I would like to be able to select multiple values from this list to appear in one cell (preferably separated by a comma). For example, the defined name list contains the following data:
Choice 1
Choice 2
Choice 3
Choice 4
I would like to be able to populate one cell with the following via the drop down list: Choice2, Choice 4
Is this possible and if so, how?
View 8 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 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
Jun 13, 2013
i created a drop down list from data validation and it's a list of names. as i select from the list of names, i just want the initials of the names to be shown, not the entire name itself.
cnf_gif.gif
cnf.gif
based on this example, what i want is to select from the list of names and only the initials are shown!
i believe its something related to custom number formats...
View 5 Replies
View Related
Mar 14, 2014
I have a very detailed spreadsheet with drop down lists. I would like to block other cells if previous cells have certain selections. I have 7 columns of data that get inputted throughout the month. These cells have drop down selections that I have created. Is there any way to create a drop down menu AND another data validation? So If a selection is made, then the next cells will be blocked, using data validation "custom"?
View 2 Replies
View Related
Jun 1, 2014
It is basically a simple sheet that lists the price (Column C) of different items (Column B) based on their Category (Column A).
I am trying to have an automated pricing sheet (on different sheet) utilizing data validation and drop down lists.
For example each row should have this, the drop down list in the first columns should list all the Categories available (This part works fine for me and i can take care of the duplicates).
Once that has been selected, i have now the option of selecting using drop down list also (in second column) the items corresponding to the Category selected in first column (Only the items that are part of that specific category should be listed in the drop down list).
Once the item has been selected, the price of that respective item should be listed automatically in the 3rd column.
View 1 Replies
View Related
Sep 11, 2013
I have giant excel spreadsheet that I am trying to clean up. I want to make one of the columns (with around 3000 rows and may of them are the same) into a drop down list. However, when I try doing so from the data validation tab, I get duplicates, triplicates, etc. of the same item.
View 1 Replies
View Related
Sep 12, 2012
I am in the process of creating a database, and have used 'Data Validation' to create drop down lists in order to obtain information quickly from the database.
I have encountered an issue where if I have a list the resembles the one shown below, the drop down list will show spaces.
This is particularly an issue for myself and my database because there is over 1,000 items in the list, and there are countless empty cells within the list. I have tried sorting the list, BUT because the database is quite large, sorting it will disrupt other functions of the database.
Ideally, I would like the drop down list to show only entries within the list that are not empty so the drop down list is entirely relevant, and more user friendly.
View 2 Replies
View Related
Mar 23, 2014
When it huge list of data for drop down list, it is too much time consume. If i type a letter, it will show all those quarry or data which start with that letter. It need not to scrolling huge list.Scrolling is barrier for use of data validation for me . I tried all dependable data validation.
View 1 Replies
View Related
Jun 30, 2008
I have a data validation in a cell that works like this : if a control cell has the value "F" it validates the input with some dates in a custom list, if not it does nothing.
The formula looks like this :
=IF(F2="F",datelist,"")
It works fine but I also want it to display the drop down list when cell F2 = F, now all i can do is type a value and it checks if it's ok.
Can I do this without using vba ? I will send this to other persons and I don't want the macro warning to pop up when they open it.
View 9 Replies
View Related
Oct 27, 2006
I want to make some of my data validation dropdown lists narrower, so they end at the length of their longest list entry.
I know NOTHING about VB and although I found some "free code" online that claims to do just what I want to do above, I have no idea how to paste it in or where to paste it in, etc.
View 9 Replies
View Related
May 2, 2014
I have a Drop-Down Menu that looks like:
BOS
PIT
COL
NYR
using List with source =Lists!$B$2:$B$9, which is just the validation list
My Table:
Team Player
BOS Bergeron
BOS Krejci
NYR Nash
... ...
My goal is to limit the end user from choosing BOS more than two times from the drop down menu
View 4 Replies
View Related
Jul 22, 2012
I have a table created that uses countifs formulas to pull data. I want the data to change based by client - so I created a drop down list with each client's name, and thought I could add a criteria to the countifs formula based on the client's name in the cell- but I get a value error.
View 9 Replies
View Related
Aug 21, 2012
I have the following function in cell (table 2 column C) where I want to find a contact name from my table TMI if 3 criteria are true (client, account, NAM) For each contact I have 1 true combination of client/account/NAM but for each client/account/Nam I can have several contacts.
{=OFFSET(TMI[[#Headers],[Contact]],MATCH(1,(TMI[Client]=$B$1)*(TMI[NAM]=C3)*(TMI[Account]=B3),0),,COUNTIFS(TMI[Client],$B$1,TMI[Account],B3,TMI[NAM],C3),1)}
The function is working well but shows only the first contact name if I have 3 contacts for the same client/account/NAM. (I pressed Ctrl+Shift+Enter)
When I press F9 on the function, it shows all the contact names! so they are somewhere.
Hence, I have copied and pasted this function into data validation / list in the source but it came up with an erreur.
Table TMI:
A B C DClient
Account
NAM
Contact
Comments
[Code]..
Table with offset function:
A B C D
Chi
Account
NAM
Contact name
Comments
[Code]..
All client, NAM and account lists are dynamic !
View 2 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
Apr 7, 2014
I'm trying to create a spreadsheet that has a data validation in it. I have a drop down list that has to options to select; Yes and no. Every time I create a new row to add more information I have to go through the process of creating a new data validation for for each cell in this coloumn.
Is there anyway of creating a way that every time I insert a new row, a data validation list box will appear in the coloumn that I want it in?
Sample Workbook: datavalhelp.xls
View 3 Replies
View Related
May 13, 2012
I would like to be able to make the same thing and select items for my drop down list, that are not blak and follow a specific condition. But I don't know how to include the condition in the array formula.
The condition I need is follow:
I want to have a dropdown list with Items that are not blank, and First N > Last N.
[URL]
It would be preferred to have formulas.
View 5 Replies
View Related
Jun 19, 2014
Attached is example of what I'm trying to do. I want to use the drop-down on the "Master Tab" and have the corresponding price by promo_month appear in the yellow cells.
Currently its on July, but I would like to switch that to August etc and have new prices populate in the yellow cells. Prices come from the "table" tab.
Example 06.19.14.xlsx‎
View 2 Replies
View Related
Apr 5, 2009
Make a Data Validation dropdown list that also enters a range for cells, for example:
Kitchen ¦ High ¦ Upto 100 ¦ Easy ¦ Blank ¦ 2 ¦ Daily ¦ >6 hrs ¦ Medium ¦ >1 week
Lounge ¦ Low ¦ Large ¦ Easy ¦ Blank ¦ 0 ¦ Daily ¦ >6 hrs ¦ Medium ¦ >1 month
Bedroom ¦ Low ¦ Large 100 ¦ Easy ¦ Blank ¦ 1-3 ¦ Daily ¦ 1-3 ¦ Low ¦ >1 year
Bathroom ¦ High ¦ Upto 100 ¦ Easy ¦ Blank ¦ 0 ¦ Weekly ¦ 1-6 ¦ Low ¦ <1 year
I can make a dropdown list for:
Kitchen
Lounge
Bedroom
Bathroom
But if I select "Kitchen" I would like it to enter the rest of the cells in that row:
Kitchen ¦ High ¦ Upto 100 ¦ Easy ¦ 2 ¦ Daily ¦ >6 hrs ¦ Medium ¦ >1 week
The total number of rows I use is 10, and the number of items in the dropdown list will be 30.
The data in the cells 2 to 10 does not change from that first set to its data menu name.
View 4 Replies
View Related
Dec 26, 2012
I've created seven names in my data validation source list on Sheet 3 in the following range of cells, A2-A7. My data drop down validation list is located on Sheet 1, A2 in the same workbook. I would like to trigger a macro based on the name I select in this drop validation drop down list that will fill out an address, telephone number, and email address in the following cells next to my data validation drop down list on Sheet 1: B2, B3, and B4. I want it to be designed so that users will select a name and have that person's corresponding address, telephone number, and email address fill right next to their name on the same sheet.
View 6 Replies
View Related
May 3, 2007
I am trying to make a drop down list and based on what I select from the drop down needs to populate a different drop down list with information based on what I select.
For example: if I have Sheet1, Sheet2, Sheet3 and Sheet4.
Inside of Sheet4 (My Main Sheet) I have a Data Validation (Drop Down list) in Cell A5 and Cell B5
I would like to choose Sheet1 from Cell A5 and in that same sheet (Sheet4) in Cell B5 I want to have a drop down with a list of names that are located in Sheet1 in Column A:A.
Or If I would've picked Sheet2 in Cell A5 of (Sheet4), I would like to see a drop down with a list of names in Cell B5 (Sheet4) that are on Sheet2 in Column A:A . Is this possible?
So far I could achive it if information I want is located on the same Sheet. But I need it to work if info is located on different Sheets
View 9 Replies
View Related
Nov 7, 2007
I am trying to do 2 things involving data validation. 1) Count the number of entries in a data validation dropdown list and display a result. 2) Use an If statement to pull the formatting from a cell into another cell.
An example would be if Cell A1="A", then show the drop down list associated with cell or sheet "!ryanB2". Alternatively, I would like to also display the number of entries in the drop down list.
View 9 Replies
View Related
Oct 12, 2011
I have a dropdown list in C24:C50 (=CategoryList) with data validation and a sub list in D24:D50 (=ItemList) with data validation. I am looking for a way to have code automatically run after selecting an item in the data validation dropdown list in column C.
Example; I click on C24 and make a selection. I what it to trigger code that would move me to D24 and open up the data validation list in D24. After the selection in D24 I would like it to move me back and down 1 row to C25. I have not found anything directly related to this but I have found that code can be run after a selection in a valadition list.
View 2 Replies
View Related
Jun 20, 2014
I am having trouble delete a drop down list inspite of selecting clear all from the data validation tab in excel 2007. When I hit Alt + Down Arrow, I still see picklist options. I didnt set up the spreadsheet, hence I am not sure how to begin troubleshooting.
View 1 Replies
View Related
Apr 15, 2012
I am trying to make an excel sheet which has a 2 column drop down. For example I want to have a drop down list in column 1 and when I select "Item A" in column 2 "item A" 's phone number appears. If i select "item B" form the drop down this, "Item B's" phone number would also appear in column 2 and so on. how this is done (if possible) on Excel 2007? I was hoping it could be done from one workbook to another but If it is easier from one worksheet to another then that is fine too.
View 5 Replies
View Related
