Excel 2010 :: Data Validation - Restrict Value User Can Enter Into A Cell
Oct 2, 2012
I'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.
View 3 Replies
ADVERTISEMENT
Dec 27, 2013
I want to prevent user from entering duplicate text or numbers in a cell using the Data Validation.
View 9 Replies
View Related
Jun 15, 2007
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.
View 4 Replies
View Related
Nov 27, 2013
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 Replies
View Related
Mar 16, 2014
Data validation in an Excel 2010 workbook.
I want the date input to be restricted a start date and an End date specified in two cells on the sheet. However, when I set up the Data Validation, ANY date will be accepted.
As an example cell Z1 contains the date 1/3/2014 and Cell Z2 contains the date 31/3/2014, so in the Data Validation box, the Start Date is referenced to Z1 and the End Date is referenced to cell Z2.
But I can enter 1/9/2020 and the Data Validation happily accepts that date.
View 12 Replies
View Related
Jul 3, 2014
I am having a terrible time with Excel today. The version I'm using is Excel 2010.
I just want a simple division formula in a cell, formatted to accounting. This should be really easy, but it isn't reacting the normal way.
The simple division is =13588/12
The output in accounting format should be 1,132.33.
Every time I enter this formula into a cell that is pre-formatted to accounting, the result is 1132 1/3. It also erases my formula and replaces it with the value.
View 7 Replies
View Related
Aug 19, 2013
I have used data validation to colour rows in one of my spreadsheets at work. The column that uses the data validation is the first column and has a drop down list of 5 words. One of which is collected, which currently indicates when the card has been collected by the individual in question and no further attention is required. It colours the rest of the row green. There is also a column that is entitled 'Date Collected'. Instead of having to input this manually, is there a way to automatically enter the date to be the date that the 'Collected' value was selected?
The title is misleading, I realise there will need to be some sort of IF statement in the 'Date Collected' column. It's been a long day already.
View 6 Replies
View Related
May 24, 2012
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
View 9 Replies
View Related
May 19, 2007
A 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.
View 9 Replies
View Related
Nov 5, 2008
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 Related
Dec 8, 2012
In 2010 I can save a file with data validation and it says it saves it OK but when I reopen it later It removes all data validation.
Is this a bug in 2010??? I had saved it under different formats same results.
View 2 Replies
View Related
Sep 4, 2009
I 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.
View 5 Replies
View Related
Apr 9, 2013
I am using excel 2010. I have 3 separate form control each with its own macro. I would like to create a combo box with a drop down with a list of each form control case and be able to perform the same function as the individual form control when a specific name is chosen in the list of the combo box.
View 3 Replies
View Related
Apr 20, 2009
I am trying to display a message box for the user if there is "agency" in cell o8 but nothing in p8... I tried the following code but it doesnt work..
If Range("o8").Value = "Agency" And Range("p8").Value = "" Then
MsgBox "Please provide name of agency in cell p8"
Sheet9.Shapes("cross").Visible = True
Else
View 9 Replies
View Related
Feb 24, 2014
I have four cells c1 thru c4. The SUM of these cells must equal 1. I want to display an error message "sum of cells not equal to 1.0" but only when data has been entered in all four cells.
For instance =SUM(C1:C4, "<>1") should display the error message. Is there a way to write the formula with an IF statement and still use data validation with error message box?
Assumptions:
-Will ignore blanks
-Error message "Sum of cells are not equal to 1.0" until values in all four cells have been entered, not after a value is entered in a cell. (validate expression after all four cells have values or blank)
Scenarios:
c1 = .25, c2 is blank, c3 = .25, c4 = .50 TRUE no error message should displays
c1 = .25, c2 = .25, c3 = .25, c4 = .50 TRUE no error message should displays
c1 = .25, c2 = .30, c3 = .25, c4 = .50 FALSE error message should display after value is entered in c4
etc
What I don't want is for the error message box to display after .25 is entered in c1 because the user is still in the process of adding values. Can this be done using Data Validation in Excel 2010?
View 2 Replies
View Related
Oct 4, 2012
I have a file where I need the user to fill in a value in a single cell (A1) in order for the data validation I have set up in column B to work. Can I set up a second data validation in the cells in column B? Or is there a way to enter a data validation in cell B11 that would error the user if A1 is not filled in, and circle the cell (A1) that they need to fill in? B11 currently returns the sum of B3:B10.
A
B
1
Straight
2
Time
3
Sun
4
Mon
5
Tues
6
Wed
7
Thurs
8
Fri
9
Sat
10
Sun
11
Total Overtime
View 8 Replies
View Related
Mar 12, 2009
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 Related
Aug 19, 2013
Using Excel 2010, I set up a simple workbook with a spreadsheet on one page and the ranges for data validation on another. I successfully linked the fields in the "Category" and "Tender" columns with the data on the second sheet, making nice drop down menus. Everything works fine on my computer. However, when we open the workbook on a different station, the drop down menus disappear. If you highlight any of the "Category" or "Tender" fields, the little drop down prompt is missing; they behave like regular, un-formatted fields. It appears that the data validation became un-linked.
We tried opening versions with certain fields locked (Mainly the Total, Breakdown, and Summary areas, but the fields with data validation were still editable), and also completely unlocked documents. Every time, the drop down lists were lost when opened on different stations.
I've attached the completely unlocked version : 00_FLEX TRAVEL EXPENSE REPORT_v3.xlsx‎
Forget to check a box or something? This seems way harder for me to figure out that it should be!
View 3 Replies
View Related
Nov 29, 2013
I want to control what a user enters into a single cell based on others.E.g.
A B
1 20 500
2 50 500
3 75 500
4 100 300
Column A is a thickness and column b is a width what I want to happen if the user enters a thickness in cell D1 and a width in cell D2 I want an error to pop up if for instance the user enters 100 as a thickness to only allow a maximum of 300 in the width. likewise if the user enters a thickness less than 100 in cell D1 to allow him to enter up to 500.
View 4 Replies
View Related
Jan 15, 2014
This question is about the need for staff to enter a code which they do manually - it is not a case of referencing a cell with a value in it and applying a formula to it to get the result I want as there is no other cell involved.
So they have to enter codes with the following format:
J0000000
Ie max 8 digits long, with the first letter being a J (it's always a J).
Can I use custom data validation to force it (so if the code was for eg J0000123 they could just type 123 and it would put the rest in for them??) - and would I set the cell to Number or Custom in terms of number format??
View 4 Replies
View Related
May 8, 2013
I have produced a basic search/lookup facility on an Excel workbook that simply allows the user to enter a post code and some details will be returned using a vlookup. The document is going to be rolled out to a number of operational users so I want to basically 'lock down' everything I can in the document (basically everything except the data entry cell) and make it fool proof- I have locked all cells apart from the data entry cell and have made the file read only.
The only issue I am encountering is when the cursor is in the one 'unlocked' cell (i.e. the one the users will enter the post code into); it appears that you can break the document. For example, when the cursor is in the 'unlocked' cell, I am able to go to Tools > Options and change various settings including cosmetic colour changes but also cell calculation which breaks the lookup functionality. This is probably enabled as the cell is unlocked, but if I lock the cell, when I protect the document, it disables data entry!
if there is another way of providing this one cell for users to input data into for the vlookup to work whilst locking down the rest of the document to ensure that no-one can break it?
View 1 Replies
View Related
Nov 3, 2011
I have a macro that will email a link of the worksheet.
the problem is I may have different email addresses to send it to. I want to be able to have an input box come up, and the person to enter in an email address, click ok and have the macro continue to email.
Sub Make_Outlook_Mail_With_File_Link()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String
If ActiveWorkbook.Path "" Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
[code].....
View 2 Replies
View Related
Oct 10, 2012
Im trying to enter a formula for volatility in my excel 2010 spread sheet
my hig low close is listed verticaly in A,B,C
The formula for D1 is supposedly “={LN((A1)/(B1))}^2â€
The formula for E1 is supposedly =SQRT(SUM(D1:D10)/10)*SQRT(252)*100
I put this in D1 and it is giving me an error
View 3 Replies
View Related
Oct 4, 2007
I have 4 textboxes that the user enters data. I then have a button on the user form.
i would like to write code so that when the user hits the button...
Textbox1 data goes into active cell
Textbox2 data into the cell below Active cell
Textbox3 below it
and textbox4 below it
then close the user form.
View 9 Replies
View Related
Jul 12, 2013
Can data validation be used to enter a text message if no numeric value is entered in the cell?
I want to force the cell to have to be populated with a number and if not show "--".
View 3 Replies
View Related
May 1, 2014
Have developed a form for my associates and want them to be able to move to the cellls I want them to fill data in to by merely hitting the TAB or ENTER key vs. having to move the cursor to each cell requiring them to input data.
Want cursor to move in a specific cell order. You would think you could merely say 1M, 2C, etc.
View 9 Replies
View Related
Jul 2, 2012
I have developed a Userform button in Excel 2010 using the developer icons, which when clicked on with my mouse it runs my super dooper macro. It works great!
But one thing I want to have happen is to have the choice of using the Enter key to start the macro or use the left mouse button
For example, I place data in (say) cell A1 and the userform button is in cell A2. When I place the data in A1 and press the Enter key, the cursor moves down to A2, but doesn't highlight the user button. When I press the Enter key again, the cursor moves to cell A3.
What I want is when I fill in the data in A1 and press the Enter key, the cursor moves to cell A2 and selects the button, so that when I press the Enter key again, it activates the macro.
View 3 Replies
View Related
Apr 8, 2009
Is it possible to lock a cell so that the user must enter the number manually as opposed to using a formula.
There is a cell on one of our spreadsheets that the user should be hand entering the numbers from the general ledger this is done to ensure that the general ledger balance is the same as the spreadsheets totals. However most of the user insert a formula that just copies the number from above into this cell that should be hand entered.
View 9 Replies
View Related
Jun 21, 2012
I've been going round in circles with this for hours and have got no-where. I 'm trying to get Excel to paste in a set pattern of data based on an input cell.
In row 1 I have months starting in cell B1. B1 = April 12, C1 = May 12, D1 = June 12, and so on
The user would enter the data in column A, starting in row 2
If the date in A2 = May 12 then Excel would enter 10 in cell C2, 20 in cell D2 and 30 in cell E2. If the date in A2 is changed to June 12, then the previous data would be cleared and Excel would enter 10 in E2, 20 in F2 and 30 in G2.
Date would be entered in A3, A4, A5 etc and I would want it to post the same fixed data depending on the date the user enters in teh relevant row.
I've tried IF statements and also some code but can't even get near it working.
View 9 Replies
View Related
Oct 22, 2011
I'm having some trouble getting control ENTER & EXIT events to fire properly when having controls embedded on frames within a userform. I'm using Excel 2003, 2007, & 2010. Here's the userforms I'm working with:
With FRAME:
Without FRAME:
In both cases, the DESCRIPTION field is disabled. The selectable controls on both are a combo-box, textbox, listbox, & 2 buttons. On the FRAMED version, the combo-box & textbox are contained on a FRAME.
Here's the code, same on both userforms:
Code:
Option Explicit
Private Sub cmbRecipes_Enter()
ListBox1.AddItem "ENTER - " & cmbRecipes.Value
End Sub
Private Sub cmbRecipes_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ListBox1.AddItem "EXIT - " & cmbRecipes.Value
End Sub
All this is doing is posting a message to the listbox when the combo-box ENTER & EXIT events fire. This works as expected without the FRAME, ENTER is shown when the combo-box is entered and EXIT is shown as focus is moved to another control. But when running it on the FRAMED version all I get is a single ENTER event recorded regardless of how I move the focus through the control set.
Another oddity is that if I have more than 1 control that can receive focus on the FRAMED version, it appears to work correctly.
View 6 Replies
View Related
