How to use data validation to restrict the input format into a cell. I'm trying to limit a cell to the following format: either ###/###' or ##/##'. NOTE: the apostrophe at the end. Here is what I've been trying to get the thing to work for the 2nd case:
=AND(ISNUMBER(LEFT(H1,2)+0),MID(H1,3,1)="/",ISNUMBER(RIGHT(H1,2)+0),MID(H1,3,1)="'")
But I'm getting an error when I input a test into the cell, e.g. 12/45'.
I have a combo box where I select "Income" or "Expense". In relation to that, In the next column called "Category", I use the offset formula in "Validation". Depending on whether I choose Income or Expense, I will get different dropdown options for "Category". What I want to do is if I choose Expense, in the column named "Amount" beside category, I want to have it so that it places the numbers in brackets or simply a negative sign. Just as reference, I use the SUM function to total up the numbers at the bottom of the sheet.
I have attached a sample copy "Data Validation Test"
Is it possible to validate a string with both numbers and text with a formula using data validation? (I don't want to use a User Defined Function)
in Column C, Starting in cell C2 I have a 12 character string of six numbers, four letters, and two numbers. like this:
280838ZNOV11
is it possible to use a Custom Data Validation formula to ensure the user follows this format?
I want to record the amount of people attending a venue over a given period. The information presents names and did they attend. In column "D' from D5 to D45 the information will be yes or no. I want a formula that calculates the amount of YES and shows the answer in a numeric format
View 4 Replies View Relatedi am using this code which was provided by a kind ozgrid member
If Not IsNumeric(Me.txtLength.Value) Then MsgBox "Use numbers only", vbCritical, "Numbers Only"
With txtLength
.SelStart = 0
.SelLength = Len(txtLength)
End With
unfortunately it will not allow me to use a decimal point EG: 2.5. allow a deciaml point?
I want one procedure that will validate the CURRENT textbox (not named by name, to allow for the procedure to be included in the change event of several different textboxes), to see if it is numeric (decimal places allowed) in Excel 2007.
I followed the instructions here: {url}, which describe exactly what I want to do. So I put the final procedure listed on that page (the dynamic validation code) into the private module of the user form object as listed below:
Private Sub pipes_Change()
OnlyNumbers
End Sub
Private Sub OnlyNumbers()
'This procedure checks to see if the value
'of the current textbox is a number or not
If TypeName(Me.ActiveControl) = "TextBox" Then
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Sorry, only numbers are allowed."
.Value = vbNullString
End If
End With
End If
End Sub
I then ran the form and typed a number into the "pipes" textbox that had been validated. A compile error is thrown: "Invalid or unqualified reference", highlighting the ".Value" portion of IsNumeric. I tried to remove certain parts of the code, such as the IF statement and the "And .Value <> vbnullstring", but nothing works. I have a feeling it is because this code was created for Excel 2003 (though I'm not certain).
VBA code which can distinguish whether a cell has been edited by:
selecting an option from a data validation list (DVL)
or
manually editing the text in a cell?
The ""Show error alert after invalid data is entered"" option is unticked to allow both selection from the DVL as well as free format text entries.
The problem arises when using the code below which makes an edited cell within a range display the new value as well as the prior value(s). This works well when using the DVL but not when editing the cell.
Is there any code which can distinguish between the 2?
[Code] ....
I get data in csv format from an external source.
The problem is that it gives me dates in a lousy format of ddmmyy
For example 120706 or 050606.
Excel doesn't recognise them as dates and just displays them as numbers (120706 or 50606)
I want to see them as dates, so I select the column and change the format to date
BUT
excel sees my "numbers" as a julian date, and jumps me forward to the dates 24/06/2230 and 20/07/2038 respectively.
I am stuck on this one. In the attachment I have values pulling into N3:N7 based on a drop down choice. Some of the numbers are numeric (no decimals), others are currency (no decimals), others are percentages (2 decimals), etc.
Is there a way to pull values I'm currently pulling into column N and have those values match the format of the source? I've tried converting it into TEXT but then I cannot place conditional formatting data bars on the values and have it work.
I need to convert data in HH:MM:SS format to normal numbers
for example if I have a time ( 10 hours 30 mins 30 sec )
10:30:30 then it should reflect as 10.3
I basically am not looking to convert the data to complicated hours format like say 10.9 and all as it complicates the other calculation.
I thought I had this solved but an inconsistency has shown up. I have a long list of chemical formulas that I want to format (partially) as subscript.
Basically what I need the macro to do is look at each character within a cell and check to see if it is numeric. If it is AND it follows a non-numeric character it should be formatted as subscript.
Examples
H2O the 2 should be subscript
H2SO4 the 2 and the 4 should be subscript
2CCl4 only the 4 should be subscript
I have a long list of chemical formulas that I want to format (partially) as subscript.
Basically what I need the macro to do is look at each character within a cell and check to see if it is numeric. If it is AND it follows a non-numeric character it should be formatted as subscript.
Examples
H2O the 2 should be subscript
H2SO4 the 2 and the 4 should be subscript
2CCl4 only the 4 should be subscript
CuSO4 - 5H2O
I have several numeric values in one cell enetered using the alt return method, can these values be recognised as individual entries and therefore when the cell is formatted as $ it is applied on all entries?
View 2 Replies View RelatedI have uploaded a copy of the spreadsheet I am working on and have completed the user forms and coding for march the 1st only, so if you test, please use the options march and then the 1st!
The problems i have is, when I enter a number into a text box and press next page, the numbers fill where i want them to but the cells do not recognise them as numbers, therefore conditional formatting doesnt work! Even if I change the cell properties to numbers, this does not remove the error!
Second problem!! If a user forgetts to enter a value in a box, or they wish to edit just one value, and go back into the user form to change a figure, when they press next page, all the values in the column seem to disappear!
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 RelatedI have a column of dates formatted as:
20130201
The cell format in the column is General.
I need to change it so that the format looks like this:
02/01/2013
Is there a quick/easy way to do this in Excel 2010?
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
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 RelatedI 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)
I am trying to do is extract the volume size of products in 'ml'
from 10k plus products from a description field cell.
this description field could also contain the weight of the product in grams
so I cannot just do a search for a numeric string ,
it has to be associated with the milli-litres statement .
is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???
I have a spreadsheet that I add to daily with 3 Columns that are always the same. Name, ID number and Phone Number.
Is there a way to make it so that when other people in my department use this that no matter how they enter the name, it is always formatted the same. No matter they enter the ID number and phone number they are always formatted the same. I've tried some variations of data validation but couldn't get to where I need to be.
Attached is a simple example.
I'd like the name to always be formatted as Smith, Bob
I'd like the ID Number to always be formatted as xxx xx xxx
I'd like the Phone Number to always be formatted as xxx xxx xxxx
I 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.
I have a question about using conditional formatting in excel (2010); I made a table with the following columns:
"Supplier Name" "Supplier Lead Time" "Internal Lead Time" "Total Lead Time". I made two different scenarios to show different supplier lead times and different internal lead times, and used the minimum function in the "Total Lead Time" column to find the smallest total lead time to select the best supplier.
The last thing I would want to do with this set of data, is plug in a formula that would somehow indicate which supplier corresponds to the shortest total lead time (which supplier has the smallest supplier lead time). I'd like to be able to use a formula that enters the name of the supplier in a designated cell, which I could indicate as the "Preferred Supplier" cell. If this is not possible perhaps there is a way to highlight the supplier's name with conditional formatting?
I have a column of several thousand entries listed as numeric with a scientific symbol eg.
1.4mSv
19.53mSv/1mSv (some have a mix and or alpha/numeric range)
I want to convert them to the numeric value only. I'm extracting to a chart which is not recognising the alpha and throwing the data out. I tried find and replace, trying various options within the 'replace format' tab with no joy.
I have a few thousand products codes (i.e ABCD123BLA08 or SHU267BLA) They are non standardised in length or structure. I wish to pull out everything upto the end of the third numeric digit.( ie ABCD123 and SHU267) I cannot use left as they are all different lengths. Ideally i would serach for a non numeric char after the number then use left up to that point. Search can't do this, FIND can't do this.
Summary
ABCD123BLA08 would be ABCD123
SHU246BLU would be SHU246
I147ORT08-12 would be I147
I have some code that goes through some data that is imported from a database via a query.
This has been working for a few years without a problem.
I lookup and employee number on my sheet to the employee number in the database table.
this is my code that checks the employee number.
Set rs = Worksheets("Employee")
If rs. Cells(r, 2) = cells(1,1) Then 'if employee number matches
......
The problem is that now the database application has adapted an alpha option (it used to be numeric only). In order for my code to work I have to change the employee number on my sheet by putting a ' in front of it.
Example if the number was 127 I need to enter '127 in the cells.
I have a few thousand in my data.
Can I add some code to my macro so I can still enter just the number in the cells?
I have a column that has cells with values as
19352510
C084111X AA
24253081
A001290U AA
19599291
48413321
I want to write an If Statement is a column next to each entry that denotes two options either Broker or Agent. Where the code is say 19352510 then Broker and where it is say C084111X AA then Agent.
Broker codes will never contain a letter. The Agent code will always start and finish with a letter.
I have a data import from our client something like this:
a1:12/11/2009 shoe bags blank-** $700.00 $0.00 $3,949.00
How can i delete all the numeric data in the cell?
i need only the text: shoe bags blank-** in a2
I am trying to create a macro that will essentially "pivot" some non-numerical data I have. One column contains data with leading zeros. I need to retain these leading zeros. The attached document has two tabs; one that displays how the data comes into excel titled "Data" and one titled "Desired Output" with the desired output from the macro.
View 4 Replies View RelatedI am trying to make a worksheet change the fill color depending on the data in the cells. I have two issues I cannot find the answers to.
View 12 Replies View Related