Is there anyway (without using VBA) of creating validation within a cell so that it will only accept capital letters?
In addition, can this also be done to accept either capitals and numbers in a set format?
eg C####C
Where C is a letter (in capitals) and # is a number
I have a column with thousads of names
i.e. JohnSmith
Is there a custom way to delimited the text by Capitol letter.
Or, to have the text string searched and then insert a space in front of every caps it finds to get the output.
I want it to copy and paste whatever the cell is;
Andrew Smith
not
ANDREW SMITH
Code:
Sub Replacing()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Cursor = xlWait
Application.DisplayStatusBar = True
Application.StatusBar = "Generating DM Pack, please wait!"
[Code] .......
I run a catalogue of music (thousands of records) made in excel
where I have in the first cell the name of the band + album + format,
something like this:
Pearl Jam - name of the album - CD
how can I turn automatically all names of bands into CAPITALS, in
example:
PEAR JAM - name of the album - CD
Is there a way to turn in CAPITALS all text from the beginning of a cell 'till the symbol with spaces on both sides ( - ) of each cell ?
I know how to turn the entire cell into CAPITALS using the formula =upper(column nš) but not just part of the cell.
A2's validation is dynamic as it's selectables varies based on the selection made by the A1 Validation. (=indirect(a1)).
My problem is this....once if have selected from both validations...if I go back and change the A1 validation to a blank (or empty value) or clear the contents on that cell....validation A2's value remains as it was. I would like it to recognize that A1 is blank and also become blank (or goto an empty value).
I 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'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 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 had some of values in Column A, B & C for ex: column A has brand name, column B has model name and Column C has sub_model name here i have a limited values i need to make it as drop down list but i had a problem with the below formula.
=IF($F$2=Sheet2!$H$2,al_v,IF($F$2=Sheet2!$H$3,am_v,IF($F$2=Sheet2!$H$4,au_v,IF($F$2=Sheet2!$H$5,be_v,
IF($F$2=Sheet2!$H$6,bmw_v,IF($F$2=Sheet2!$H$7,bg_v,IF($F$2=Sheet2!$H$8,cv_v,I
F($F$2=Sheet2!$H$9,ch_v,IF($F$2=Sheet2!$H$9,ch_v,"")))))))))
i need to add some more ifelse but the validation don't allows it.
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 Relatedwhen I select a data validation value in cell A3 of sheet 1 will change the data validation value in A5 of sheet 2, and verse vice.
Also in the same attached file, I want to solve another formar issue. The needs is expained in the file.
I have a list for example with two variants "YEs, "NO"..
Is there any possibility to choose YES or no in any cell an reflect the same value in another list on another sheet.
I'm not sure if its possible to do what I want. I'm trying to do it without VBA as my users might have a high macro security setting.
I have a validation dropdown in A2 where they pick 'yes' or 'no' for FLSA. I would like B2 to be a dropdown (validation, listbox, whatever) that would change based upon A2. If A2 is 'yes' I want to use the data in A5:B7 and if its 'no' use A10:B12. Ultimately I want B2 to equal one of the numbers from A5 - A7 or A10 - A12. However when they click on the drop down they see the combo of column A and columb B as column A has no meaning for the user.
I want to make a cell validated so that a user can only enter a value that is either below -0.1 or above 0.1. This is not necessary the next but is desirable to set a maximum of 999.99 and -999.99.
View 4 Replies View RelatedI'm trying to set up a spreadsheet for users to update with details of customer service problems. A few of the columns have data validation against them (the master lists are on another worksheet) but I want to be able to copy the validation to a new row when created.
I've found this macro which seems to do the trick in terms of creating a row and copying the validation - but there's a couple of things that I'd like to amend...
VB:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim LR As Integer
If Target.Column > 1 Then Exit Sub
[Code].....
Firstly, I want to be able to create a new row above what was entered previously. The macro works fine as it is, but as the spreadsheet grows and grows, I don't want users to have to scroll down to the bottom of masses of information. I'd rather have it so that every time a user double clicks in cell A2, the data shifts down but the validation that was in row 2 remains in row 2.
Also, at present in the macro above the value in column A for the new row is 1 greater than the row previous. I'd like that to still happen even when the new row is going above the previous data. Trying to prevent duplicates is another reason why I'd like the macro to only run if cell A2 is double-clicked.
I inherited a spreadsheet and the date validation does not work. It checks for the formatting of the date being entered, and when anything is entered is returns the pop-up error message. so even when a correctly formatted date is typed into the cell the error message prevents the user from entering the new date. I have checked and cannot find the problem. I even tried formatting the cell with Crtl-1 to ensure the date being entered was formatted right, but still does not work.
This is the code snippet for three columns I am working with:
VB:
columns("G:G").Select
With Selection.Validation
.Delete
.add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="format(""m/dd/yyyy"")"
.IgnoreBlank = True
.InCellDropdown = True
[Code]....
I have a spreadsheet (attached is an example).
The problem I have is that there is a check cell in column J that either "A" "B" or "C" can be entered and the total in column K can be used in different sheets in the workbook.
Here is the part I cannot get to work, If "C" is selected in the check coulmn then Columns G,H,I, are not allowed to contain numbers and a warning dialog should appear on screen. I have tried numerous ways to get this to work and have given up
I've attached a basic excel file with an example of the problem I need to solve. As you can see, if you select "No" to "Do they have a pet?" then conditional formatting blocks out the next field. The problem with that is, it still allows you to enter a value into the "Type" field which is skewing my results. Is there a way to allow a value in "Type" ONLY if there is "Yes" in "Do they have a pet?". I would also like it to delete the value in "Type" if I later select "No" instead of just covering it up. Is this possible? I'd like to avoid the scripting route if at all possible but I'm not sure it is...
Also, I have two linked spreadsheets, one pulls the totals off the other. However, If I add rows into the original it does not update the total formulas in the second sheet. Is there a way to make it do this automatically?
Is there any chance someone could post an example file for this? I cannot figure out how to set it up.
View 14 Replies View RelatedI need a code/formula/validation that needs to go in cell C3 and D3. If B3 contains the following, Transactional Accounts, Notify Me and Enhanced Notify Me than there has to be a number/reference number in C3 and D3. If there is no reference number than a warning needs to pop up informing them that they need to put in a reference number or ID number before they continue. If there is no reference number or id number they cannot move on, or something of that sort.
View 2 Replies View RelatedBasically what i need to do is create a validation list, the contents of which will be dependant on the entry in another cell. The catch is that the other cell contains an "if" formula.
eg.
A1: 10
B1: =if(A1=10;"A";if(A1=11;"B";if(A1=12;"C";"Invalid Entry"))) ....
Hi I have a column where a cheque number can be entered I need to check on entry that the cheque number has not been used before further up the column.
View 7 Replies View RelatedI need to find a solution that will prevent users from pasting values into cells that do not match values in a named range or validation list.
I think the best method would be to have it trigger with a SheetChange event. I would like it to check to see if the enteredpasted value(s) in a range of cells is equal to one of the following:
K
1
2
3
4
5
6
7
8
If it does not, I want the cell cleared and a message box shown.
I am sure this is simple, stock code for someone, but I am teaching myself vba and am fairly green. Does anyone have this handy in thier code library?
my main project will have about 8 different buttons.
View 5 Replies View RelatedHaving a problem locking the sheet and still have it work. Not sure if their is a easy work around for this. Just lock sheet and see what I get.
View 7 Replies View RelatedI have got a macro link to a button - but I would like a box to pop up saying warning do you want to do this etc etc - the person has to then yes in the box (then potentially) click on ok for the rest of the macro to run. As a fall back there would be a cancel option on the box.
View 2 Replies View RelatedI have a TextBox on a UserForm in which the user should be entering a series of coma separated zip codes. (12345, 54321, 15243, ...) The number of zips to be entered is variable.
I would like to validate entry as the user types. My first thought is to use the change event and tell it that only numbers, comas, and spaces are allowed, but that doesn't do anything about ensuring 5 digits per zip or having a space after each coma.
Is there a Format function that would allow for a variable number of zips? What else might work?
I've got a cell with a dropdown list using in-cell validation.
Unfortunately, the items in the list are a bit too small to read. Is there any way on increasing the text size and reducing the number of items shown at one time (currently 8 but 4 would be better)?
I am making a spread sheet which contains drop down values and there is a grand total which should not exceed 100. Like distribution table in 100% type1 to type 10 and the total distribution should be 100% not less not more. how do i do the validate and indicate an error in case a wrong value is entered.
View 9 Replies View RelatedI have validation tables in a few cells. I've unlocked them and password protected the sheet so that they couldn't be changed other than what is in the list.
What's happening is that the list is there, but the cell is also allowing an entry that is not in the drop down.
How can I protect the cell to only allow the validation list?