I have 2 input boxes. Input Box 1 asks for user's Date of Birth. Input Box 2 asks for user's name: Pretty simple right! They will loop until following 2 conditions are met: If the user enters something in them OR user presses Cancel. If they are empty and Ok is pressed then keep on looping.
View 4 RepliesIve been having a little issue with this spread sheet. The problem ive got is I cant get the validation to work where it only allows one yes in a bunch of cells and gives an error message, heres an example of what im talking about.
View 3 Replies View RelatedValidation on Excel
The User enters the date in cell D5 - I would like excel to validate the date based on the following rules:
1. Format "dd/mm/yyyy"
2. Prevents entering any weekend dates
3. When the user enters the date it can be the previous or current months date range
I have tried several ways in excel but I can't get anything to work. If an error occurs I want an alert message to appear.
I am in charge of validating 800+ excel reports coming from our vendors each month. I use excel data validation feature everytime I receive a new file. Data Validation feature is native to the excel workbook and cannot be saved to be used with other workbooks (at least; this is what I think).
What is the best way to write a generic validation script that I can run on any workbook. I am thinking somewhere along the lines of a test script that looks for specific data, data format, required data etc.
Here are some of the rules I have to validate against:
Cells in Column 'A' must contain data, reject if not populated.Cells in Column 'B' must contain a specific value (cat, dog, car), reject if other.Cells in Coulmn 'C' must contain (CA,AMM) only if cells in Column 'D' = 'Yes'Cells in Coulmn 'E' must be in proper casingAddress must use U.S. Postal Service standardsZip Code must be 5digits+4Exclude formatting charecters like '( )' from phone numberMinumum age must be less than or equal max ageMaximum age must be greater than Min ageCheck for populated data in cells, else reject file.Date must be in 'YYYMMDD' formatTime must be in HH:MM:SS' or 'MM:SS' formatID must contain 7 integersThis is a sample that represent 70% of files recived.
create macro in order to protect Data Validation rules.
e.g. If user copy and paste cells from others source which is not in the same validation criteria or not contain any validation rules, the existing validation will gone.
so, is there any macro which will be able to automatically run to prevent the data validation?
InputBox User Validation. Need to modify my code as follows?
View 5 Replies View RelatedI want to be able to validate the data input into an input box. It must be in the following format
Jun-07
First three letters of the month, a dash then the last two numbers of the year.
Im trying to add value to a data validation list using the guide at [url]
The only issue I have with it is that my validation list is in another sheet, and I can't get the code ti work with the changes suggested in the comments at that site.
Below, both inputbox cases appear to work fine. If you enter an incorrect input on your first attempt it will offer you the option to return you to an inputbox.
View 3 Replies View Relatedcreating a spreadsheet for work which is almost working a treat Unfortunately, when a row of data moves from one sheet (Queries) to another (Archive), data validation is lost. Initially I thought I daidn't have it set up on the Archive sheet, but on moving it back to queries (by use of a macro) the validation is still not working. how to keep validation rules WITH data when it is moved please? I will upload my file when I get home from work....network restrictions prevent me doing it here!
View 8 Replies View RelatedI have an Excel workbook which contains data entry fields, which have different types of data validation rules - like Lists, Date, Whole Number.
I do not want end users to remove these data validations as well as the formatting of these cells by doing copy/paste. So, I have implemented techniques mentioned in the following post, and elsewhere - to override the paste functionality and implement PasteSpecial values automatically.
[url]
To keep it simple, I'm only supporting pasting a single cell at a time.
Now my problem is this:
Doing the PasteSpecial values programmatically doesn't prevent the user from pasting values in the cell that violate the data validation rules. So, I can paste a string into a cell having data validation as Whole Number, or a invalid string into a cell having data validation as List.
The following post just suggests disabling paste whenever data validation is present:
[url]
But I would like to allow the paste operation if the value being pasted is a valid value for the cell's data validation.
I have the inputbox so i can set a string value,
When the inputbox Cancle button is pressed i want to exit sub,
If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname.
If the inputbox has a value do >>>>>>That>>>>>
I am creating a worksheet that I need to encorporate an If statement into. What I need to happen is when cell A1 has a number in it (1,2,3,4...) I need it to perform a function on cells B1 and C1. However there will always be information in cells B1 and C1, but I only need it to perform the function when the number in cell A1 is anything but 0.
View 8 Replies View RelatedHow can I create a rule for a table that finds a specified word and replaces it with another such that the rule would continue to apply to the table even when a new record (or row) is added. There has to be a simple way to do this without a macro.
View 3 Replies View RelatedThis Subroutine:
[Code] .........
Seems to behave differently each time it is called from my main Loop. As an example the first loop finds:
TotalTubes = 6
TubeRows = 4
TopRowTubeCount = 2
Where 6/4 = 1.5 and is rounded up to 2
The controlling variable in my code for Length is TopRowTubeCount and it is a Public Variable Dimensioned as an Integer
However on the next loop it finds:
TotalTubes = 210
TubeRows = 4
TopRowTubeCount = 52
Where 210/4 = 52.5 and is rounded down to 52?
I even used the round function for the TopRowTubeCount calculation and the erratic behavior continues.
I have set all cells in a worksheet to be protected. I then selected specific cells that are not protected. The problem is that cells A11:D29 except C13:C14 do not follow the protection rules in that I can edit them (but when I right click, I cannot format, just like they're protected). These cells are NOT part of the group that was selected to be unprotected. When I unprotect the sheet and check the protection setting for these oddball cells, it shows as protected. All other protected cells do not allow entry (which is what I want).
View 9 Replies View Relatedhow to display the current set of conditional formatting? I don't want to go to the cells I want a listing of all the conditional formatting rules. I am sure I have seen this somewhere in the past but cannot locate it.
View 3 Replies View RelatedI have two conditional formatting rules that work independently, but I am looking to combine them into 1 rule, making (2) subject to (1) when (1) is true
(1) =$B3:$B6="y"
(2) =AND(COUNTIF(K$3:K$6,MIN(K$3:K$6))=1,K3=MIN(K$3:K$6))
I have started to use excel again because I need to create a spreadsheet for maintenance hours done on a vessel. The problem I have right now is, how to set a rule to allow only certain multiple of a particular number to appear. For example,
I have a initial cell value where I have to manually insert the total number of hours that have gone by in the vessel. Let us say that this value is 500 hours.
In another cell which is labeled, "Last Completed", it would have to read at how many hours this job was last completed.
I also have another cell which gives you the time interval before the job is performed again. This value is 250.
So if the current number of hours is 500, the last completed date should be 250. (assuming Maintenance was done at 0 hours and 250 hours).
How do I make it so that the "Last Completed" cell would read 250 until I hit 750 hours which at that point should read 500 Hours.
I'm looking for assistance with working out the following formula. I live on an apartment complex in Marbella Spain and we pay Monthly Community Fees of say $100.
We want to calculate a penalty amount for late payers of say 30% of the monthly fee due for non payers? So for instance if the monthly amount due in Cell D8 = 100 and the actual amount received in cell E8 shows 0 or then increase any figure due for the following month in Cell F8 should increase by 30% ($ 130) in the following month. and so on
I use Excel ver 2003. Trust my query is reasonably clear?
I'm trying to do a CONCATENATE function in Excel 2003. The first part of the function (this is working correctly) is a) concatenating 4 cells from a different worksheet (WB_NEW) into one cell and is b) putting a space in between each entry:
=CONCATENATE(WB_NEW!AH3," ",WB_NEW!AI3," ",WB_NEW!AJ3," ",WB_NEW!E3)
What I'm now trying to do is enhance this function to perform with 2 separate concatenation rules i.e. "if one of the cells specified above is blank, concatenate a different set of cells".
I would like to fill some cells with a customer id if this customer is the same as the cell bellow. As my example below.
CUSTOMER idCUSTOMER NAME TYPE
737346 Nikos 81
1154765 Kyriakos 81
1154780 Thanos 81
I would like to fill automatically the empty cell bellow Kyriakos the customer id 1154765 in new column. I want in column "new" to fill with customer id and when the customer id is empty to fill the same customer id as above customer id.
Like the example below
CUSTOMER idCUSTOMER NAME TYPEnew
737346 Nikos 81 737346
1154765 Kyriakos 81 1154765
1154765
1154780 Thanos 81 1154780
I have got to this stage with a formula but cannot understand why it isn't working:
=IF(OR(A2=1,A2=2),(MIN(B2:B6)+50),(MAX(B2:B6)+50))
If A2 equals 1, I want it to add 50 on to the smallest (earliest) date in C2:C5.
BUT
If A2 equals 2, I want it to add 50 on to the largest (latest) date
The formula just adds 50 on the smallest (earliest) date, whether A2 equals 1 or 2.
What am I doing wrong?
I have a file which I want to copy the lines.
I have made a test file for clarity. See annex.
I am trying to clear row with the following rules:
the last cell must not contain 0, if 0 delete entire row.
the first cell the the A row should be only three character long, if more delete entire row.
Now I managed to delete the 0 in the last cell and but cannot get the VBA to count the characters in the first row and if more than three, delete the entire row . I know to count the characters in a cell is done by Len(Rng.Value) , but getting the VBA right is alluding me .
the script is here,
Sub Del_rows_with_zero_in_column_of_activecell()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Const StartRow As Long = 1 'Row to Start looking at
Dim StopRow As Long
Dim Col As Long
I want something strange but i guess you can do just about anything with excel these days if you ask around.
I want names for the sheets that are contained on a specific excel file that i have to be automatically generated accordingly to the following:
sheetname = runxyy_s=zz%
Where x is the lowercase letter of the word formed on the name of the first sheet of the workbook
and yy and zz are values stored on specific cells on the sheet,lets say yy is A1 and zz is H4.
I have found this very challenging, and have really confused my self on how to systematically approach this. I have the user enter the value in the black highlight boxes, some subtraction produces a base value, the user may also enter the tolerance of the working value (base value), effectivlly giving rise to a small range to generate the permutations from.
What I would like to do then is find all permutations of the inventory (letters in the sheet) with their corresponding values right above them to the small range determined via the user (working value and tolerance). There are also quantity limits for each item. There four basic rules that need to be adhered to:
1) Permutation can't have both S and PP, so one or the other
2) Sum of H and HN must be greater or equal to sum of S and PP
3) H or HN or both must be present, so their sum cannot be zero
4) If HN is less than or equal to 2, and H is greater than 2, then NA and NG must equal 0.
I had previously posted a query on deconstructing 2 set of tables (with similar heading)(in sheet 1 & 2) & combine them side by side with similar heading (in sheet3) so as to use condition formatting to highlight the differences & visually i can compare them easily.
1 group is 2 tables to compare. I have about 30 groups. So after creating the 1st group, i intended to use it as a template, without having to create the conditional formatting again & again. But after copying, the conditional formatting rules change. I am not sure why.
I attached a sample of my template for reference.
Sheet 1 - Table 1
Sheet 2 - Table 2 & part of Table 1
Sheet 3 - combine both Tables with similar column header side by side
in Table 3, cell B3 rules are "=C5<>B5"
SO the problem is when i copy data from Sheet 2, Table 2's Column Header B (cell f9 to f421) to Sheet 3, Column Header B2 (cell c5 onwards) the rules change to "=C5<>XFC5"
I have a spreadsheet with many rows of data. I want to be able to see the last month or so of data (each day is one row), but I do not want to have to scroll to the bottom of the worksheet to see the recent data, so I want to reverse the data - I want Row 1 to be my column headers, and for every new day I want to insert a new row at Row 2 and add my data. However, when I do that, my conditional formatting gets all messed up. Once the new row is added, the formatting rule changes its range to only those rows which had data in them before I did the insert, each new row gets a separate but identical rule that applies only to itself, and the headers row gets its own rule.
That seems incredibly stupid to me. Is there a way I prevent the formatting rules from changing and just stay the way they are which applies to the entire column?
I have a column with data in the following (ideal) format:
texttextexttext/s2
texttextexttext/s2/c14
texttextexttext/s24/c2
and need a VBA solution to automatically split it across 3 columns, i.e
column1 column1 column1
texttextexttext s2
texttextexttext s2 c14
texttextexttext s24 c2
/s will always be present and will always be followed by one or more digits (no limit).
/c may or may not be present but if it is present, again it will always be followed by one or more digits (no limit)
I have been using TextToColumns with "/" as the delimiter and it works fine in most cases. However, the data frequently contains additional "/" characters which breaks everything. some examples of valid but problematic data are:
texttext3/12/2012text/s2/c2
text/textexttext/s2/c2
and even:
text/stext/ctext/s2/c2 (i.e. multiple occurrences of /s or /c)!!!