Data Validation - Add Some More If / Else But Validation Don't Allows It?
Jun 30, 2014
I had some of values in Column A, B & C for ex: column A has brand name, column B has model name and Column C has sub_model name here i have a limited values i need to make it as drop down list but i had a problem with the below formula.
=IF($F$2=Sheet2!$H$2,al_v,IF($F$2=Sheet2!$H$3,am_v,IF($F$2=Sheet2!$H$4,au_v,IF($F$2=Sheet2!$H$5,be_v,
IF($F$2=Sheet2!$H$6,bmw_v,IF($F$2=Sheet2!$H$7,bg_v,IF($F$2=Sheet2!$H$8,cv_v,I
F($F$2=Sheet2!$H$9,ch_v,IF($F$2=Sheet2!$H$9,ch_v,"")))))))))
i need to add some more ifelse but the validation don't allows it.
View 2 Replies
ADVERTISEMENT
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
Sep 26, 2007
I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
(I do not want to use a combo box for this)
View 9 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
Apr 3, 2009
I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.
View 2 Replies
View Related
Nov 11, 2008
A2's validation is dynamic as it's selectables varies based on the selection made by the A1 Validation. (=indirect(a1)).
My problem is this....once if have selected from both validations...if I go back and change the A1 validation to a blank (or empty value) or clear the contents on that cell....validation A2's value remains as it was. I would like it to recognize that A1 is blank and also become blank (or goto an empty value).
View 2 Replies
View Related
Mar 26, 2008
I want to add a validation list to a spread sheet using a named ranged. Easy enough so far, but if the range only has two values which are repeated several (hundred) times - Yes & No - how can i stop the value appearing on the list more than once?
I'm open to other suggestions with this such as using list boxes etc, just so long as i can get around it without having to maintain a seperate spreadsheet of the values i would like to appear in my list.
View 9 Replies
View Related
Jul 27, 2009
I have a valve in A1 of 5
I have a value in A2 of 1
I have a value in A3 of 2
In cell A4 using data validation
Min formula=IF(AND(A1=5,A4>=A3),)
Max formula IF(AND(A1=5,A4
View 9 Replies
View Related
Feb 26, 2010
I have 2 sheets in a workbook. The first sheet is 'working loads' and the second is 'completed loads'. I enter data into the 'working loads' sheet. When that row of data is completed, I mark a x into the 'completed' column, when I put the 'x' into that column it sends that row of data over to the 'completed loads' sheet.
This is my problem, when I enter data into the 'working loads' sheet into the 'Bill to" column, because it is not restricted it can be entered any way the user decides to enter it. When it goes to the 'completed' sheet, the 'bill to' is entered in 2 or 3 ways depending on how the user entered it in the beginning on the 'working loads' sheet.
I want the user to be restricted on how the data is entered on the 'working loads' sheet so that I don't end up with the 2 or 3 ways it was entered for the same client on the 'completed loads' sheet. I also need to add to the the list if it has not been used in the 'bill to' info as we get new clients weekly.
I currently am physically changing the 'bill to' info on the 'completed loads' sheet manually so that the client is entered only once, but as the year goes on the list grows as I might have 5000 rows of data with the same client entered 2 or 3 different ways.
View 9 Replies
View Related
Mar 16, 2005
I am using data validation for a drop down list e.g Good, Average, None. What i want is when a user selects for example "Good" i need a score to be automatically entered into another cell so that i can then do some calculations.
View 2 Replies
View Related
Jan 7, 2014
I have alist in which a location is mentioned multiple time..
eg:
Delhi
Delhi
Delhi
Punjab
Punjab
Mumbai
Mumbai
I need a data validation in which a location should come only once...
View 7 Replies
View Related
Jan 28, 2014
I am creating a worksheet as we have collect client satisfaction surveys. I have created a worksheet with each question at the top of the columns and the answer (yes or no) with data validation. I input each survey into a new row. I now would like to total how many people answered Yes and how many answered No. What formula can I use to total the answers?
View 6 Replies
View Related
Jul 18, 2014
I have the following sitation:
xls Sheet 2
ITEM EQUIPMENT
1234 a
1234 b
5678 c
5678 d
xls Sheet 1
PO ITEM EQUIPMENT
56 1234 **
99 5678 **
** in the equipment cell of sheeet 1 I want a drop down (data validation) with the possible equipment I can use forthis item only. I.e. for PO 56 Item 1234 only the equipment a & b should be in the drop down menu.
View 12 Replies
View Related
Oct 6, 2008
I want to do couple of things here.
1. put an empty field in a drop down box. If possible the empty field should be at the top from the drop down menu.
2. Update names in drop down box when I add names to the list
View 8 Replies
View Related
Oct 8, 2008
I would like to impose data validation on Sheet1 row A. I have a list of values in Sheet2 row A. Is their an easy way to do this?
View 3 Replies
View Related
Oct 22, 2008
I've found that sorting a column next to one which has drop down boxes set up in it, does not take the options for the drop down boxes (data validation ) with them when being re-arranged. Is there a way to make this possible, or an alternative format I should be using? see example:
If item 4 is the only one available from supplier 3, (As shown by the drop down box options) how do I sort by say, department, and still have the correct options on hand? Sorting by department, the items move, but the data validation info stays where it is, leaving the supplier 3 option for item 1, which is not correct.
View 3 Replies
View Related
Jan 12, 2009
I know this is pretty basic for most of u, and I did try to search for something in previous forums but gave up after 3 pages. I would like to input data into cell B1, if & only IF "YES" is typed into cell A1.. else it should not allow any input into cell B1.
View 3 Replies
View Related
Mar 6, 2009
I thought what I wanted would be simpler than I have found. What I am trying to achieve is for any user trying to enter anything other than a "Y" into a cell would get an error message. In a nutshell I have in column A colleague names and from there 7 columns representing the days of the week split by a "spacer" column. This goes on for seven 4 week periods followed by a column that calculates how many "Y"s and into a formula that gives a running percentage.
My problem is that as there are several people that input data, some put multiple "Y"s so making the formulas throw up erroneous figures. That is why I am after a simple solution to stop any entry other than a "Y". I am guessing this would be better done via macro code?
View 4 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
Apr 7, 2009
I have a problem in creating data validation. I want to make 3 drop lists which are connected to each other (file1.xls for example). The sample is like the tutorial from Contextures: http://www.contextures.com/xlDataVal02.html
The source of lists are from another excel file (file2.xls). The method is also from Contextures: http://www.contextures.com/xlDataVal05.html And then, when I tried this, the second drop list didn't show up according to the first drop list. But, if I put the source in the same workbook (in file1.xls), It could show up. I think the problem is in the name box, but I don't know how to fix it.
View 5 Replies
View Related
Apr 10, 2009
Firstly I have these 12 values as in the attached excel sheet (in the order: FY09, FY09Q4, FY09H1, FY09H2, FY10, FY10Q1, FY10Q2, FY10Q3, FY10Q4, FY10H1, FY10H2, FY11Q1) to be selected as Start Date and End Date. The Limitation on Start and End Date is that the End Date cannot be smaller than Start Date (for e.g.: If start Date is FY09Q4 the End Date can be FY09Q4 and above but not FY09). Now I have a separate set of Initiatives/Objectives for each time period (each Initiatives are marked in excel sheet 2) and Initiatives depend upon Start Date Selected so once Start Date is selected only the related List of Initiatives should be published.
Now the Validation and Naming I have used is not allowing me to use a list name as “FY09” and “FY10” as they are cell references in the Workbook. But I cannot also change the name to something as FY_09 as the data that would be there in the Template will finally be used without editing for upload to an application.
View 3 Replies
View Related
Aug 7, 2009
I have a simple work book. Please refer to the attachment Book1.xls.
In B1, the data is limited (by Data Validation with a drop down List) to Yes and No.
I need to limit the range of data in D1 as, If B1 = Yes , then the value of D1 could be entered More or Equal to 51 ; and if B1 = No then the data entered into D1 should be Less than or Equals to 50
I prefer to do it with Data Validation.
View 7 Replies
View Related
Aug 24, 2009
I'm building a workbook that has a tab for each department (sales, admin, tech, etc.) w/i a project. I also have an inputs and instructions tab where I have all of my lists and background info hidden.
Essentially, on each tab, the user will build a team for the project using drop down lists. The lists reference data on the inputs tab. This data includes a list of all of the titles in the company, as well as which department they belong to. I want the validation to be structured such that the sales tab drop downs only see sales titles, admin only sees admin titles, etc. I also need it to be dynamic in its drop down inclusions so that when I add/subtract titles, I don't have to reset the validation for new cell references. Below is an example of the what the reference data looks like.
Input Tab:
Col A --- Col B
Dept --- Title
Sales --- Rep
Sales --- Sr. Rep
Admin --- VP
Admin --- SVP
Sales --- Closer
Tech --- Analyst
Tech --- Developer
View 4 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
Oct 27, 2009
I have two tabs in one spreadsheet and I want to have the results chosen in one tab to filter in the other tab. So based on my drop down box result chosen in the first tab, I want the data in the other tab automatically filter only the result that was chosen.
For example, if I have the word "apple" in the drop down then I want all my data in the second tab to filter by "apple" in column B.
Is this something that would be possible?
View 12 Replies
View Related
Nov 12, 2009
I am trying to create a data validation rule that only allows a neagtive value to be entered if the word "backcharge" appears in an adjacent cell. So far I have come up with = if(d2="backcharge",<=0, .... But I am not sure how to finish it off.
View 4 Replies
View Related
Jan 11, 2010
I’m a fairly basic excel user, I think I have a fair idea of what I’m trying to achieve.
I’ve tried to take the time to explain my query so if anyone is happy to help, I’ll definitely take the time to give a decent response.
I’ve broken it down abit.
Background
I’ve created a form for which users select a series of drop down boxes, as each drop down box has a value selected, the options available for the proceeding drop downs are filtered and the options then become limited.
In order, the drop down boxes are;
1. Select a Utility – the data validation source list is “utility”GasElectricity2. Select a Distributor List of 3 Gas Distributors the data validation source list is “ElectDist”List of 5 Electricity Distributors the data validation source list is “GasDist”If user selects Gas as a Utility as per dropdown list #1, then the option shown in “GasDist” validation source list will appear.
If user selects Electricity as a Utility as per dropdown list #1, then the option shown in “ElectDist” Validation source list will appear.
3. Select a Service the data validation source would be either “Eservice” or “GService”Special Read Investigation4. Select a Sub Type the data validation source lists are either “EReadSub, GReadSub, EInvestSub, GInvestSub”
View 9 Replies
View Related
Mar 31, 2013
Have a drop down with data validation, or just manually enter the names? I have a list of 500 names, and sometimes people who enter data can spell names wrong; which I have to fix at a later date. What are some thoughts as to a best way to go about this?
View 3 Replies
View Related
Oct 29, 2013
I would like the drop down list in D15 to show only the house price values which are equal to or lower than that of the budget.
The budget currently shows 50 (cell A2) so i would like the drop down list to show 20 and 50 (eventually i will link it to the names to show the names rather than the budget. )
If i change the budget to 49 only the 20 should be in the list etc.
View 5 Replies
View Related
Feb 11, 2014
I've attached a sample file of what I am trying to do.
In row 2 I have data validation in the form of lists. Below row 2 is the data.
Cell B2 has an indirect expression referring to cell A2. In other words, B2 is dependent on A2 (cascading)
What I would like to happen is when a new selection is made in cell A2, B2 populates with "Choose platform", instead of keeping the old information. The drop down populates with the new data but cell B2 still displays the old data.
Book2.xlsx
View 1 Replies
View Related
