In need data validation in a range of cells (lets keep it to A1 for the example please). The data being entered is a 9 digit number, which needs to satisfy the following:
The 7th and 8th characters should be equal to an even number and be greater than zero, but less than 35.
Example 1: If 123456789 is entered in the cell, this would return an error because 78 (although being an even number) is above 35.
Example 2: If 987654321 is entered in the cell this would be allowed as 32 is an even number and less than 35.
Is there any way to use a Named Range in the "Source" field in a Data Validation? I have multiple sheets in a workbook that have the same layout, and I am using alot of different Data Validations. My problem is that whenever I need to change, or add a choice to one of these, I have to go to each sheet and change the Data Validations that are effected by that change. Is there a way to use a "Named Range" or something to put all of my acceptable choices, for that Data Validation, in one spot. And make any changes I had to that field.
I have a cell for which I need the only options for data to be entered from a list in two different tabs from which I understand cant be done but is it possible to have two drop down options for the same cell?
The attached spreadsheet should explain this slightly better...
I have a monthly report showing actuals and budget for current month (or quarter) and year to date. Range names have been created for (1) each month, (2) each quarter and (3) year to date). I'm using the SUMPRODUCT function to extract information by account. For example:
=SUMPRODUCT(($A9=ACT_Account)*(ACT_Jan))
This formula works except I have to replace the "ACT_Jan" each month accordingly. Can a data validation list be created with the range names so that updating this formula monthly can be simplified? For example, changing "ACT_Jan" to "ACT_Feb" with a drop down list.
In the above table I want to enter in another column or row, a number which is not available in the given table (i.e. no duplication is allowed). (This table may grow both column & row wise). If, duplication is there the message box should say that the value already exist. And, if possible, the existing value can be colored with any color, so that we can easily find out where it is.
1) how to name a offset table and 2) how to avoid duplicate value when entered? Is data validation is one solution?
I've 12 numbers for 12 months in Cells. 13th Cell is the sum of all these 12 Cells. What I want is - Can I have a Data Validation on the Sum that it should not be more than 100. So, the users can enter the values in 12 Cells to make it equal to sum of 100 only. I know, I can do in VB Code but if it is possible to do as a function or Data Validation..
I have several cells with data validation's drop Box. Every data validation uses a range name as source (myColorList). Here goes the hard part: I need a new value inside the dropBox list, but The problem is that I can NOT modify the "myColorList" range.
-- Is there any way to build up the dropbox source using the range name and the new value? Something like
Basically in cells b12:b26 (15 cells) I need to enter the type of item (which is limited to 2 items). I made a drop down list for that range which works perfectly, therefore the user can choose either item 1 or item 2 or blank. However, I need to figure out a way so that item 1 can be chosen a maximum of 10 times in the range, and item 2 can only be chosen 5 times in that same range..
I am assuming that you can't add that condition to the drop down list... So I was thinking of adding an extra column duplicating b12:b26 (c12:=b12 etc...)- and entering a data validation there.
I was going to enter the following:
if item 1 is chose : =NOT(COUNT($c$12:$c$26)>10) & if item 2 is chosen: =NOT(COUNT($c$6:$c$6)>5).
However, for some reason it doesn't work - it doesn't block the entry if I try to enter 11 item 1s or 6 item 2s.
I have been tasked with creating a data validation list. My data is contained on a separate sheet from my data validation list. I am using a named range to use the data for my data validation list. However, I am looking to add a criteria to my data validation list so that it only is populated with values based on another column on my data sheet.
For example, if I have a data sheet called "Employee" and I have two columns. Column A is a list of employee names and I have named a range in column A as "EmployeeName". Column B is used to define the category of employee. This value is a letter, for example, "A", "B", "C", or "D". This column does not have a named range as I don't know if I need one to get this to work.
I would like the data validation on one sheet to use a list of employees of a particular class from another sheet. Straight forward validation is easy using a validation list with a formula =EmployeeName. But how do I only list employees that have a category "A"? For some reason, I cannot get some of the formulas I have found while searching this message board to work.
I have a list of names I used in data validation (dropdown list)....I need to add more names to the list....What is the best way to change the range to include new listings? Is the offset function suitable for data validation? How do you use the Offset function in data validation?
I am using following code to apply data validation list. However this code gives error Intermittently. The error message is 'Application defined or Object defined error'. Also this code looks little cumber some. Will really apprciate if any one can put some ligh on why is this error appearing sometimes. Do I need to change something in the code or add few extra lines. I feel following points will be necessary when you check my code
1. The data of validation list is stored under the column heading 'Reported_By_List' 2. Validation is applied on the range under the column heading 'Reported Through' 3. Start and last Cell Address Of the cells of the column in which data of validation list is stored are extracted into the variables 'StartCellAddress1' & 'LastCellAddress1 ' 4. Start and last Cell Address Of the cells of the column on which data validation will be applied are stored are extracted into the variables 'StartCellAddress2' & 'LastCellAddres2'
Sub Validation1()
Dim wsName As String Dim RValue As String Dim WorkBookName As String Dim StartRow As Integer Dim StartColumn As Integer Dim LastRow As Integer Dim CellAddress As String Dim ColumnAddress As String Application. ScreenUpdating = False WorkBookName = ThisWorkbook.Name wsName = ActiveSheet.Name...........
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.
In Column C, I have a variable range (usually like, C5:C100000) that has data validation in it. However, only about 10% of the cells have a value from that data validation list in them (which is what I want). I'd like to write a macro that only cleared out the cells with no value in them, so that they no longer had any data validation in them. Is this possible?
I have several cells on a worksheet which contain data validation and force the user to select from a list, and several other cells with free user input. I want to use a macro to clear all data, and return the cells containing data validation to blank. (I have a blnak row at the top of all my lists, so there is a selection for "blank".)
I have no problem clearing the cells requiring user input, but my recorded macro would not select the cells with validation and return them to blank.
The first cell containing validation that I need to clear is M39. I have my code listed below for clearing the other cells. If I could get a clue on how to return M39 to blank, I could figure out the rest of the cells.
Attached is the spreadsheet I am working on, log sheet and example sheet. As you can see from the example sheet I have location, Equipment type, equipment id and some others. As you can see on the log sheet, at the moment people select the information from validated cells, but if they select Point Machine from the equipment type list they have to scroll through all the equipment ID’s when they only really need to see 10 equipment ID’s relating to Point Machines. Is there a way to get the validation cells to do this or is there another way. I am very very new to VBA if this is an alternate way to do this.
I have a list and set cells with a List Data Validation so you can choose them from a drop down menu. What i want to do is when you choose one of the option is the cell next to it come up with other option relating to the previous. E.G. I choose John Smith in cell A1 as a manager and in A2 a list of his team appears.
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
The codes in E column are what is entered in a worksheet range (user cannot see the list). Needed is to show both the two-character code in column E and the text definitions in column F in the dropdown. As data validation now works, users only see the two-character code and are not remembering what the code stands for - they need to see the definition.
Is there a way to show both the two-character code from column E along with the text in column F? When user selects, only the code would be entered in the target cell. (Is data validation the correct technique?)
I have set this problem up in closely located cells to construct the issue. In reality, the validation list is well off-screen from where the user is actually inserting the codes and there are many more codes/definitions.
I want to prevent copying and pasting over cells with data validation as this means the validation is overwritten. To get around this I've selected all the cells with data validation and unlocked them, then protected the worksheet with all boxes ticked apart from format cells, columns and rows.
This prevents copy/pasting from overwriting data validation but it doesn't prevent data being pasted in that doesn't meet the validation criteria.
So for example, say -1 is in cell A1, with no data validation. In B1 there is data validation, which doesn't allow negative numbers to be entered. If I copy and paste A1 into B1, the data validation isn't overwritten, but it doesn't stop the non validated data (-1) from being entered! If I then double click on B1 and press enter it recognises the validation criteria is not met.
I have column of cells, both of with have drop down menus to select information from. I have an adjacent cell to display an error based on conditions. Is there a way to have conditional formatting to apply to the cells with Data Validation to change the color, based on the error. I have tried using the Conditional Formatting options but it still does not change the color of them. I believe i have to use the formula option, but not exactly sure what to put, i know a if statement could work but unsure on how, there are only two conditions that i would need for it to apply, if the adjacent cell has an error, and when it does not. Is this possible?
I have a list in one worksheet which comes from "=SALESMEN!$D:$D" but the list is extremely long with blank values. How can I make the list only show values from column D which are non-blank?
Currently the list goes up to 30, however I want to use all of Column D from the SALESMEN worksheet, that way if I add to it, the names will automatically be added to the list in the other sheet.
Starting to struggle a bit here. Trying to create a user form, with help from this website I was able to create 2 dropdown lists based on data validation and information from another sheet in the same workbook. I have one cell B7 that uses data validation and sheet "Eq List" to create a drop down list of departments that the user can select from. Once B7 is filled in, it creates the 2nd dropdown list in B9 so the user can pick "Equip Desc".
Where I am trying to fill B11 which should automatically give me back the Mach ID that is related to B7 & B9. Am I going about this the right way? I have ttached a sample file to make it easier to understand.
Is there a way to have a user on sheet1 pick from a pull down of a data validation on sheet2? I have important information that is linked to only sheet2 within a data validation. I want to be able to reference it in sheet1 to have the user pick, then it toggles them on sheet2. Is this possible?
