it is possible to create a character count in certain cells? I wish to create a worksheet that others can fill in but some cells must have a character count or restriction. It is similar to this - http://javascript.internet.com/forms...r-counter.html
View 3 Replies
I'm am trying to restrict the length of data that can be entered into a cell based on a drop down in that row. I have achieved this, but what I need to be able to do is use data validation to restrict the cell length to one of two values - so for example the length can only be 6 OR 9 characters long.
I cant see how to do this. At present the best I can do is rectrict to between 6 and 9 - but this would allow 7 and 8 which is not allowed.
I am looking for a VBA event(?) code that would erase another cell if I enter a value in another cell, because the criteria is that only one of the cells are to be allowed a value per row.
ex: cells A1, B1, C1
if I enter 1 in cell A1 and then enter 1 in cell C1, I want cell A1 to erase and cell C1 would have the value 1. I am looking to create these sets for 200 rows.
I am looking for a macro that would accept only one entry per selected ranges. It would be something like if there is one entry in range("b4:e4"), a message box would appear advising the user that only one entry is permitted in that range. It would give the option to delete the entry and to rekey the entry for that canditate.
I hope I am clear, if not let me know
1 entry in range("b4:e4"), per canditate
1 entry in range("f4:h4"), per canditate
1 entry in range("i4:l4") per canditate
1 entry in range ("m4:o4") per canditate
1 entry in range ("p4:r4") per canditate
1 entry in range ("s4:v4") per canditate
I have following for change event in text boxes to only allow numerics e.g.
Private Sub txtGBP10_Change()
If (Not IsNumeric(txtGBP10.Value) And (txtGBP10.Value <> "")) Then
txtGBP10.Value = Left(txtGBP10.Value, Len(txtGBP10.Value) - 1)
End If
End Sub
Private Sub txtShare10_Change()
If (Not IsNumeric(txtShare10.Value) And (txtShare10.Value <> "")) Then
txtShare10.Value = Left(txtShare10.Value, Len(txtShare10.Value) - 1)
End If
End Sub
Can I add some code so that the user can only add numeric entries to 2 DP (txtGBP) or 3DP(txtShare)
The 'gist' of what I need is for events scheduled (top of spreadsheet), no school can be placed into an event within 29 minutes (because one-half hour apart IS ok). The time increments are on the left side of the spreadsheet (rows). I'm trying to "automate" somehow so that if we try to plug a school into an event too soon from their last event, the spreadsheet won't allow it.
View 2 Replies View RelatedI need a formula that I can put into a cell, I assume via data validation, and will prevent the use of any symbols other than a hyphen "-". Spaces are fine. The cell is being used to enter the 1st line of an address.
View 14 Replies View RelatedI have a cell (A1) with a number in it. The number has 30 digits. I need a formula for (B1) that will enter the same number as A1 but restrict the number to 28 digits. Basically I need it to cut off the last 2 digits.
View 3 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 need a way to protect my worksheet from any changes but allowing the users to edit certain cells (eg. allowing users to enter data only into columns A-G).
View 2 Replies View RelatedDATA VALIDATION IN MY CODE
Below is my code which looks at Table1 and converts the cell to the appropriate colour when the cell equals the Case
What i would like to do is also have the Case be Data Validation anything else error
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rRow As Range
Dim icolor As Integer
Dim ifont As Integer
With Application
.CellDragAndDrop = False
.CutCopyMode = False
End With
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 RelatedHow to restrict copy paste for certain cells in excel?
For Example : Range ("A1:A20) and Range ("C5:C20")
I want to use the SumIf function to sum cells when other cells begin with certain characters.
I've toyed with a few ideas of how this could work, but i don't know how to specify that the cells need to begin with certain characters. The cells that would be the criteria and the ones that would be summed come out of an Oracle database (and i have no control over the way they're pulled out - yet) so the beginning characters are connected to extremely unique information, so i dont want that to be included in the if part, for obvious reasons.
I'm trying to use Data Validation for cells. In these cells I only want a Y or N entry and cannot have blanks. I'm rusty on formulas.
This is what I've come up with
=IF(NOT(ISBLANK(B6))*OR(B6="Y",B6="N"),"TRUE","FALSE")
I have column E showing elapsed time =($D$5-$C$5)*24. I want two totals from that column. One the total time elapsed and two the number of cells that have an entry as not all of them will. With the time formula, a simple sum function adds all of the columns.
View 3 Replies View RelatedI have some text that I need to format, at the moment it looks like this:
[note name="Person A" label="0"[Person A's information]/note]
[note name="Person B" label="0"[Person B's information]/note]
etc
I would like to extract the Name (Person A) and the actual note itself (Person A's information) into two columns. I tried using "Text to Columns" as I have in the past, but there is a problem. Many of the information fields contain hundreds of characters, with the largest being ~2500 characters. The text to columns works does its job but cuts out a lot of data (anything over 255 characters).
I'm using MS Excel 2000
The text contained HTML codes, so they have not appeared correctly I have replaced with []
I have a spreadsheet with approx 1000 rows of data. One column contains ledger codes that can be up to 9 characters. I need the column to only show the first 5 characters.
View 2 Replies View RelatedI have a column of about 3000 numbers that need to be converted to bar codes. In order for the codes to work, I need to place a set of parentheses "()" around each number in the column. Do I need to do this by hand, or is there a function/script for this? Using Excel 2003
View 2 Replies View RelatedThere are three questions in Comment boxes in the file. The bottom line is that the final item name in column F can only be 50 characters long including spaces. As I am entering the information in columns B,C & D it would be great to be able to see the numbers of spaces I have left to enter characters in column H without having to hit "Return" and leave the cell.
The first question may need a Macro so excuse me that it is included in the Formula section.
I have recently purchased a new GPS for my fishing boat. I am trying to transfer my old coordinates from one unit to the other. I am using a .csv file to achieve this. Can some kind please let me know (and possibly tell me) if there is way to insert characters and spaces in to multiple cells. I have over 800 individual coordinates to modify to the new gps and this would take a long time to achieve individually.
I need to replace this: 12.34.567 to this: 12 34.567 N
and this: -1.23456 to this: 001 23.456 W
Cells in the range of BG8:BP8 either have a "W" or an "L" in them. I want to have cell BO9 display the total number of Ws and cell BP9 to display the total number of Ls.
View 3 Replies View RelatedI've looked through some of the archieves and couldn't find anything like my request.
I need help filtering through 3+ columns (it varies) of data and look for strings of data that have 3 or more repetative characters or sets of repetative characters.
eee
boook
bbbbbbb
Mi...ke
111222
123###789
jpppp@myemail.com
Excelll
If this is possible, can it go one step further and do the same for:
ababab
ckckck
F&F&F&
121212
Jkjkjk
I need to remove the last 2 digits from a cell that maybe different lengths. For example:
PL26 7QS
ST20 0AW
LE4 8LF
PE30 3WH
CF31 3AY
Column A in Excel has loads of numbers all in this format 971-417. I need to have each of those two numbers in a separate cell from each other(and without the hyphen of course).
Column A Needs to be: Column A Column B
971-417 971 417
In short, I'm looking for a quick way to put each number in its own cell for hundreds of rows. Are there formulas I can use to do this or does it have to be done slowly, one at a time, stripping the information from one cell to another?
I am trying to write a macro that will go through each cell in a column with the following format "| 12- 4" or "| 60-11" and will remove the first to character "| ".
There numbers are lengths, the first being feet the second inches. I would like to achieve in a separate column the numerical length (with decimals) multiplied by 1%. My code is as follows I just need help on the conversion.
Sub newLength()
Dim LR, lrow As Integer
Dim feet, inches, Line As Double
LR = Range("B65536").End(xlUp).Offset(1, 0).Row
For lrow = LR To 2 Step -1
If Cells(lrow, "D") <> Cells(lrow - 1, "D") Then
Rows(lrow).Insert Shift:=xlDown
End If
feet = Left(Cells(lrow, "E").Value, 4)
inches = Right(Cells(lrow, "E").Value, 2) / 12 'I need to debug this line, but I suspect there is more I need to do.
Cells(lrow, "F") = feet + inches
Next lrow
End Sub
I need a formula that will turn a cell green if either A*, A, B or C is in it.
View 5 Replies View Relatedi deal with column that has client initials, date of birth and gender, in this format t-b-23/05/72-f however i want to remove the initials and gender(i.e. f or m on the left) and - so that only date of birth remains in same column.
View 3 Replies View RelatedI have an accounts spreadsheet that I copy and paste customers names and addies into from the website back end sales information.
I do not copy e-mail addresses.
I have a mailto: with an e-mail address appear in the file in lots of places, it seems I delete it from some cells and it appears in others, my file is infested with the things now.
I can delete one by one, but this would take me weeks any ideas of how I can ctrl a select all cells and mass delete these things.
I am face with making a brand new accounts file which is a lot of work.
I'm trying to create a worksheet with only one unprotected cell (simple enough!) and I want to base calculations in other protected cells on a running total of how many times a value has been entered in my unprotected cell. Re-entering the same value would need to be counted.
First enhancement would then be a means of resetting the counter.
Second enhancement would be to check for a valid entry and only then increment the counter.
