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 RepliesI 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
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 RelatedI 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)
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 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
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 RelatedI 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.
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.
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.
How would I go about displaying in a cell the time from the last entry to the current but show 0 time if the range is empty?
E5 to R6 currently has space for numbers to be entered.
E3 has the command =NOW()
I would like E7 to show "xxx minutes have passed since last entry" where xxx would show 0 while the range is empty.
i type into any cell in row 1 i need the time at that moment to be captured and shown in cell "B". When i type into row 2, the time which i entered the data must be captured into row 2 cell "B".
View 5 Replies View Related=IF(E14<=0,0,IF(N9="yes",MAX(E15*C15,30),30))
I am currently using the above formula and need to make an addition to it.
If D8 is greater than 9000 and less than 9999 then the entry will be 35 rather than 30. Any other entry in D8 would leave it at 30
I need to first merge cell entries of column 2 if cell entries of column 1 are the same; following columns always retain their corresponding first row (see cells highlighted in yellow in attachment).
View 14 Replies View Relatedapply conditional formats to a cell, based on the entries in another, different cell?
View 2 Replies View RelatedI have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
I am creating a log to keep track of my start and stop times when I work on a project. I have two columns labelled START and END and I can use a macro button to make the current date and time appear in the cell under these headings. What I haven't been able to find is a way of getting the macro to move to a new blank cell ready to accept the next entry. For instance, move one cell to the right or move one cell down and one to the left.
Can anyone tell me how to do this? (recording it just names the cell so it always goes back to that named cell.)
Trying to write an if statement giving a simple value of 1 if a time is outside of weekday hours 8am to 9pm. Cell formatted as (6/3/17 2:15 PM). Function should report "0" value as this is a Tuesday between 8am and 9pm.
A cell formatted as (6/3/14 7:54 AM) should result in "0" value.
I have a bunch of data and I want to be able to count the number of entries that fall within each of the 24 hour increments in a 24 hour clock. (military time)
For 12:00:00 all times would be between and including 12:00:00 and 12:59:59
Column B | Count
------------------
12:00:00 344
13:00:00 44
14:00:00 5
Basically each line signifies a date & time that a deposit was placed and a date & time that a withdrawal was actioned. My goal is to be able to determine what kind of average response time we have between the time a withdrawal was requested and the time a withdrawal was actioned.
View 2 Replies View RelatedMany years ago I created a complicated spreadsheet that calculated employees's leave, sick, vacation etc. The timekeeper would indicate the amount(s) used for a particular day and it would automatically calcuate. The problem with this is that the time keepers do not use .25, .50, or .75 for the fractured time. Instead they used .15, .30 and .45. Previously, I used a convuluted if statement looking to the right of the leave to figure out what and how to calculate it and I'm trying to revamp it to make it more streamlined.
View 14 Replies View Relatedi have a slight problem i have this script which i want to run on all worksheets which are numbered (i.e. 1,2,3,4 etc) and to delete the rows in the F128 range which is under 00:05:00. I just cant figure it out to get it working.
View 2 Replies View RelatedI have a database of over 10,000 entries. I am trying to get the average time for four specific entries (department, test 1, test 2, test 3). I did an averageifs for the tests individually and calculated the average time individually.
The three tests comprise of the department.
Average of test 1 = 40.8
Average of test 2 = 39.9
Average of test 3 = 94.8
Average of dept using the averageifs is 41.3
Average of dept by taking the average of the values above is 58.8
Why is that discrepancy there?
I have an excel spreadsheet that has about 40,000 rows....and about 30 columns across. The two columns I'm writing this about is column "PO#" and column titled "VALUE". The PO# column will have a number such as 4500234567...and the value column will have the cost of the PO#, for example $5,000.
Now within the the spreadsheet the PO# number and value will be listed multiple times....and there are hundreds of PO#'s listed, with its value.
I would like to perform some kind of filter on a seperate worksheet (? or whatever i needed) that would show the PO# number and value once.
I need to generate a report that indicates how long it took a vendor to respond, and indicate if the time was less than 2 hours.
I'm having trouble with a formula that will indicate how long it took for the vendor to respond during business hours (8-5 monday through friday). I can easily calculate the total elapsed time, but I'm at a loss on how to account for the after hours time period.
Would this be better handled with VBA?
for instance if i type in a particular cell any number 1, 0, 53 .. excel must not allow me to do this...
but there are certain value that have both number and alphatbets eg. 001 michael jordan ... cell should allow such values.
I have a range B1 to B4 for user to input value. Worksheet Change Event
However, the range B1 to B4 should not accept any value if the cell on the left hand side is empty.
Eg,
If A1 is empty, B1 should not accept any value.
If A2 is empty, B2 should not accept any value.
If A3 is empty, B3 should not accept any value.
Below code checks for condition in A1 only.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:B4")) Is Nothing Then
If IsEmpty(Range("A1")) Then
MsgBox "Please input something under column A."
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
Target.Offset(0, -1).Select
Exit Sub
End If
End If
End Sub
Eg, If I enter something in A1, B2 would accept a value which is not what I hope to achieve. It should be checking for cell in A2 whther it is empty.
Would be grateful for some advice on how to modify the code.
I attached a sample file for reference
I'm trying to find an easy method of recording the available hours of operation for an airfield which can close a couple of times a day due to weather.
The attached sample explains it better.
I have data entry in a spreadsheet which shows minutes, seconds and thousandths of a second - example looks as follows:
12:48:589 or 04:21:998. I would like to be able show the time difference between two data entries, so for example:
09:57:145 and 08:12:055 would give a difference of 1:45:090
12:07:985 and 18:59:788 would give a difference of 6:51:803
To be honest, I even struggled to work out the values on paper. Is this even possible? If so, can you let me know the number format I should be using as well as the formula or even better, post an excel sheet with the example.