How To Enter Date In Separate Cell By Using Data Validation
Aug 19, 2013
I have used data validation to colour rows in one of my spreadsheets at work. The column that uses the data validation is the first column and has a drop down list of 5 words. One of which is collected, which currently indicates when the card has been collected by the individual in question and no further attention is required. It colours the rest of the row green. There is also a column that is entitled 'Date Collected'. Instead of having to input this manually, is there a way to automatically enter the date to be the date that the 'Collected' value was selected?
The title is misleading, I realise there will need to be some sort of IF statement in the 'Date Collected' column. It's been a long day already.
View 6 Replies
ADVERTISEMENT
Oct 2, 2012
I'm using Excel 2010, and I need to restrict the value the user can enter into a cell (E9).
In cell E3 is the screen width (pixels). eg 6024
In cell E5 is the preferred width of a window. eg 450
The user, in cell E9, enters an x coordinate for which they prefer the top left corner of the window whose width is specified in E5.
If the value that the user enters in E9, added to the width entered in E5, exceeds the value of E3, (if E9+E5 > E3) then the value should be disregarded (window will be off right of screen) and the user re-enter.
I'm not familiar with the use of data validation, so I'm uncertain as to how to use it in this circumstance.
View 3 Replies
View Related
Feb 15, 2014
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
View 1 Replies
View Related
Jul 24, 2014
I am trying to force users to enter data in 5 separate cells in a worksheet. I have tried editing code I have found here, but just can't get it to work.
It is in the second sheet of the workbook and the sheet is called Invoice - the cells I want to force entry into (and the message I need) are as follows.
J2 - You must enter the salesperson's name
J4 - You must enter the customers name
J5 - You must enter the customer's address
J6 - You must enter the customer's postcode
L2 - You must enter the Invoice number
View 5 Replies
View Related
Jul 12, 2013
Can data validation be used to enter a text message if no numeric value is entered in the cell?
I want to force the cell to have to be populated with a number and if not show "--".
View 3 Replies
View Related
Dec 27, 2013
I want to prevent user from entering duplicate text or numbers in a cell using the Data Validation.
View 9 Replies
View Related
Jan 10, 2008
I am trying to set up a data validation to ensure that when a user enters a number in column A that matches a previously entered number in column A, the user is only allowed to enter the same date for the number it matches in column B.
For example, if:
Row 3 column A = 1320
and
Row 3 column B = 16-Dec-07
and
Row 15 column A = 1320
then:
Row 15 column B must be 16-Dec-07
View 12 Replies
View Related
Apr 23, 2014
I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.
View 9 Replies
View Related
Nov 15, 2010
Is there a way to have a data validation drop down menu but at the same time allowing users to enter free text as well.
View 3 Replies
View Related
Mar 16, 2014
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.
View 12 Replies
View Related
Aug 19, 2009
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 Related
May 2, 2007
I have 13 columns - 12 representing each month of the year and then a total. I start in F15 with Jan and end in R15 with the total - this is the heading row.
Below that I have my numbers for each month. Here's what I need to do: Anytime the total changes (by updating the monthly numbers), I need the day's date to be automatically entered into F14. This way I know the last time the data was changed.
View 9 Replies
View Related
Aug 18, 2014
Please find attached.I have two sheets. In sheet 3 I will enter data. It must record on corresponding date & column in sheet 4.
Book2.xlsx
View 1 Replies
View Related
Feb 15, 2014
On Sheet1 I want a macro that looks for the last used cell in Column D and then put tomorrow's date into column A, 2 cells below where the last used data was found in col D.
I then want to copy the entire contents of sheet2 and paste it into the cell below where we enter tomorrow's date.
View 5 Replies
View Related
May 28, 2014
What I want to do is if I add data into B7 or F7 then the current date will appear in J7. I also don't want the date to change every time the project is opened.
Thus,
If I enter "withdraw or any word" into B7 or F7 - The current date (4-Dec) would appear in J7.
then if I close excel and I open it tomorrow, J7 would still read as 4-Dec, while if I enter "deposit or any word" into B8 or F8, the current date (5-Dec) would appear in J8.
- would need to be able to enter multiple entries on one day as well.
- also i am not familiar regarding macros.
View 2 Replies
View Related
Jul 31, 2014
I have to enter a ton of dates in cells that don't need to include the day. And so was hoping if there was a way to enter a date into a cell without having to enter the day.
In essence, it seems like I have to type in the date as month/day/year and excel will figure it out from there. I would like to be able to just type month/year without bothering with the day, such as 9/14. And then excel recognizes this as a date.
Excel has to be able to recognize it as a date because these dates are expiration dates. And I would like to conditional format these dates so that when the expiration date has past the cell turns yellow.
View 10 Replies
View Related
Jan 25, 2007
when the number in cell 'A1' reaches zero i want the VBA code to enter the date in cell 'A2' that it became '0'.
This is as far as i've got.
Dim keyRange3 As Range
Set keyRange3 = Range("L:L")
If Not Intersect(keyRange3, Target) Is "0"
Target.Offset(0, 2) = Time
End If
View 9 Replies
View Related
Dec 10, 2008
I took an Excel course in college but am a bit rusty. I have a customer database that I created and I would like to see certain things after entering a date range that I specify. I uploaded a small excel sheet to show my database and what I'm looking to do. Basically I would like to enter a date range and see these answers:
Total Orders in that date range
Total unique orders in that date range
List of customers (name included) who ordered more than once in that date range
The only columns of data I need to use are: firstname, lastname and orderdate.
View 4 Replies
View Related
Jun 17, 2009
I am venturing into the unknown with this macro. I am attempting to take a sheet that has already been sorted by name.
It deals with payroll, and the sheet does include the last day of payroll in column F. The actual day worked is in column C.
The problem is that I sum all of the hours worked for each individual person, but I need to exclude hours that are not within the pay week.
Currently they are summed in a new row along with the hours from that week. Does anyone have any suggestions for a macro that could handle this task.
View 20 Replies
View Related
Nov 20, 2013
I have a spreadsheet that contains data from sales being made in various locations (sheet 1). The date the sale was made is in column A, and the location is in column AZ. I may have from 0 to 12 sales at that location on the same date. I am building a report in another sheet (sheet 2) that counts the number of sales for each date and displays that number in a cell in row 3. Above row 3, I want a formula that will reference the location (AZ sheet 1) and display that into my cell above row 3 in sheet 2. Each column in row 1 sheet 2 will have new date.
View 5 Replies
View Related
Jan 21, 2008
I know there is a way to enter the current date in a cell.
But
Is there a way to prevent the previous dates enters from updating to the current dates evey day?
View 9 Replies
View Related
May 29, 2013
I want to enter the word 'Closed' in cell B1 when the due date in cell A1 is exceeded by 272 days. I have tried using conditional formatting and excel accepts the formula but nothing seems to happen. Here is what I have tried;
B1=IF(A1<=NOW()-272,"Closed")
View 4 Replies
View Related
Jul 10, 2006
I have 6 Headings in excel named...
"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.
There are two projects.
Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.
My Specification follows...
1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
View 3 Replies
View Related
Dec 26, 2013
I have a spreadsheet where a date is entered in column A with data in B-F. I am trying to write code to look in column A for a specific date and enter additional data into columns G-L on the same row. It needs to be able to skip the rows that are blank. So I have the dates of 12/1, 12/2 and 12/3 in column A rows 1,2 and 3 and I want to enter data for 12/2 on row 2 skipping the blank cells in row 1 for 12/1. Here is the code I have below.
VB:
Private Sub CBSecure_Click()
Dim my_name As String
Dim r As Variant
Dim l As Long
sFind = DockDoorCal.Value
If Trim(sFind) = "" Then Exit Sub
[Code]....
View 4 Replies
View Related
Aug 10, 2009
I have a cell with both date & time and I need to separate this into 2 separate cells, 1 for the date and 1 for the time. Sample is attached for the before and after formats.
View 3 Replies
View Related
Aug 13, 2008
I have Excel 2000, recently when I format a cell to display mm/dd/yyyy and enter mm/dd/yy, it is displaying dd/mm/yyyy. Or it starts out correctly and during a future opening of file it displays incorrectly.
View 2 Replies
View Related
Aug 20, 2013
I am attempting to enter a code for the current date (m/d/yyyy). For appearance purposes only on the form I am using,
I am trying to enter spaces between the numerals. (" "&m&" "," "&d&" "," "&yyyy&" ".
I can only get the serial numbers to come up, no matter how I try to format the cell.
View 6 Replies
View Related
Aug 21, 2009
I have two sheets - Data and Mail.
I am using a userform to collate the information in the "data" sheet and have a unique ref number in column A. Data populates columns A - AC.
In sheet "Mail" I enter the Ref number and it pulls through the info using vlookups into a template which I then e-mail as the body of the text on the click of a macro button.
I wish to capture the date each time the button is clicked in the same data row starting in column AD then AE etc.
I have cobbled all of this together by searching around this forum - I have not expertise so please be gentle with me!
View 9 Replies
View Related
Jul 12, 2012
I receive a flat text file every week which I would like to grab with excel and extract only the data I need and enter the data into separate cells and loop until I reach the end of the flat file. I got a subroutine written that allows me to open my text file and it will enter all the data however I need to know how to parse only the stuff I need and enter it into the right cells and loop until I reach the end of my text file. Here is what I have so far:
Sub testFSNew()
Dim fs As Object ' scripting.filesystemobject
Dim txtIn As Object ' scripting.textstream
Dim strFile As String 'File Name
Dim strLine As String 'Current line being read.
[code].....
Now so far this opens the text file and dumps all the data into an excel spreadsheet however when I say all I mean it dumps everything into the first cell and does not separate it, the following is an example of the text in the flat file. I will only put in the first 5 rows because their is 5000 rows in the real file.
HDR20120710
001010000366175270012008085197804171984102919730621DOE BJ52702B25713700000000016005
00101000036617JOHN 109080 55512345671978093000000001MACHINE REPAIR 4
001010000997885270002010384198910301989103019891030SMITH DS52501C257077S0000000000005
00101000099788ROBERT 109109 55523456781999082700000001ELECTICIAN-PROJECT COORD 4
Ok so the first problem is I don't need the first line it's a header line and if you will notice everyline of the file ends with either a 5 or a 4 but it is information about each employee, so the next line would end in a 5 and that would be the beginning of the next employee.
P.S. I noticed in the preview post that this message board truncated my flat file data, so keep in mind that each line is indeed 1 line ending in either 5 or a 4
View 9 Replies
View Related
Jul 4, 2013
I have a problem regarding highlighting a specific cell, when i entered a specific date in another cell
Let me explain
If there is a Name, joining date, termination date/resignation date in cell A1 A2 & A3 respectively.
If the name is John, Joining date = 01/01/2012, Resignation Date 06/06/13
I need a formula which highlights the cell A2 plus all those cells which have the same date when i entered a date (i.e 01/01/2012) in another cell.
View 2 Replies
View Related
