Restrict Cells Text To X Characters Based On Corresponding Cell
Apr 14, 2009
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.
View 3 Replies
ADVERTISEMENT
Sep 14, 2007
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
View Related
May 14, 2014
I 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 Related
Sep 17, 2012
I 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 Related
Jan 29, 2008
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 Related
Jan 23, 2008
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 Related
Jan 22, 2013
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
View 4 Replies
View Related
Mar 13, 2008
I am trying to do with data validation, trying to stay away from vba on this... and it is probably very simple:
Cell A1, they can select Rice, Cheese, or Rabbit
I want to use custom data validation on B1, so that if A1 = Rabbit, they can only enter 1. If it is blank or the other two choices, they can enter 1 through 10.
Can I do that with data validation? I can't get any if thens to work in it.
View 3 Replies
View Related
Jan 28, 2013
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 Related
Jul 27, 2009
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.
View 14 Replies
View Related
Sep 18, 2007
I am looking for a forumla, which I think will be an If forumla, to allow/block entry into a cell depending on what is entered in another cell.
So if "Yes" is entered into cell 1, I want cell 2 to show "N/A", and if "No" is entered in cell 1 I want the user to be able to enter data into cell 2.
View 3 Replies
View Related
Mar 13, 2008
I have a cell (B2) I would like to apply multiple data validations to.
I know I need to use the custom formula option but don't know how to write the formula.
I don't even know if it is possible, but here is what I'm after
I need to make sure the cell is 4 digits long
I need to make sure the cell starts with a zero (Because the cell starts with a zero I have it as a text cell)
I need to make sure the 2nd number is not 0 if A2 begins with 5 (A2 is also a text cell).
View 6 Replies
View Related
Sep 14, 2007
IF there is a whole number in either column a, b or c, I need to have column e reflect that with the insertion of a / for each column that has a whole number. i.e.
Column - A - B - C - D - E
3 4 / /
5 6 9 / / /
I am attaching a copy of the spreadsheet showing the examples, I can't seem to get the function to work.
View 8 Replies
View Related
Oct 12, 2013
Initially I'm simply copying a data table from a web page using "Ctrl + A" then "Ctrl + C", and then pasting the data straight onto a new worksheet so I can work with it. (After temporarily re-naming the old sheet)
But I keep finding what looks like double-spaces after some of the important text within the Range of cells I'm working with. I need to be able to select & conditional format the values of the text in some columns of the sheet, so need to loose these trailing spaces.
Unfortunately, it's not consistence as to how many spaces trail the text I need. Sometimes it's only one space, sometimes its two spaces ?
So far, I've had mixed success with a recorded "Replace" code but none of the other codes I have found on forum pages either don't work all or seem to give any consistent results. E;g; TRIM, CLEAN
I suspect my problem is, I do not know how to call the code properly, or trying to work with too large a range ?
The start of my code reads:
Code:
Sheets("Data").Select
Sheets("Data").Name = "Old Data"
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
ActiveSheet.Name = "Data"
[Code]...
' At the moment I'm using to select the pasted range I want to work on: Range(Range("C46"), Range("C46").SpecialCells(xlLastCell)).Select
This is where I need a code to work on the new Data sheet and remove all the trailing characters.
MsgBox "All data cleaned successfully !", vbInformation + vbOKOnly, "All Done"
View 3 Replies
View Related
Aug 7, 2012
What I'm trying to do has been done before, However, all of the Code i am seeing online is not working for me...so maybe i'm missing something
I want a range of cells to remain locked unless SCP is entered. I need this done for four different selections (SCP, SD, MM, PP)
So if SCP is entered, then a certain range of cells remains locked, If SD is entered then another range of cells remains locked, so on and so forth for the 4 selections.
This was what I was working with.... However, i am not sure if i need to protect or unprotect the spreadsheet before i run the macro, also am i supposed to leave all cells locked or unlocked?
Private Sub Worksheet_Change(ByVal Target As Range)
If [B3] = "SCP" Then
ActiveSheet.Unprotect ("PASSWORD")
[Code].....
View 1 Replies
View Related
Jan 15, 2010
I have a column of dollar amounts (variances) and next to each is the shift they belong to.
I am looking to get a break down of the variances for each shift.
Attached is an example of what it looks like.
View 9 Replies
View Related
Jan 15, 2010
I have a column of dollar amounts (variances) and next to each is the shift they belong to.
I am looking to get a break down of the variances for each shift.
Attached is an example of what it looks like.
View 6 Replies
View Related
Jul 5, 2012
I need to unlock and lock certain cells when other cells have data entered.
I also need to enforce in the unlocked cells a minimum value.
In my worksheet i have the below requirement
Initially Cells C6 and 7 are unlocked and Cells C5, 8 and 9 are locked
1. Cell C6 must have a value entered greater than or equal to 50, when this value is entered I need to unlock cells C5 and C9 and lock cell C7
2. Cell C7 must have a value entered greater than or equal to 50, when this value is entered I need to unlock cells C5 and C8 and lock cell C6
I also need to unlock cells C15 and 16 when C13 has "Yes" selected in the drop down menu.
View 3 Replies
View Related
Aug 14, 2008
I pasted in 1369 characters (including spaces) to a cell, and NO MATTER what I try, all characters will not print.
If I have the cell up for formatting on the function line, all text can be seen, but for some reason it cuts off the last sentance or more and will not show it in print preview.
I've tried all kinds of cell text formatting, cell merging, etc. with no luck. The only work around I found is to just have the "missing text" on the following row.
View 4 Replies
View Related
May 6, 2009
This is very similar to my previous post, which was solved. Now that I've extract the numbers, I need to extract the text for the specific work activities, for example 13Z or 9GGG. I'm assuming some variation on this formula:
=LOOKUP(9.99E+307,--MID(C4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},C4&1234567890)),ROW(INDIRECT("1:"&LEN(C4)))))
is the solution, but I'm struggling with making the correct alterations.
View 2 Replies
View Related
Aug 18, 2014
Is there a function that can remove all text and other characters from cell and only keep the numbers? The numbers can be randomly in the cell so not only in the end or beginning.
See attached file.
View 7 Replies
View Related
Aug 8, 2007
This should be simple, but I am struggling with finding a way to use the search or find function to identify text characters. This is my situation, I have for example a cell that contains FW023 or D1234. I need to be able to count the number of characters that are text.
i would think I would be able to do it with the search or find function, but can't figure out how to get it to just count the number of text characters with in it.
View 9 Replies
View Related
Nov 18, 2008
a formula that will count the txt chars in a cell example ie "aa99" result would be 2 or "aa99aa" equals 4
View 9 Replies
View Related
Mar 27, 2008
I have some cells in a spreadsheet that contain unique numbers as the beginning of the cell (these numbers are always ten characters). In the rest of the cell there is text. the data would look something like this:-
SGGHNVT561 - 3yr Maintenance
I also have some cells which dont have the unique number at the begging, so the data would just look like:-
3yr Maintenance
In another table I have a list of the unique numbers and the name that they correspond to, for example (this is the 2 columns)
SGGHNVT561 - Dave
HUKIDO8946 - Stuart
HJUTIFHE78 - Graham
I have ben trying to construct a formula that only searches the beginning of the cells to see if it containts one of the unique numbers and if it does to put the name of the person beside the cell. If the unique number is not there to return a value like 'Check'.
i tried vlookup but then i ran out of ideas :/
this is what i came up with so far:-
=IF(P7="","",VLOOKUP(P7,'[Master List.xls]Sheet1'!$B$2:$C$5,2))
The only problem i can see that this is looking for the full cell to be within the master list - but its really just the first 10 characters I want it to check
View 5 Replies
View Related
Aug 28, 2009
I have created a drop down box that allows me to choose multiple different Text Options. Example: Cell B4 has dropdown that lets me choose the Text Options, "House", "Car" and "Truck".
Next, I have 5 or so other columns underneath with their own text. What I want to do is have different cells highlight themselves depending on what is in Cell B4.
Example, if "House" is selected in B4, I want cells B7 and B9 to highlight. If "Car" is selected, I want cells B7, B8, and B10 to highlight. If "Truck" is highlighted, I want cells B9-B11 to highlight. Is this possible to do? I've tried using If/Then statements, but those don't seem to work for this type of thing.
View 3 Replies
View Related
May 4, 2006
I need a way to check to see if the first four characters of cell A1 is = 2006. If it is, do not delete the row, else, delete the row. Have tried everything I can think of.
View 5 Replies
View Related
Aug 11, 2006
I'm trying to find a way to save a file based on the contents of cell A2. I've modified the code to Create Worksheets for Each Item in an Excel Table of Data, so that it creates a new workbook for each item. I am also trying to get it to save the master workbook based on the first 2 characters in the cell. Here is the code I have, at the moment (the FAIL.xls is what I put in for testing)
If Range("A2").Value = "YE*" Then
ActiveWorkbook.SaveAs "YE_Master.xls"
ElseIf Range("A2").Value = "NE*" Then
ActiveWorkbook.SaveAs "NE_Master.xls"
Else: ActiveWorkbook.SaveAs "FAIL.xls"
End If
I've tried this, string arrays and everything else I can think of, and it's failing every time.
View 3 Replies
View Related
Feb 27, 2014
In an active cell I have this value : " Hello.xlsm"
I need a MACRO code that will take out the characters ".xlsm".
I am trying to focus on the active cell and not a range.
View 3 Replies
View Related
May 28, 2014
I have a cell which contains the following information:
01E4R3; 01W5; 01M4G3; 01W5
I want to sum up just the numbers in that cell. In this example, the answer should be: 30
View 9 Replies
View Related
Sep 2, 2008
Is there a way to specify if there are 46 characters in a cell, to apply text wrap, indent the second line, and resize the row height to 25.5?
For example, I always will have text on merged cells B7:C7. I'd like to have a macro that determines if the text goes over C7 (I figured that it would take 46 characters to do this), that the merged cells will be text wrapped, then row 7 will be resized to 25.5.
I know how to record a macro that will text wrap and resize the row height but am not sure how to do the "if" condition.
View 9 Replies
View Related
