For some reason my data validation cell is not working when I first open up the spreadsheet.
I have to go to data validation, open it and just hit ok, then I am able to use my drop down list.
My data is this
"=OFFSET(name,MATCH(LEFT(B2,LEN(B2)),LEFT(address,LEN(B2)),0),0,SUMPRODUCT(--(LEFT(address,LEN(B2))=B2)),1)"
I have a data validation list does nothing upon start up (not working). The formula is:
=OFFSET(E$1:E$5,MATCH(C94,LEFT($B$3:$B$87,LEN(C94)),0)+2,0)
If I go into data validation and just click OK (leave formula as is), it starts working again, but the next time I start up the same thing happens.
Data validation in an Excel 2010 workbook.
I want the date input to be restricted a start date and an End date specified in two cells on the sheet. However, when I set up the Data Validation, ANY date will be accepted.
As an example cell Z1 contains the date 1/3/2014 and Cell Z2 contains the date 31/3/2014, so in the Data Validation box, the Start Date is referenced to Z1 and the End Date is referenced to cell Z2.
But I can enter 1/9/2020 and the Data Validation happily accepts that date.
I'm trying to add code to validate the format in which users are entering in a date within my userform, and I am also trying to validate if they enter in a specific value within a combobox, they will be unable to enter data within a textbox further down in the userform. Here is how my code is currently written, but neither my date or data validations are working. I have bolded the new coding I added to an existing code that was created by someone else at my job prior to it being given to me.
[code]
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
[Code].....
I need to compare 2 cell with 1 specification reference.But the function can't give return value as per required.Both 2 input cells using Data Validation List.
Please refer attachment for some examples : matching.xlsx
I have a tracking sheet... in that I got in column C say "priority" there I have a drop down list date say P1, P2, P3
Now I need to restrict that if a user enters a data in the C2 and trying to enter a data in D2 leaving the drop down list data blank in C2. A error message saying "pls select priority forst" etc...
I got the following solution from moderator:
You can apply the Custom Data Validation formula =LEN(C2)>0 to D2, making sure that Ignore Blank is unchecked.
It works but, when I enter something in that cell it gives the error message and when I click OK or Cancel it just ignore the conditions and leave the data what ever I entered and moves further.
I have the following function in cell (table 2 column C) where I want to find a contact name from my table TMI if 3 criteria are true (client, account, NAM) For each contact I have 1 true combination of client/account/NAM but for each client/account/Nam I can have several contacts.
{=OFFSET(TMI[[#Headers],[Contact]],MATCH(1,(TMI[Client]=$B$1)*(TMI[NAM]=C3)*(TMI[Account]=B3),0),,COUNTIFS(TMI[Client],$B$1,TMI[Account],B3,TMI[NAM],C3),1)}
The function is working well but shows only the first contact name if I have 3 contacts for the same client/account/NAM. (I pressed Ctrl+Shift+Enter)
When I press F9 on the function, it shows all the contact names! so they are somewhere.
Hence, I have copied and pasted this function into data validation / list in the source but it came up with an erreur.
Table TMI:
A B C DClient
Account
NAM
Contact
Comments
[Code]..
Table with offset function:
A B C D
Chi
Account
NAM
Contact name
Comments
[Code]..
All client, NAM and account lists are dynamic !
I have a ('start:end') function that's not working. A simplified example is shown below in the screen shots.
I want a given cell in sheet1 to produce the sum of factors within a given column/row arrangements and equations from sheet3 and sheet4.
I've tried all the arguments I can think of in their proper places including the ' arguments before and after start:end but nothing seems to work. My resulting answer is usually #REF!
sheet1.png
sheet3.png
sheet4.png
worksheets.png
I have 2 dates+ times.
1 the contract start date and time.
2 the contract end date and time.
Can I calculate the number of working hours between these two using a formula?
So if I have a 10 hour working day (08:00-18:00), Mon-Fri and the two Dates/Times can I calculate the number of working hours?
Example
Start 06-Mar-07 10:00
End 14-Mar-07 14:00
This is 2 part days and over a weekend.
The code below is fired from a Worksheet Change Event on cell c18. The cell has a userform calendar control to select Date of Birth. What I want the code to do is check the age at the current date and if it is below 16 or over 25, then show the appropriate message. I cannot use data validation to not allow values outside this age range because there will be instances where a person's details can be added if their age is under 16 or over 25. I just need to alert the inputter in case they are unaware or have inputted incorrectly.
When the message box pops up, if they click Yes then the code should take them to the next cell for inputting. If they click No then it should stay in cell C18 and hopefully pop the calendar back up (I've not tested this bit yet).
The code as it stands brings up the "This person is under 16 years...." message no matter what date of birth is input.
VB:
Sub AgeValidation()
Dim age As Integer
Dim dob As Range
Dim AgeMsgAnswer16 As String
Dim AgeMsgAnswer25 As String
Set dob = Worksheets("Monitoring Form").Range("c18")
[Code] ....
I have a log that I have data validations on.
Column C validates on a list called "Division"
Column D is supposed to validate based on the selection in Column C, and give the list based on Column C.
I had it working awhile ago, then I went and changed the lists. Now, the lists are not validating appropriately (I'm not getting the expected drop down in D based on the selected in C) and I don't know why its not working anymore.
I have a UserForm with Combo Boxes, List Boxes, Text Boxes etc. When the OK button is clicked I want it to ensure certain fields are not left blank and notify the user (see code below). This works great for the combo boxes but not for list boxes. It seems to skip over that code. In other words, in the code below, if a manager is not selected, it doesn't notify the user.
Private Sub OkButton_Click()
'Verify fields are not left blank
If cboAgent.Value = "" Then
[Code]....
I have set cell validation to use a named list and checked 'In-cell drop-down'. The validation works but the drop-down is not working.
View 9 Replies View RelatedI'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 the following code to check the date of birth entered into cell C18 and to alert the inputter if the age is either under 16 or over 25.
VB:
Sub AgeValidation()
Dim age As Integer
Dim dob As Range
Dim AgeMsgAnswer16 As String
Dim AgeMsgAnswer25 As String
[Code] .....
It works to some extent in that the correct message box pops up if the age is under 16 and similarly if over 25. If answering Yes on either message box then the correct thing happens. Its what happens if the inputter selects No thats not right. If the age is under 16 and the user selects No in answer to "Is this correct?" then the code is clearing the cell contents and showing the calendar again but is also popping up the 'Over 25' validation message box which then won't go away until Yes is selected. Also there are then multiple copies of the calendar open.
What I need the code to do is look at the date selected from a popup calendar in c18 and decide if that age is within the 16-25 year old range. If it is outside that then the inputter needs to be alerted to it. I can't use the inbuilt data validation because there are some scenarios where it would be acceptable to have an age outside of that range but we want to cover inputting errors as well as double checking the age.
When a msgbox pops up to alert the inputter and they choose "Yes" to say the date of birth is correct then I want the focus to go to cell C20 ready to input the next piece of information.
When the inputter selects "No" on the message box, then I want the original date to be deleted and the calendar to reappear so they can select another date. So effectively resetting the field so they can start again choosing a date like when they first entered the cell.
There is a range of quarters and relevant dates like from and till - means if you have 1 Q 2009 => it means from 01.01.09 till 31.03.09. I would like to have following in my excel. There is a validation for selecting Q from list, once you select Q, there is LOOKUP function displaying from and till dates. But however it's not working, in same cases it's working, but in most cases the from/till period is not relevant to selected quarter.
View 2 Replies View RelatedI needs a formula (not VBA) to calculate the required start date.
I have to do a job of 14 working hours and this job must be finished on 05-may-2014 13:00
My working week is from monday u/i friday and every day I work from 08:00-16:00
At what time do I have to start the job to get it done in time.
The formula should give this result: 01-may-2014 15:00
i am trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 4 Replies View RelatedI'm using the following code to try to ensure a value is entered into a text box, but when I click over to the next text box skipping the first one entirely, I don't receive any message indicating the previous box is empty. I'm trying to make it so certain fields are required and others are optional. Here's the code I'm currently using:
[Code] .....
I'm trying to validate a cell in Excel 2007 that should contain only two letters.
Formula:
Code:
=IF(AND(LEN(A1)=2,ISTEXT(A1)),TRUE,FALSE)
When the value of A1 is JK, the result is TRUE
When the value of A1 is 12, the result is FALSE
When the value of A1 is 3K, the result is TRUE
When the value of A1 is K3, the result is TRUE
When the value of A1 is 123, the result is FALSE
When the value of A1 is JKL, the result is FALSE
When the value of A1 is 3, the result is FALSE
When the value of A1 is K, the result is FALSE
The LEN function works as expected, but the ISTEXT function does not, whenever the cell contains a number and a letter. I've tested the LEN and ISTEXT functions separately, and get the same results.
Is it me, or is it Excel?
I 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 am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies View RelatedI 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 RelatedI 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 am looking to fill in data in a column C that is in between a start and end "trigger" in a separate column B with a constant value (10). Additionally I would like to have a constant value (50) populate another column for a count of 5 cells from the End "trigger".
A
B
C - working
D - clean
1
.39
Start
2
28.07
10
[Code] ........
I have data as shown in the attached image file. Though the example image shows that the data starts from cell D8 but it could start from J30 or any other cell for that matter.
View 9 Replies View Relatedcan anyone help me how to create a macro which will copy range of data based on a criteria? I have a worksheet which contains dates on column C rows C30:C64, dates would start from march 29 to may 2. how can I copy the range of data from April 1 to April 30? the criteria which will be copied will always be the start of the of the month up to the end of the month...meaning if the dates placed is like April 26 to June 6, it would automatically extract the range for May 1 to May 31 and place this on another sheet.
View 6 Replies View RelatedI am brand new to using macro in excel. I have a report that is generated every month (looks like sheet 1) and I use that data to create an analysis report (sheet 2). Each month forecast is replaced by actual for the month that just finished, I have tried to use macro recorder to make this more automated as every month I copy the forecast down and the actuals in and then readjust my totals and add in the now 12th months forecast, but I have had no luck.
View 10 Replies View RelatedHaving trouble figuring out the correct formula to show the start and end of shifts in a staff rota? Its probably really easy as it seems quite straightforward. I just cant figure it. DOH!! I have attached a short template of the problem.
View 5 Replies View RelatedCode:
With Worksheets("Utvalg1")
.RAnge(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)).Copy Sheets("Oppsumm").Cells(.Rows.Count, "C").End(xlUp).Offset(1)
.RAnge(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp)).Copy Sheets("Oppsumm").Cells(.Rows.Count, "D").End(xlUp).Offset(1)
End With
I have this code, but it doesnt paste the data, its only left empty. I want it to paste in columns C and D from row 15 and down as far as there is data to paste, aprox 5-7 rows...