I am trying to restrict a cell to only be able to input the letter "i" multiple times, almost like a tally sheet, in other words I want the cell to be restriced to one letter, but allow that letter to be entered multiple times.
View 4 RepliesI want to restrict user from entering alphabets like I,O,Q and S in a cell. Can we do this using data validation or any formula because I don't wan't to use drop-down list or refer to values in other cells either.
View 3 Replies View RelatedI'm using Excel 2010, and I need to restrict the value the user can enter into a cell (E9).
In cell E3 is the screen width (pixels). eg 6024
In cell E5 is the preferred width of a window. eg 450
The user, in cell E9, enters an x coordinate for which they prefer the top left corner of the window whose width is specified in E5.
If the value that the user enters in E9, added to the width entered in E5, exceeds the value of E3, (if E9+E5 > E3) then the value should be disregarded (window will be off right of screen) and the user re-enter.
I'm not familiar with the use of data validation, so I'm uncertain as to how to use it in this circumstance.
How can I restrict the user from entering anything other than the date and the proper format required for this input box? I am having a tough time figuring out how to check the input for the right date and format . .
Sub EffDate()
Dim a As Variant
a = InputBox("Enter the effective date" & _
" of the subject's lease using a 2 digit day," & _
" a 2 digit month and a 4 digit year" & _
" (dd/mm/yyyy).", "C/NC Analysis")
Range("U4").Value = a
End Sub
Is it possible to add a data validation to a cell which restricts the user from inputting more than 2 decimal places?
View 6 Replies View RelatedIs there a way to limit say a value to be inputted into a cell say between 1 and 10, and to have a warning message pop up if any other values are keyed into that particular cell?
View 3 Replies View RelatedI have two fields that do the same thing [preset selection vs custom entry], and I need to restrict one from holding any data if the other already does. One field is a drop-down menu, and the other is a decimal entry. It was easy to restrict the decimal from being entered if a drop-down selection has already been made - Data Validation prevents any entry unless the drop-down cell is blank. It's not so easy to restrict the drop-down, however, as it already uses List-type Data Validation to allow only values from a specified range.
Does anyone have any ideas for allowing the drop-down to function as normal if a specific other field or range is blank, but not allowing a selection to be made if that specific field or range already has data in it? I've attached a simplified example worksheet.
I have two cells, A1 = Yes/No and B1 = Date. I want to restrict someone inputting a date into B1 until A1 is marked as Yes...? I've tried data validation, but seem to be hitting a wall with it...
View 2 Replies View RelatedSituation: I have an Excel file with multiple worksheets. Each of these worksheets contain the same rules for data validation in a certain column (G).
Problem: If I want to add an item to the data validation lists I have to edit every single worksheet. I've tried making a seperate worksheet containing data validation items and using that as the source for the data validation rule, but Excel doesn't allow me to switch worksheets while defining the source.
In other words, I want to manage my data validation rules of multiple columns in multiple worksheets in one central location.
I am using the below code to enter the data in Cell "D" & "E" of the worksheet.
Dim a, b As Integer
a = WorksheetFunction.CountA(Sheet2.Range("C:C"))
ActiveWorkbook.Sheets("Retailing Data Sheet").Activate
'If Range("C" & a + 1).Value "" Then
[Code] .......
But, If someone wants to enter the data manually into the Cell "D" & "E" its allowing which i dont want.
It should be enter by using the form only...
I am working in both 2003 and 2007 Excel.
This is a simplified version of my worksheet:
I have two cells, A2 and B2. When I enter a numeric value into B2, I want it only allowed when certain text values are in A2. I.E.: Allow any value in B2, but only if A1 is either "SYDN or "ADEL".
Now using info found in an old post, I can achieve this with a combination of data validation and code:
=OR(A2="SYDN",A2="ADEL") for the validation and for the code:
[Code] ......
The only problem is I also need to reset B2 if A2 changes to invalid data as a result of some other input to that cell (A2). In other words if B2 is showing "1" already and A2 were to change from "SYDN" to say "LOND", then B2 needs to be cleared. If A2 changed to "ADEL" then it doesn't need to clear but it is ok if it does because it is fine to re-enter "1" into B2 again.
I have attempted to attach a sample worksheet : Xl0000003.xls
I want to restrict the value entered on a sales sheet to force the value to be over 15% margin. In column M you enter a value in column N it report the margin. I want to force the value in M to give a minimum 15% in column N or report an error.
View 3 Replies View RelatedI have two coloumns on a worksheet. In the first column I have data validation drop down list. I want to be able to: if the first column "Other" is chosen then the next cell will allow text for description, if anything other is chosen in the first drop down list cell then I want the next ( text for descritption ) cell to be resticted.
View 4 Replies View RelatedI want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.
Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.
code that will be able to lookup a cell in Column J of Sheet1 by using a lookup value on Sheet2. The lookup value can be any cell in columns B, D, F, H, J, K, or N on Sheet2. The tricky part is, I want the result of this lookup (the result comes from Column H of Sheet1) to be placed as a data validation input message for the cell directly to the right of the lookup value when this cell is clicked.
For Example: Lookup Value is "416991" which lies in cell N8 on Sheet2. Look it up in Column J of Sheet1 and return the appropriate value "X" from Column H of Sheet1. "X" will then appear as an input message when cell O8 of Sheet2 is clicked.
I've written a spreadsheet and am wondering if there is a way to permit data entry in a cell, contingent upon another cell being populated.
In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.
i have this in my 1 cell: ttgtcctacttacaacactgtgcttagtaatggttattgcgactttatccttgttctgaa i want to count how many "a" in this cell . which formula i can use to solve this problem ?
View 8 Replies View RelatedI'm trying to set a data validation in B1 from values in A1:A5.
A1: Car
A2: Papers
A3: Pencil
A4: Pants
A5: Shoes
What I want is show in dropdown list the words in singular (without the "s" at the end).
The formula below works if I introduce it in a normal cell,
[Code]....
or as array formula with CRTL+SHIFT+ENTER in this way
[Code] .....
But I get the message that the formula has error when I introduce any of both in "Validation Criteria"-->Allow "List"-->Source.
Ive been having a little issue with this spread sheet. The problem ive got is I cant get the validation to work where it only allows one yes in a bunch of cells and gives an error message, heres an example of what im talking about.
View 3 Replies View RelatedI'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.
Is there a way to restrict a macro to run only a specified number of times during a user designated time period, for example run no more than 3 times during a 24 hour period?
We have a macro that runs to alert maintenance personnel if a certain temperature condition is met in machinery. This condition may happen numerous times during a day but we only need to alert the mechanics once a day if the condition is true.
I'm trying to write a macro that will not allow a number to be zero. I have a macro that references a cell that if it is input to zero will create a loop that goes on forever. The thing is this cell will never have to be zero, so is there a way that I can write a macro that if this cell is zero, it gets reset to its previous value?
View 4 Replies View RelatedI have a spreadsheet which monitors the hours employees work - one of the rules is that an employee must have 12 hours rest between shifts...
I am using the below Data Validation formula in column I to highlight when an employee has less than 12 hours rest and an alert to appear
=OR(AND(I4-B4=0.5),AND(I4-B4>=0,I4-B4>=0.5))
Now this works perfectly with the exception of when an employee finishes at say, 18:00hrs on a Friday, and starts at 22:00hrs on a Saturday - Excel Calculates the Rest hours as 04:00, when it should be 28:00
I've had a suggestion to use the following:
=I4+I2-B4-A2>=0.5
(I2 being the saturday date, A2 being the Friday date) - this does work but only for Cell I4, If I copy the Data Validation down the column all cell references change - is there a way to keep the I2 and A2 in the formula without having to retype in every row?
In cells B7:B229, I only want the user to be able to enter 1,2,3, or 4. How can I force this on them?
View 1 Replies View RelatedI have , within a workbook multiple worksheets which have multiple cells with a certain DATA VALIDATION MESSAGE. I am trying to write a macro to sum up all those cells which have a common DATA VALIDATION MESSAGE.
View 2 Replies View RelatedI currently have 6 cells that Have a Validation Criteria of a "List". I'm using the Input Message on the Data Validation box. I need to have this Input Message Pinned to a specific area on my worksheet. Each time that I exit and reopen the file the Input Message returns to the Area around the dropdown box. The input message is different for each of the 6 cells.
View 9 Replies View RelatedCells A1 to A10 contain names of different cars, what i would like to do in VBA is when i select any individual cell to the right of these the name of the car appears as a message (as if i had gone DATA -> VALIDATION -> INPUT MESSAGE -> CARS NAME).
Tried positioning a Userform next to the active cell but when i scroll down the page and select a cell the userform is to far down the screen to be seen.
Is it possible to create a formula using a Data Validation List where it will add a new entry to the existing entries in a cell?
For example: I have a data validation list with the names of Pete, Chris, Bruce, Carly, Megan, and Becky. With the normal data validation set I can select one entry and it will be output into the designated cell. If I select Bruce then Bruce with be placed in the cell. Is it possible to set it up so that each time I click on data validation it will add an entry to the already existing entry? E.g. I have already selected Bruce then I decide to add Becky, I want the output cell to show both Bruce and Becky.
I know I can do this with a formula that will place all the results from different cells into a single cell, but I would then have to create multiple data validation entries of which I would rather avoid if possible. Not to mention I don't remember how to do this formula anyway......
I have to input data into "ANYDATA" field. Since I need this input to be 100% accurate, I would like my operator to input it TWICE. E.g. 1st input - Obama 2nd input- Obama. Only if BOTH the entries match will the data move into ANYDATA field, else the field becomes blank once again, and the operator has to re-key twice again. In example above, if he had input 2nd time as Obame, then he will have to repeat the process. Can anyone pls help me create suitable formula/ field settings...Thx! Preferably, I would NOT like to create two dummy fields "ANYDATA1" and "ANYDATA2" with corresponding entry in "ANYDATA" if both match.
p.s. Need this to be done using MS-Excel only.
What would be the correct syntax for pulling information to the input message box from another sheet?
Code:
Sub test_data_validation()
'
' test_data_validation Macro
'
Dim nrows As Integer
Dim i As Integer
Sheets("sheet1").Activate
[Code] .........