Enter Custom Date Range To Show Data
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Show Custom Error Message When No Data To Paste
Below is a macro I recorded to help copy 'Paste Special - values only' data between two spreadsheets that have different colour and conditional formatting in cells. However, if this is run and there is no data to paste, it throws up the End and Debug error. Any chance that someone knows that if there is no data sitting on the Clipboard, then a msg box would appear saying "Please select the data you require to be copied" in the message box. Sub data_input() ActiveSheet. Unprotect Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B5:AG4804").Select Selection.sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("B5").Select ActiveSheet.Protect End Sub
View Replies!
View Related
Enter Date If Data Changes
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 Replies!
View Related
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc. I added a new cell called Track Back on the employee search sheet. What I want to do is only show variances for the amount of days back selected in the Track Back cell. For example if I select the last 30 days, only the last 30 days would show up below in the sheet. I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
View Replies!
View Related
Date Range (formula Show A Week Number In The 2nd Table )
I have two tables: the 1st table consists of date range (From and To) and week number while the other table has only dates. Example: 1st Table FROM TO WK 3/27/2009 4/2/200914 4/3/2009 4/9/200915 4/10/2009 4/16/200916 4/17/2009 4/23/200917 4/24/2009 4/30/200918 2nd Table DATE 03/28/2009 04/11/2009 04/26/2009 Need simple formula that would show a wk number in the 2nd table (2nd column)? I.e 03/28/2009 has wk no. 14, etc.
View Replies!
View Related
Vba To Show Data When Date Matches
Looking for an excel worksheet to keep a record of |MOT's due By entering the Name, Reg, Date, Comments, Reminder by way of a userform after the data is entered it is stored on anther worksheet.. every morning when they get to work. they want to open it and on the MotDataEntry sheet it needs to bring up the Mot's that are due in a months time (reminder) ,name reg etc
View Replies!
View Related
How Can I Get The Sheet To Show The Next Expected Date That The Data May Appear In That Column
I have some more questions about excel formula. In one column I have data appearing, as either a 1 or 2, at random as I input it. Each column has around 120 rows. In the row at the bottom I created a formula to calculate how often, on average, the data appears in that column. 1st how do I get each row to represent a date. 2nd how can I get the sheet to show the next expected date that the data may appear in that column, taking into account the date it last appeared and the average between appearances?
View Replies!
View Related
OFFSET With Today's Date To Show Last 6 Months Data
I have a data table with monthly data in columns (65 rows deep), with the months (in format dd/mm/yyyy but showing as Dec 05) running across Row 4. I want to be able to use OFFSET to identify the current and previous 5 months, in order to dynamically chart various items in the last 6 months worth of data. The charting bit I'm okay with, and I realise I need to assign Names for this to work, but I'm struggling with the OFFSET & date combination. I have the following but it starts from a defined reference cell; =OFFSET('BO Data'!$L$4,0,('BO Data'!$4:$4)-1,1,-6)
View Replies!
View Related
Show Custom View Upon Exit
I am trying to set a custom view when exiting, but I keep getting runtime error 424 saying an object is required. I am attempting to unprotect the sheets, check the value of an option button, set the custom view based on that value, and then protect the sheets. Please keep in mind that the user may or may not be on the sheet containing the option button when exiting. Here is the code I am having trouble with. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call UnprotectSheets 'Unlock to set option button value With Worksheets("Customer_Info") If btnExistCust.Value = True Then ActiveWorkbook.CustomViews("Sales_Exist").Show Else ActiveWorkbook.CustomViews("Sales_New").Show End If End With Call ProtectSheets 'Call the sub before closing End Sub
View Replies!
View Related
Hide/Show Pivot Table Data Based On Date Criteria
I have looked at the below Hide/Show Pivot Table Field Items help web pages: Hide/Show Pivot Table Field Items Hide Pivot Table Fields Pivot Items by Criteria I am trying to use the above, but with dates in the following format in each cell: YYYYMM 200612 200701 200702 200703 200704 200705 200706 etc, etc The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters <200706, instead the code goes through to the “NonValidCriteria” prompt. Sub HideByCriteriaYYYYMM() 'Declare variables 'SEE: [url] 'SEE: [url] Dim pt As PivotTable, pi As PivotItem Dim lMonth As Long Dim strCri As String, strCri1 As String, strCri2 As String Dim bHide As Boolean Dim xlCalc As XlCalculation
View Replies!
View Related
Custom Functions Show #VALUE! After Macro Has Run
Recently I found this forum through a Google search along with the perfect solution to a problem I was having finding and replacing text based on a table of replacement values. Here is the thread: Multiple substitute or replace text using a table I incorporated the SuperSub function that steveorg developed as a result of that thread into my worksheet by creating a new module and pasting the following code into it: Function SuperSub(OriginalText As String, rngOldText As Range) Dim cel As Range Dim strOldText As String, strNewText As String ' loop through list of old_text used in substitute For Each cel In rngOldText.Cells strOldText = cel.Value strNewText = cel.Offset(0, 1).Value OriginalText = Application.WorksheetFunction.Substitute(OriginalText, strOldText, strNewText) Next cel SuperSub = OriginalText End Function It works like a charm as a formula, but I also have a macro in the same workbook that, as soon as it selects the sheet with the formula, calls the SuperSub function and starts executing the code as a macro (even though there is no call to this routine in the macro). The macro ran just fine before incorporating the function into my worksheet. The formula looks like this: =TRIM((supersub( UPPER(E2),rngSubst))) If cell E2 contains the string "101 North Main Street, Apartment 5", it would return "101 N MAIN ST APT 5" Here is the macro: Sub Import() ' Sheets("Shoebuy FTP").Select Range("A2:R200").ClearContents 'This is where it jumps to Function SuperSub(OriginalText As String, rngOldText As Range) The function runs for every occurrence of the formula in the worksheet, and when it is finished, all of the cells containing that formula show a value of "#VALUE!". I should mention that it does not change the formula at all, but I then have to highlight each cell that contains the formula, press <F2> to edit, then <ENTER> to get it to display the correct results again. It is probably some stupid little thing that I didn't do when I created the function, but I can't figure it out. I have written numerous macros, but this is the first time I have ever created a custom function.
View Replies!
View Related
Display/Show Custom Error Message
I have a userform with a combobox (cmbSearch) , a textbox (textbox1) and a listbox (listbox1)....and a commandbutton (cmbFind). This userform is used for searching records in my worksheet....where user selects a "Search By" category from the combobox and then enters relevent text search criteria in the textbox....and all the search results are shown in the listbox. This part works fine untill someone tries to search a "non-existent" record. e.g. a customer name thats not in the database and it gives an error. I would incorporate in my code to have a Message box pop up on these type of searches saying "textbox1.value Not Found!" insetad of error message. Private Sub cmbFind_Click() Set c = Range("a65536").End(xlUp).Offset(1, 0) 'write userform entries to database Dim DataSH As Worksheet Set DataSH = Sheets("ComplaintData") With DataSH .Range("L1").Value = cmbSearch.Value .Range("L2").Value = TextBox1.Text .Range("N1"). CurrentRegion.Clear .Range("A1").CurrentRegion. AdvancedFilter Action:=xlFilterCopy, criteriarange:=.Range("L1:L2"), copytorange:=.Range("N1") End With lstSearch.RowSource = vbNullString lstSearch.RowSource = "outdata" End Sub
View Replies!
View Related
Custom Number Format :: To Contain (and Show) Both An Interger And Text
I would like to format a row of cells. I want each cell in the row to contain (and show) both an interger and text. The interger will range in value from one to ~10,000. The text will always be two characters long...but these characters will change from cell to cell. Elsewhere in the spreadsheet I will have a cell (w/ a formula) that counts the values of the intergers in the row, but ignores the text in each cell. a procedure for format ing the cells in the row?
View Replies!
View Related
Show/Hide Specific Columns For Custom Views
I have a spreadsheet with four columns of data for every month (i.e. January has a Prior Year, Budget, Outlook, and Actual column). I have been trying to come up with a macro or form that will allow me to customize which columns I would like to see and hide the rest. For example, if I chose to see the Budget and Actual columns, it would hide the Prior Year and Outlook columns for each month.
View Replies!
View Related
Show UserForm Before Printing For Custom View Choice
When I click the excel printing icon, I want a userform to pop up in which I make the choice for a certain customview that I build. This customview must then be printout. Note that I want to use the standard Excel print icon, not a custommade one in the worksheet. I figured out some code, but it doesn't work. For instance my if structure returns "false" even if I did click that commandbutton, see the code below. Private Sub Workbook_BeforePrint(Cancel As Boolean) Static PrintRequest As Boolean formMyview.Show If formMyview.CmdCancel= True Then MsgBox "printrequest canceled" Unload Me cancel=True Exit Sub End If If PrintRequest = True Then Exit Sub End If If formMyview.CmdOk = True Then For Each Myoption In frameViewoptions.Controls If Myoption.Value = True Then..........................
View Replies!
View Related
UserForm Text Box To Show Range Data
I have created a User Form with required controls to add data in a worksheet and it works fine. Now, I want to retieve data from the hidden worksheet and to view the data in one or two text box controls by clicking a command button placed on the user form. I expect the user to use the user form only.
View Replies!
View Related
Show Custom Error Message If Opening Workbook Macro Code Fails
I cannot figure out how to get my error handler to work, or actually, not work. It seems to work fine when there is an error, but the code still gets read even when there was not an error. Basically, I am trying to open a file, which may or may not be there. When it is not there I want a message to pop up informing the user. However, when the file is there and it opens, the error handler still gives the message box. Any ideas what I am doing wrong? Private Sub btnOK_Click() Application. ScreenUpdating = False Dim LCSfile As String LCSfile = frmSelectFile.Listbox1.Value On Error Goto ErrHandler Workbooks.Open Filename:=sPath & sDate & "" & LCSfile & "QUANT.CSV" ErrHandler: MsgBox ("File is not quantitated. Please select another file.") Application.ScreenUpdating = True End Sub
View Replies!
View Related
Sorting Data By Date Within A Date Range
Hello, I have a question about sorting information using dates but using a range. I need this to sort when I enter a date range into the spreadsheet (the blue blocks on the attached spreadsheet). The factors in colum G need to be sorted by month and input into the yellow highlighted area in column B. There may be an easier way of pulling the data from the "Data" sheet but this was the best I could figure out. Overall, January (or any of the months)needs to have the January (or appropriate) factor that fits within the date range that was input but sorted properly based on the months listed in Column A.
View Replies!
View Related
Enter Date Without Separators
I'd like to be able to enter a date in excel as just the characters without the dashes and have it correctly recognized -- such as 05142006 changes to 5/14/2006. I have a great deal of data to enter and it would go much faster if I could do it in this format. Is there a VBA script or function of excel that I could use for this? I've tried looking around on here a bit and in the program settings,
View Replies!
View Related
Auto Enter The Date
I would like to make a list of vehicles with auto servicing dates appearing in the next column. How can I do this, example would be say 30/11/2008 was the last service, next column would be next service due 30/02/ 2009 and then a column next to that to say when service was done. Pretty simple but I can't seem to get it to auto bring up the next service date which would be in 3 months time or 90 days.
View Replies!
View Related
Enter Ending Date
In my sample workbook, (attached), all I want to do is up a formula in column E that compares values in columns A, B and C. If they are the same, I want to put the last year model in Column E. I have filled in the first 12 rows to whow the desired result. Ultimately, for each part number and model combination, I want to put the final year for that combination in column E. The sample workbook constains just a few rows, but my master has about 30,000 rows.
View Replies!
View Related
Show Data In Cell Only If Other Cells Show Data
I have 2 different formulas that I need changed in a similar way. The first formula is for cell AV11: =SUM(BI11,BP11,BW11,CD11,CK11,CR11,CY11,DF11,DM11,DT11,EA11)+10 Every cell starts off blank. What I need is for cell AV11 to always start off blank until data is entered into one of the other cells. The problem is that since the sum always needs to be +10 only when data is entered in the other cells, I don't know how to keep 10 from showing in cell AV11 when no data is typed in the other cells. The other formula is for cell CO39: =(CU8)+3 I pretty much need the same thing. If no data is entered in cell CU8, then I do not want cell CO39 to show the 3.
View Replies!
View Related
Enter Date If Cells Not Empty
I'm having a hard time putting this into words, so I'll try to make it simple. I have a list with cells b1 thru f1 empty. If one or more of those cells becomes populated, I want today's date to show up in cell g1. Yes, the user could enter the date manually, but apparently that's too difficult for some people.
View Replies!
View Related
Enter Date Via Input Box
I would like to bring up an input box when a button is pressed containing the text "which number do you want to update". The user would then enter a number eg 101 and then by clicking ok, todays date (in the format MMM/YY) would be entered into cell J101 in Sheet5.
View Replies!
View Related
Enter A Date From An Input Box
I would like to enter a date from an input box(which i have entered in the code below), this would then lookup in column F and find all rows and copy them to relevant sheet 7 days from the date entered. E.g. if 24/11/2006 this would bring back all rows which from the 17/11/2006 Sub FindMe() Dim intS As Integer Dim rngC As Range Dim strToFind As String, FirstAddress As String Dim wSht As Worksheet Dim rngSearch As Range Dim strToFind1 As String Dim rngC1 As Range Application.ScreenUpdating = False intS = 1 Set rngSearch = Worksheets("Template").Range("a3:z20000") Set wSht = Worksheets("Closed Issues") strToFind = InputBox("Enter the title to find").................
View Replies!
View Related
Date Custom Format
How do you make custom date format to a cell in such a way that it appears e.g. "Jan-06"? I tried custom format "Mmm-yy" but it still resulted to "jan-06". I want the "J" here to be in the upper case.
View Replies!
View Related
Custom Date Formula
I have entered a date like May 21 in a cell. I want to enter a formula that will take that date and automatically add 7 days to it (after I create and run a macro of course). I just have no idea what formula to use to continually change the date from the 21st to the 28th to June 4th etc. each time I run the macro on a copied excel worksheet.
View Replies!
View Related
Custom Date Column
Fellow Forum Members, The formula below generates the dates for every Tuesday and Friday for every week of the year by doing a Fill Down operation. =A145+IF(WEEKDAY(A145)=3,3,4) My objective right now is to generate the dates for every Wednesday and Saturday for every week of the year by doing a fill down operation. What I can't figure out is how to I modify this formula to give me dates only for Wednesday and Saturday. The Help Excel provides for this function is not too clear. I would appreciate it if anyone can explain what is the logic being used for the key numbers "=3,3,4)" ? These numbers is what controls the days of the week that show up with dates, but the logic being used doesn't make sense to me.
View Replies!
View Related
Enter Date In Next Available Cell On Button Click
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 Replies!
View Related
Enter A Date And Return The Fiscal Month
Is there any way to defined our own fiscal month. I have a fiscal year where the start & end date is different from the normal calendar. I have store the start & end date on the different column. What i need is that when people enter a date, it will look up to the table and return the fiscal month.It's something like If the value is >=column A and <=column B, then the fiscal month=column C(refer to the below table). But i have no idea how to make comparison on date value. what kind of formula i should use? And also how to write the code if i want to use macro to implement this? Start End Month 09/21/08 10/18/08 Oct 10/19/08 11/22/08 Nov 11/23/08 12/27/08 Dec 12/28/08 01/24/09 Jan 01/25/09 02/21/09 Feb 02/22/09 03/28/09 Mar 03/29/09 04/25/09 Apr 04/26/09 05/23/09 May 05/24/09 06/27/09 Jun 06/28/09 07/25/09 Jul 07/26/09 08/22/09 Aug 08/23/09 09/26/09 Sep
View Replies!
View Related
A Column Of Cells Where Manually Enter In A Date
I have a sheet where I have a column of cells where I manually enter in a date. the format is d-mmm-yy. Before the end of the year rolled around I was able to enter in say only 12-31 and it would automatically change that to 12-dec-09, now when I do that it spits out 12-dec-10. It's not a big deal to type in the extra -09 to get it right but was wondering if there was a quick/easy fix to get it to know that I don't want a date in the future? All dates I type in will be in the recent past...never more than 3 months or so old and never later than today's date.
View Replies!
View Related
Form To Enter Date When Button Clicked
I am looking to create a form that when a user enters data in it, and clicks a save button it will submit that data into cell A2, and then with the next time the enter data it will submit that data to cell A3. I would like it to continue moving down a cell each time somone hits a "submit" button.
View Replies!
View Related
Custom Date Picker Control
I'm building a userform excel application and am using the MS date/time picker control. I've run into the familiar "computer doesn't have OCX" problem and want to avoid using a custom OCX. I was thinking of just making my own control instead. Has anyone made one in VBA? I found some VB examples which I could port over, but they use a lot of api calls. For reliability, I'd like to keep this as simple as possible though.
View Replies!
View Related
Custom Date Format: For 2 Dates
I want to display 2 dates in one cell and have them custom formatted. Eg. Display in one cell: 26 Jun - 27 Jun With text typed: 26-6 - 27-6 (Or something along the lines of) I know that dd mmm will display one date. But I cannot get 2 dates formatted. I tried dd mmm; dd mmm" - "dd mmm, which didn't work.
View Replies!
View Related
Show Only The Date That Corresponds With The Current Date?
How do I get cell B1 to show only the date that corresponds with the current date? ex. A1 15-Nov 26-Nov 29-Nov 2-Dec 4-Dec 4-Dec 5-Dec 6-Dec If I enter 12/2, in one of the rows in column A it will display today dates in cell B1 since today is 12/2, but when tomorrows comes, since there is not a 12/3 date in column A, B1 cell will be empty, but when 12/4 comes, cell B1 will show 12/4 since that is a 12/4 date
View Replies!
View Related
Selecting Data Over A Date Range
I have 2 worksheets where I need to transfer a certain amount of data from one to another depending on dates. Sheet ‘A’, is the main data source. Col A holds all 365 dates from 1 Jan to 31 Dec(starting at A2), and the subsequent data for each day is held in cols B to P. Sheet ‘B’ is the destination where the user will specify a key date in cell A3, then in A4 to A32, formulae populate the next 29 dates (ie so there is a continuous run of 30 days). What I need to do, is to identify the same 30 day range from sheet B on sheet A, then to copy the data in Cols B to P for that range back onto sheet B (pasting in at D3)
View Replies!
View Related
Extract Data Within Date Range
I have a an excel file which has 2 columns of data. 1st column indicates the date. 2nd column indicates the data associated with the date. I would want to create a code that is able to extract the dates that is able to let user select the date range and its associated data and copy it into another worksheet. But it seems that my below code fails. Can anybody give me some advice? For eg in the attachment file, the Raw Data worksheet contains all the infomation and i wish to copy all the data from 8thJuly07 to 30thJuly07 to another sheet named Edited Data. Sub date_choice() Dim i As Integer, j As Integer Dim end_date_last_occurence As Integer, No_of_end_date_occurence As Integer Dim strt_date_1st_occurence_row(1 To 10000) As Variant Dim end_date_1st_occurence_row(1 To 10000) As Variant Raw_data_last_row = Range("A" & Rows.Count).End(xlUp).Row 'Get the last row no Sheets(1).Name = "Raw Data" start_Date = InputBox("Enter Start Date", "Start Date", "dd,mm,yy") end_Date = InputBox("Enter End Date", "End Date", "dd,mm,yy")
View Replies!
View Related
Userform - Match Date And Enter Value At Adjacent Cell
I have this userform: DATE : [TextBox1] WITHDRAWAL: [TextBox2] [Commandbutton1] In my sheet I have a list of all the days in a year in the cell range of AJ12:AJ376. Basically I want to find the date in Textbox1 within the range of AJ12:AJ376 and enter the value of Textbox2 in a adjacent cell. The adjacent cell would be in column AT. This would be achieved hopefully via a code using the commandbutton.
View Replies!
View Related
Enter Date/time Cell Was Filled, And Then Leave It ()
I have a spreadsheet in which I want to track the date and time someone made an entry in an adjacent cell (so if someone enters something in B2, I want the date/time of that cell edit to appear in C2). When the date/time has appeared, I want it to remain the same and not update when the worksheet is opened the next time. So I can't use NOW to pull it in, because that's going to change every time the file is opened.
View Replies!
View Related
Enter Current Date Into Last Used Row Of Column Of Another Sheet
From sheet1 on a checkbox click I am attempting to post the system date into sheet3 cell c3. If c3 already contains data then I want the cell selection to go down to the next row and post the date there. I if cell c3 has no data then it posts fine but if I need to goto the next row then I get a runtime error "object doesn't support this property or method". If Me.GCN = True Then If Not IsEmpty(Worksheets("GCN_Paid").Range("c3")) Then Sheets(3).ActiveCell.Offset(1, 0).Select Sheets(3).Range("c3") = Date Else Sheets(3).Range("c3") = Date End If End If
View Replies!
View Related
Custom Formatting A Date In A Text Entry
I have a date in H34, 21/03/2009. In I34 I have the same date again, except formatted as MMMM YYYY so its showing "March 2009". In G10, I want the cell to read: Number at end of March 2009, with the month and year being taken from the date range. To be honest, I can get the year okay using the Year() function but cant get the month as cant convert it back from a numeric.
View Replies!
View Related
Custom Sorting A Column By Date....and Text
I need to custom sort a column. I have 3 different types of data in the column. First - multiple dates, Second - "TBA", and Third - "ASAP". What I need is when the column is sorted the "ASAP" rows will be first, the dates (sorted) will be next and finally the "TBA"s. I have been trying to use a custom list.
View Replies!
View Related
Custom Date Format To Be Retained In .csv Also
i have the required date as part of a string, which i need to pull out and then format it as "yyyy-mm-dd hh:mm:ss" and put it in another file which will be saved as .csv Here is the code i am trying to modify without much success so far Sub dateFormat() Dim dtMydate As Date Dim sMydate As String Dim strWhen As String strWhen = "As on 13-MAR-2007 14:45:59 Hours IST" ' the starting string 'strWhen = Cells. Find("As on") 'take out the date portion from strWhen strWhen = Mid(strWhen, InStr(strWhen, "-") - 2, (InStr(strWhen, "Hours") - 1) - (InStr(strWhen, "-") - 2)) dtMydate = CDate(strWhen) Debug.Print dtMydate 'returned value is 13/03/2007 2:45:59 PM dtMydate = Format(dtMydate, "yyyy-mm-dd hh:mm:ss") Debug.Print dtMydate 'returned value is again 13/03/2007 2:45:59 PM...
View Replies!
View Related
|
TRACKER
