Macro To Concatenate Based On Entered Date
Sep 3, 2007
I have attached a spreadsheet illustrating my query.
I would like to run a sub procedure called generatedataset then I'd like to enter a date from column J (starting from J4) into G12 ( cell G12 is a named range called effective_date). Then enter the concatenation of the adjacent cells K4 and L4 and place this into cell G9 (cell G9 is a named range called dataset_name). Then call the procedure again. Repeating for all values in range starting at J4 extending down, and for all corresponding concatenated values in K and L.
Data would always be taken from columns J or K&L and placed in the same cells ie G12 (named range effective_date) and G9 (named range dataset_name) respectively.
View 5 Replies
ADVERTISEMENT
Jul 19, 2014
I own 3 restaurants and I use Excel to track my sales statics. I have to enter certain pieces of data on a daily basis and it can become slightly cumbersome when dealing with the sort of washed out look of Excel spreadsheets. I wanted to write a Windows application that would handle the process for me but my knowledge of programming languages is too limited and I don't really have the time to learn. What I'd like is to have a single page on my workbooks with which I can enter the data in on a field type view, and have it go to the appropriate cell on the "Raw" data sheets in the same workbook. For example:
Let's assume this is the Raw data sheet. I currently enter the data that I need here, and Excel does the rest for me. This gets hard to look at everyday when dealing with hundreds of numbers.
Net Sales
Taxes
Prior Year Sales
Percent Increase/Decrease
[Code].....
Entering the data here would automatically fill in the data on the Raw sheet, and would know which cells to put the data into based on the date, which would of course be gotten from the =Today() formula.
View 4 Replies
View Related
Jun 20, 2006
What i want to do is copy all records from whatever date i enter, onto sheet test. The full excel file has over 80 worksheets for each individual rep, the example i attached has 8 sheets..
View 9 Replies
View Related
Feb 11, 2008
i wanted to create a macro that will autofilter my starting dates in I to the newest date entered. So ive recorded the macro: ....
View 9 Replies
View Related
Jul 23, 2014
I have a table that looks like:
7300
3
7301
2
7302
5
And I am trying to make a macro that will go concatenate the first 3 columns of all rows in worksheet 7300, the first two columns of all rows in worksheets 7301, etc, and insert them into column A of each corresponding worksheet.
View 7 Replies
View Related
Dec 23, 2009
I need a formula that will basically just display a 1 or 0 in one cell based on the date in the cell next to it.
If the date is older than 180 days then a 0 will be displayed in the cell.
If the date is less than 180 days then a 1 will be displayed in the cell.
I have an attachement as an example.
View 4 Replies
View Related
May 19, 2009
I need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:
5/10/2009 to today is -9
5/22/2009 to today is 3
View 2 Replies
View Related
Aug 14, 2012
I have a input box that prompts a user to enter a date of a new month - it has to be the 1st of a new month. I have validation that it is a date that has been entered but then i want to validate the date entered is a month ahead of a date in a cell range on a sheet.
It is a monthly reset so it has to roll on from the previous month.
Here is what i have currently but it isn't working.
Code:
' Get user to input the first day of the new month to populate all dates with
dNewMonth = InputBox(Prompt:="Enter first Day of the new Month. Must be the 1st of the Month e.g. 01/10/2012", _
Title:="Enter Date")
' Validates the entered date is a valid date
If (IsDate(dNewMonth) = False) Then
[Code]...
View 1 Replies
View Related
Jan 17, 2013
Cell B1 contains a date, then B2 contains a formula that says:
=IF(A1>TODAY(),A1,A2)
A1 contains a green tick and A2 contains a red cross.
What I am trying to add is that if B1 contains no date then B2 needs to be blank.
I tried using =IF((A1="",0),(A1>TODAY(),A1,A2) to get it to show a 0 if there was no data but this doesn't work.
I am using Excel 2003.
View 4 Replies
View Related
Jun 25, 2014
I have a lost and found log that has 2 worksheets: (1) Unresolved and (2) Resolved. Data is entered into the Unresolved worksheet when something is lost or found. When a date is entered under Date Claimed / Sent to Capitol Police (column I), I would like the data in that row to be removed from the Unresolved worksheet and automatically inserted in the next blank row of the Resolved worksheet. I know very little VB,
Date Reported
Lost or Found?
Item Description
Name of Person Reporting Item
[Code]...
View 5 Replies
View Related
Mar 24, 2014
I'm using nested SUBSTITUTE formula to make some changes to the some of the data. I'm also using IFERROR to return the input if it does not find the criteria I have specified. I have lot of variations in my input data.
The formula works fine for all except for date format input.
Sample:
N90232Y09--->SUBSTITUTE(A1,"N","")-->90232Y09
12335--->12335
12/3/1923---->58936 (I want the date to be retained)
why the date format is changed even if don't specify any changes for it. I expect my IFERROR to just retain the input as it is.
View 4 Replies
View Related
Apr 15, 2014
I'm working on the final stage of a project. I'm attempting to write code that will set off a chain of events if two dates are 91+ days apart from each other . I've attached a sample worksheet that shows the bare bones basics of what I'm attempting to do.
What will be the most efficient and effective way of accomplishing my goal: userform, functions, formatting, etc.? T
View 14 Replies
View Related
Jan 24, 2007
I have two cells. Cell A1 contains ‘The date is ’ cell B1 is formatted in the date format, 2007-01-24. I need to concatenate these two cells so that on cell C1 I have ‘The date is 2007-01-24’ but whatever I try excel keeps on changing the date to a text value and hence I am left with: ‘The date is ‘This is the date'39141 or this is the date 39141. how to get to the result that I am after?
View 5 Replies
View Related
Jan 8, 2010
I'm having a problem getting the date to show up when I add the date as a referenced cell to a formula using CONCATENATE. The date shows up as a serial value instead of a date format. The formula that I'm trying to get to work is in cell J7 on the attached file.
View 3 Replies
View Related
Jun 28, 2007
How can I add a date stamp that will populate a field when text is entered into another cell but will not change every time I re-open the sheet. I have been using =if(B4="",NOW()) which changes each time though and as soon as text is entered it disappears. I want to capture the actual date that someone enters text into a cell and save that date in another cell???
View 5 Replies
View Related
Sep 26, 2007
I am using the concatenate function, however my date field, instead of maintaining its 08/07/07 state, changes to the annoying 5 digit 33935 number.
Even if I cut and paste it as special / values, it stays as the 5 digit number. Can this be avoided somehow?
View 9 Replies
View Related
Jul 8, 2014
I have to enter date into cells. It is being written from the lists of paper, where for date is entered only year (e.g. 1987), but I have to enter it into excel with day and month (the program working with data only recognizes this form; eg 01.01.1987).
I was thinking of writing a conditional formatting, to remind if I will forget to write 01.01. before year.
I prepared formula like this (to check for the dots):
=IF(AND(MID(AD2;3;1)=".";MID(AD2;6;1)=".");1;0)
But it is not working, because the function recognizes general formatting of cell instead of date formatting (28491 instead of 01.01.1987).
View 2 Replies
View Related
May 4, 2006
I have been given a spreadsheet to "fix". We have 15 classes set up on it and we enter the dates of classes which then copy over into the rest of the classes using the "=+ cell" that contains the date we enter. There are 23 cells for date (maximum number of days we have class). If you enter class dates using 15 cells, the remaining cells on the second and subsequent classes get a 1/0/00 value which I then have to delete.
View 5 Replies
View Related
Feb 2, 2009
I am concatenating a date with some text. However, after concatenating, the date becomes a text string. How do I preserve the date format? Pls see the attached file.
View 3 Replies
View Related
Apr 7, 2009
i have this concatenate problem from dates... see the attached file. if i copy and paste as special value.. it puts the date values aswell.. i cant seem to put there only numbers.. if i wanted to use concatenate..
View 2 Replies
View Related
Feb 25, 2007
I have a date, 03/17/2007, in column A
I have the word, Cashiers in column B
I want to concatenate the 2 and use this in a vlookup. Can this be done? If so could someone explain please?
I keep getting the N/A error on this.
View 9 Replies
View Related
Apr 12, 2007
when i concatenate two cells (the word "excel" and today's date), i get
excel 39183...? i would like: excel 4/11/2007
View 2 Replies
View Related
Feb 6, 2013
I worked out the code below. I supposed to check the date entered in the text box, if that date is greater than the date in cell A2, is supposed to pop up a message box, but is only half working.
For example if I enter 3/1/2013, the code works, but if I enter 1/1/2014 it will not work, it thinks the date is OK and goes on to to the Else statement. In other words, is only looking at the first part (month and day) of my date and ignoring the fact that the year is in the future.
VB:
Dim frmDate As String
Dim toDate As String
Set ws = Sheets("Dashboard")
frmDate = frmEnterDate.txtReportEffectiveDate.Value
toDate = frmEnterDate.txtReportEffectiveLastDay.Value
[Code].....
View 2 Replies
View Related
May 19, 2014
how to switch cells per date entered?
View 3 Replies
View Related
May 7, 2014
One of our interns collected some data for me but they entered the dates "2 14 2014". I need to convert these into a usable date format in hurry.
View 3 Replies
View Related
Oct 22, 2010
I am looking for a way to auto sort the info in column A based on the numerical account number. The auto sort also should move the rest of the info in the row along with column a. I want this auto sort to happen once a new entry has been entered with all the information in columns A -L.
Basically the goal is someone with very little knowledge about excel or computers in general can enter information in all 12 columns and then the table will auto sort itself based on the account number.
View 6 Replies
View Related
Feb 10, 2014
I have a file in which records are saved in chronological order. What i want is when i click on a button it should prompt me for a date and when i enter the date it should retrieve the data from the database sorted in chronological order from the date i have entered.
View 10 Replies
View Related
Jul 24, 2007
i'm trying to form a macro that allows the user to enter a date and have excel auto filter for all dates prior to (or ON and prior to if it makes it easier) that date, and delete all rows with that auto filter criteria in place. i'm working with a old auto filter macro i put together, but cant seem to get the criteria to work with, say, <7/14/2007 for example.
Attribute VB_Name = "modDeleteVal"
Sub DeleteRowByValue()
Attribute DeleteRowByValue.VB_Description = "Macro recorded 7/9/2007 by KHenzel"
Attribute DeleteRowByValue.VB_ProcData.VB_Invoke_Func = "V
14"
'
' DeleteRowByValue Macro
' Macro recorded 7/9/2007 by KHenzel
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Dim rTable As Range
Dim lCol As Long
Dim vCriteria
On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else...
View 6 Replies
View Related
May 21, 2008
I have a form that will allow the user to type in a date. I need to take that value and validate that it is between 01/01/2007 and 10 years ahead of the current day.
View 4 Replies
View Related
Sep 3, 2008
how can i merge Date 1 and Time 1 into a cell ? And give me mm/dd/yyyy hh:mm format? And then a subtraction on 9/3/2008 0:00:40 - 9/2/200823:31:25, which give me a hh:mm:ss result?
View 5 Replies
View Related