I 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 RepliesI'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
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 Replies View RelatedA Macro needed which will be assigned to a button,say “Check” and when the same will be clicked all the sheets will be say protected an users cannot modify any data. Another button will be there which will be assigned macro & when clicked will ask for a password to be known by only some administratives say departmental heads only and they can modify the data.Users after clicking the “Check” button,cannot modify the data.
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 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 RelatedI 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 have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
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 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'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'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.
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 RelatedIn 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 an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
1
2
3
4
5
£11
£133
£29
£193
£100
[Code] ........
I'm using a worksheet as a surveying program. I need to be able to enter a cell reference in my "input cell", ie "=A11". Then in cell the the right of the input cell I need "=B11" to be entered. And in the cell to the right of that, I need "=C11", and to the right of the I need "=I11". I can get so far working with one cell at a time.
If I enter "=A11" in T3, in T4 I can enter: =IF(T3=A11,B11,IF(T3=A12,B12,IF(T3=A13,B13..and so on. But 8 conditions is all I can enter before I get a message saying that the formula uses more levels of nesting than are allowed in the current file format. And I need to be able to enter at least 50 conditions in 3 adjacent cells.
I am trying to move (archive) data from one sheet to another sheet based on a date range provided by the user’s input. If the date on sheet “list” in the cells in range R9:R10000 fall within that date range the following takes place. The rows that meet the date range per the user’s input in the range of R9:R1000 are copied and just the values are pasted into the next empty row starting with row 7 on the “report” worksheet. The cells that are copied on the “List” worksheet are NOT deleted but the contents are cleared. The reason for this is because there are formulas/links in those cells.
I have attached a worksheet example and have some VBA code but it does not work.
I have a spreadsheet of sales figures that I want to interrogate to show data relating to sales of specific manufacturers products to specific customers, I have a colomn (call it A) that has the customer name listed, coloum B that has the Manufacturer name, coloum C that has the type of product it is and coloum D, coloum E, coloum F, coloum G that has the sales figure for the last 4 quarters.
I would like a user input box to pop up on loading, which asks for the Manufacturer I want to search for, and then a report genertaed that lists each customer with their sales figures by type of product for the specified manufacturer for the last 4 quarters (individually not tottaled). If the customer doesnt have any sales figures for that Manufacturer I would like that listed showing '0'
i have a sheet that manipulates a variable amount of rows containing data input by the user. if a cell has an unexpected entry causing an error the program just halts with the default error message. the user then has to find the error which can take time. is there any way a message box can appear showning what row the error was found on ?
View 5 Replies View RelatedI am creating a spreadsheet for work - I have a column that is all the formulas projected sales minus actual sales.
I need to lock this column so that no one BUT MYSELF can change the codes. In other words "idiot proof" the worksheet. there are only 18 sections that I want anyone else to be able to type in.
is there a way to do this without adding users? half the ppl have personal emails and half have company emails.
Excel 2003, I have a list of products with corresponding prices located on a hidden worksheet. I have a spot on another worksheet where users can select a certain product and next to that cell there is a column for price. What I want excel to do is auto-populate the price cell based on what product the user selects. For instance, if the user selects OD2000N, then I want the price to auto-populate with the corresponding price that is found on the hidden sheet. Like I said before, I know this has probably been answered, maybe even mulitple times but I can't seem to find the appropriate thread.
View 9 Replies View Related I would like a cell to have user input. If there is no input then the cell should reference a value in another cell. My strategy was to use a formula in another cell to check to see if the user input cell was empty. If it was empty then the formula propagated the cell. If it had user inputted data then it stayed like it was.
Here is an example:
Begin Invent
1000
Gallons
End Invent
User input or
formula here
Gallons
Received
Gallons
Used
Gallons
I want the user to be able to input a value for the End Invent cell right below 1000. If there is no value I want that same cell to mirror the cell above it with the 1000 in it.
I have a cell which team members enter their team name in to. For example FR1 and RA5. I set up data validation to only allow four characters so they can enter RA10 FR15 etc. However I have now found some are entering FR 1 with a space between the team name and number. This then means my formulas further on are not picking up the cell correctly. How can I have the space between the FR and 1 removed automatically upon user input?
View 4 Replies View RelatedI need a cell to display a default value (something like "Input Here") which a user can overwrite with a value. When the value is erased, the cell should return to the default. Preferably, it'd be great if the cell reset to the default value whenever the workbook is opened as well (i.e. won't save the user's input value).
View 2 Replies View RelatedI have a table with 4 radio buttons that assume certain data in three cells when they are selected. I have used the IF command to getthe information to appear in these boxes when the cooresponding radio button is selected.
I want the user to be able to either leave the selection as it is presented to them, have the option to change the data if it doesn't fit their scenerio.
Example: button 1 "Electric" makes the data "$0.12", "$per kWh", & "100%" appear in the three cells. Each button produces different data in these boxes, however if when button 1 is selected, the user's electric cost per kWh is $0.10, I want them to be able to type that in without changing my preset default.
I m just looking out for some code where i can restrict the user from changing or inserting a value in a particular column in excel
View 9 Replies View Related I need to copy and paste the specific dates from the specific city that the user will insert in the "dash" sheet.
Basically, I need the macro to read the city and range of dates that the user will type into the "dash" sheet and copy the columns "AA" to "AD" from rows specified by the dates input. Once it has been copied, it has to be special pasted just the value onto the sheet "1" beginning at location B2.
the the data will not be available as it is vlookups to another excel sheet that i have not provided. I have deleted the data, but you can pretty much put 1's everywhere.
In the spreadsheet shown below I would like the user to select a project via a dropdown list in cell B2 which is from the data set shown in row 7 downwards. Then based on the project they select, they need to be able to see all of the locations associated with that project and choose in B3. Finally they then need to be able to select a team which is associated with the project & location combination chosen in cells B2 and B3.
Excel 2007
ABC2
Project3Location4Team56ProjectLocationTeam7Project 1PerthTeam 18Project 1MelbourneTeam 29
Project 1SydneyTeam 110Project 1Brisbane Team 111Project 1Brisbane Team 312Project 1DarwinTeam 413
Project 1DarwinTeam 514Project 2PerthTeam 315Project 2PerthTeam 416Project 2MelbourneTeam 117
Project 2MelbourneTeam 218Project 2SydneyTeam 419Project 2Brisbane Team 6