Marking A Column Based On Date In Another Column
I have a worksheet that keeps track of members for an organization. One column, let's call it B, notes the month when the members are due to renew their membership. Our organization requires that members be notified of pending renewals one month before, the month of, and one month after they're due to expire. Rather than having to re-sort the database every time I want to pull up this information, I'd like to create a second column, column C, that puts a character next to the names of members who are about to come do.
Ideally this character would change depending on what stage they are in the renewal process (a month before, month of, month after). Super ideally, if this is even possible in Excel, members who haven't renewed by the last month will automatically be moved to worksheet two, which contains recently lapsed members. What sort of code would I need to do this? I've tried googling for it, but I can't find quite what I'm looking for, and I have no script savvy whatsoever.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Count Filled Cells In One Column Based On Date In Another Column
I think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example: AB 1-May 1-May777 1-May 1-May 1-May 2-May999 3-May 2-May 1-May 2-May111 1-May If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.
View Replies!
View Related
Converting A Date Based Upon A Time In Column A..
I some assistance with converting a date based upon a time in column A. I have a number of dates in column A as follows: 01/13/2009 11:50 AM 01/05/2009 09:14 AM 01/12/2009 05:30 PM 01/12/2009 02:30 PM 01/12/2009 01:45 PM I need to convert these values to another date base upon the values in column A. If the time < 2:00 pm then leave date as is, if time > than 2:00 pm then add one day. The expected results in column B would be as follows 01/13/2009 01/05/2009 01/13/2009 01/13/2009 01/12/2009
View Replies!
View Related
Delete Rows Based On Date Column
modifying the following from deleting rows older than 365 days to delete rows older than 12 months. So I can keep a rolling year by month. Sub DeleteOldSR() Dim x As Long Dim iCol As Integer Application. ScreenUpdating = False iCol = 7 'Filter on column G (Create Date) For x = Cells(Cells.Rows.Count, iCol).End(xlUp).Row To 2 Step -1 s = Cells(x, 3).Value If s Like "Closed" Or s Like "Closed w/o Customer Confirm" Then If Cells(x, iCol).Value < (Date - 365) Then Cells(x, iCol).EntireRow.Delete End If End If Next Application.ScreenUpdating = True End Sub I tried modifying the IF statement using month with the following but it seems to delete all months < and is not year dependant. If Month(Cells(x, iCol).Value) < Month(Date) - 12 Then
View Replies!
View Related
Highlight An Entire Column Based On A Cell Date.
I want to highlight an entire column based on whether a cell in that column is equal to today's date. I know how to write a loop that cycles through all of the columns until it finds today's date and then modify the column's properties, I was wondering if there was an easier way to do it. Also, I want the highlighting to be temporary meaning it should not save.
View Replies!
View Related
Sum Up Column Based On Text & Date In 2 Others
I'm trying to create a SUMIF-style formula which is able to test for more than one condition. i.e if the values in column C meet a certain criteria and if the values in column E satisfy another criteria then add all the corresponding values in column D. In my particular spreadsheet, column C contains a date value. So, for all the dates in column C which are later than 01/01/2008 and for all the cells in column E which contain the word "oustanding", I need to add up all the corresponding values in column D.
View Replies!
View Related
Hide Show Pivot Column Items Based On Date
I have a pivot table with the column fields as dates. I will like to be able to use VB to dictate which value to show. Here is brief code in which I just recorded... Sub Button1_Click() Range("B7").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date") .PivotItems("10/30/2006").Visible = False .PivotItems("10/31/2006").Visible = True End With End Sub I would like to show current day only using =today() or something similar. THis would eliminate the user having to modify the pivot tables daily.
View Replies!
View Related
If Statement/indexing: Column To Adjust Based On The Date Of The Transaction/trip
I have an employee expense voucher with a column that calculates mileage. I also have a column that lists what the mileage rate is. I'd like for that column to adjust based on the date of the transaction/trip. Here's an example. Trip between 7/1/08 - 12/31/08 = .585 mileage. Trip between 1/1/09 - 12/31/09 = .405 mileage. If someone turns in a receipt for a trip taken on 11/13/08, I want the form to adjust the mileage rate to .585. Wasn't sure if this is an indexing kind of thing, or if there's an if statement I could use.
View Replies!
View Related
Count Column W/blanks & Values Based On Specific Date
I am in desperate need of a function that will count a column of data where there are blanks and values based on a certain date that will also capture any data that is added after refreshing the table from Access. I have tried several functions but this is what I have: =(ROWS('TouchBack Detail'!$Q:$Q)*COLUMNS('TouchBack Detail'!$Q:$Q))+(COUNTIFS('TouchBack Detail'!$B:$B,'Nov TouchBack Summary'!B$1)). The result should be 3 but it’s including all other cells in the column that are not and should not be included in the refreshed table’s data (Table_TouchBack.accdb). I have attached the spreadsheet for review. The function is in cell B27 highlighted in yellow.
View Replies!
View Related
Configuring Dates: Enter A Date In One Column, Another Column Will Automatically Populate With The 1st Of The Next Month
how to make a certain type of date automate. It's kind of hard to explain, but basically, I'd like to make it so that when I enter a date in one column, another column will automatically populate with the 1st of the next month. For example: If I enter 4/26/2009 in the 1st column, column 2 will read: 5/1/2009 If I enter 1/19/2008 .................................................. 2/1/2008 Also, it's very important that if the FIRST date is already the first of the month, then the second column will read the same. For instance: If I enter 3/1/2009 in the first colum, the second column will ALSO read 3/1/2009.
View Replies!
View Related
VBA Code To Set Unknown Range Based On Current Date In 1 Column
i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).
View Replies!
View Related
VBA Loop Through Worksheet And Format Each Column As Text, Or Date Based On Header
I am trying to figure out a way to condense my code (improve my code) Right now I am using a Macro in Excel to go through a workbook by selecting each column individually and if there is something in the header then formatting that column as text. The problem with the way I am currently doing it, is that due to size limitations on the VBA code, I can only select columns A to Z, and if there is more data in the worksheet it is not formatted. Also I would like the code to check the header and if the word date is in there, format it as a date instead. Also if there is a way to delete any invalid ranges in the workbook Sub Format() Dim VarFileName As String Dim VarPath As String Dim VarSavein As String Dim wsheet As Worksheet VarSavein = Sheets("sheet1").Range("C2").Value VarFileName = Sheets("sheet1").Range("A2").Value VarPath = Sheets("sheet1").Range("B2").Value Workbooks.Open VarPath & VarFileName For Each wsheet In ActiveWorkbook.Worksheets Sheets(wsheet.Name).Select Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ := Array(1, 2), TrailingMinusNumbers:=True..........................................
View Replies!
View Related
Trying To Find Max Date In 1 Column For Selected Text In Another Column
I have 2 columns: Locations (Column A) & Dates (Column B). I want to find the most recent date for a selected Location (stored in $G$1). currently I have in C2: =If($A2=$G$1,$B2,"") copied down the column to the end of data (currently C153). Then in C1: =Max(C$2:C$153). I also have in D2: =If($C2<$C$1,$C2,"") copied down to D153, and in D1: =Max(D$2:D$153) to find the 2nd most recent date. Is there some way to condense this into just 2 cells? See attached file for example (note the columns are currently sorted by date, but that is not always the case).
View Replies!
View Related
VBA To Enter Text In Column After Date Entered In Other Column
I have the following code that fills a range of cells (starting at the active cell) with the date a user selects from a calendar control. Private Sub Calendar1_Click() ActiveCell.Value = Calendar1.Value Selection.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Selection.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Selection.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)....................
View Replies!
View Related
Formula To Check Column A For Date Range And Count Column B
I have a formula that counts if a date range is present. However I need to change it to count another column only if that date range is present. For example a17 a50000 the user will enter the date of the order. and in column B has the order number. I want the formula to count the order numbers for a data range in column A. Here is what I have but it is counting the dates in col A not the order numbers in B?
View Replies!
View Related
Fill Month And Year Column Using Data From Date Column
I have 4 columns Column A is "Customer Number" Column B is "Date Can Opened" Column C is "Month" (currently blank except header row) Column D is "Year" (currently blank except header row) I would like a macro to do 2 things here.... 1. Extract the Month part of the "Date Can Opened" column and put it in column C. 2. Extract the Year part of the "Date Can Opened" column and put it in column D. Currently, the "Date Can Opened" column is formatted as a date like */14/01 taken from the Number tab in the Format Cells dialog box) ...
View Replies!
View Related
Multiple Date Column To Single Column & Sort
I'm looking for a way to sort dates from several columns into a new single column (perhaps multiple columns if the entry columns become too numerous). I've included an example. There are currently only 4 columns, but there may be as many as 20 in the future, each with 20 dates under each heading. Any blank cells would be eliminated. If I filled a blank with a new date, that date would be placed into the chronological column. So basically, this would take the date from several different categories and create a single calendar of events.
View Replies!
View Related
Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.
I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in. I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.
View Replies!
View Related
Calculate The Average Of A Group Cells In One Column Based On The Condition Of Another Column
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days 17, 4 17, 3 17, 5 26, 4 26, 8 26, 10 26, 7 On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
View Replies!
View Related
Range Object/property: Calculates 2nd Column Based On 3rd Column's Number
I'm trying to write a code that calculates 2nd column based on 3rd column's number or vice versa based on the condition set on the 1st column. Below, there are two procedures. "SimpleCalc" and "SimpleCalc2". I first wrote SimpleCalc2, but it isn't working, so I worked around the error by writing SimpleCalc, which selects a cell and moves around by offset. I personally find it hard to read and error prone as I develop more logic around it. I'm trying to develop more function based on this code, so I need to make it neat and flexible. what I'm doing wrong in SimpleCalc2? Or do you have any suggestion to improve the code "SimpleCalc"? I'm using Excel 2003. Sub SimpleCalc() Dim SimpleMethodRng, SimpleMethod As String, i As Integer ActiveWorkbook.Worksheets("Dashboard").Range("P5").Select SimpleMethodRng = ActiveWorkbook.Worksheets("Dashboard").Range("N5:P12") For i = 1 To 8 SimpleMethod = SimpleMethodRng(i, 1).........
View Replies!
View Related
Function To Sum A Column's Entries Based On Another Column
Let's say I have a dataset with blank or X in column A, and a dollar value in column B. I want to sum the dollar amounts for column B only if there is a corresponding X in that row in column A. The way I currently do that is by creating a new column C, making a if statement to display the value of B only if there is an entry in A, and then doing operations on that new range. I figure there has to be an easier way. I don't know how to use VLOOKUP, despite trying to read the helpme a bunch of times. Is that relevant?
View Replies!
View Related
Transpose Multi-Column Groups/Repeats Based On 1 Column
I have a sheet with 3,000 rows and only two columns. Column "A" consists of 20 to 30 different names, column "B" consists of 50 to 60 different products. I need to be able to evalute the value in column A and copy, paste special transpose all values in Column B that have the same value in column A. As an example if cells A1 through A5 is "Arizona" and cell B1 is Broccoli, B2 is Cauliflower, B3 is Apples, B4 is Oranges, and B5 is Bananas, I want to copy B1 through B5 and paste special transpose to cell C1. This then would need to loop all the way to the bottom of the data in Column A looking for a change in value. The attached file called Sample Data has two tabs. The one titled "report" shows the raw data, the one titled "Final" shows how I would like the results to appear (column L)
View Replies!
View Related
If (Column A) Within Date Range Then Add Number In Column C
I'm still confused regarding a complex formula - rather n00b. Using Office 2007 with XP. Can't figure out with help or previous questions. The following is what I would like this formula to accomplish. Picture included below. Take all the values of column of C and add them together if and only if the corresponding date value in column A is during the month of January (and February, March, and so forth). Event DateNameTotal Amount2/21/2009$4,5002/22/2009$04/3/2009$04/25/2009$9,0004/30/2009$8,3005/2/2009$9,0005/7/2009$5,5005/9/2009$9,0005/12/2009$3,000
View Replies!
View Related
Sum Numbers In Column If Date In Other Column Matches
I have a spreadsheet which will be completed by numerous users, with a worksheet reserved for each area. The spreadsheet is to record the number of days lost to training etc on a weekly basis. Each worksheet has 3 columns – column A DESCRIPTION, column B WEEK COMMENCING DATE and column C DAYS LOST. The table will be completed by the manager’s as the info becomes available to them. I will be collating the data on another worksheet and need a formula that will look in column B for all instances of 01/10/07 and then sum the corresponding cells in column C, then do the same for 08/10/07 and so on. I have attached an example of a page. I thought it may be VLookup or Sumif, but I don’t know how to go about it.
View Replies!
View Related
Color Fonts In Column G Based On Contents In Column E
I have created an Excel spreadsheet teachers schedule for a small school with 8 teachers. I have assigned a number to each teacher (1 - 8) so that a number typed in a cell in Column E will cause a teachers name to appear in a cell in Column G. The ranges are E3:E20 and G3:G20. I hope to find a Macro that will display each teachers name in a different color.
View Replies!
View Related
Count Data In Column X Based On Conditions In Column Y
I am wanting to count the number of records (excluding cells with no value) based on criteria in a corrosponding column. In column "AS" I have a number of records that are not sorted showing values "7", "13" and "2". In column "AL" there are values attached to some of these records based on certain IF statement conditions. I am wanting to count and sum the number of records in column "AL" that meet the conditions of "7" in column "AS" and so forth.
View Replies!
View Related
Condition Paste Formula In Column N Based On Column A
I am wanting to paste formula from N1 till N X (X = varaiable row) where X reliant on Column A Row X. If Column A Row X has character "=====" it should paste N1 Formula all the way down till N Row X which is equal to A Row X containing "=====" . I might be sounding complicated over here but it is a simple equation.. I have tried to approach this in the capacity i could by condition if Column A row x is blank delete the row.
View Replies!
View Related
Auto Fill Column B Based Contents Of Column A
I have a list that is streets and addresses. All contained in column A. Cell A1 is the street name and then Column A3 is the street number. This repeats down column A for almost 1000 street names. I need to fill column B3 with the street name, as well as B4, B5, B6, etc until the street name changes. I was trying to do this with an if..then but couldn't get it to work. I also tried to work on a do.. loop looking for the change from a string to number. But my programming is a little rusty. If anyone can help I was be forever grateful. I mean the alternative is to sit here and copy and paste all day. ABBEY CT 1700 1700 1703 1703 ABERDEEN LN 1305 1313 1321 1321
View Replies!
View Related
Copy Cells In One Column Based On Criteria In Another Column
I have an personnel file with employee info, and I want to create a macro that will look in the "Master" worksheet at the Department column ("I") for anyone in Benefits, and then copy their name from the Name column ("D") into the "Benefits" worksheet. The names should begin pasting in cell "D3" but will recognize if a cell already has a name in it and then paste in the cell below that. Here is what I have: Option Explicit Public Sub RatingbyDept() Dim Dept As Range With Sheets("Master") For Each Dept In .Range("I2:I1000") With Dept I keep getting error 92 - "For loop not initialized".
View Replies!
View Related
Insert Date From Column D, Into Column A, 2 Rows Up
Following up from Insert Blank Rows Where Date In Column D Changes from the first populated row after the inserted rows, and paste it in the 1st blank row above the populated rows (as a kind of title). How would I build this into the code below? Private Sub CommandButton1_Click() For x = Range("D65536").End(xlUp).Row To 7 Step -1 If Range("D" & x) <> Range("D" & x - 1) Then Range("D" & x, "D" & x + 3).EntireRow.insert Next End Sub
View Replies!
View Related
Sum Column Based On Input From Another Column
This has to be easier than I am making it out to be! I have column C as my selection column...so basically if they would like to see any items in rows 7-60, they can put an X in Column C and see an aggregate of those items. What formula would I use to do the aggregate that is determined by input into column c?
View Replies!
View Related
How To Create A Cumulative Column Based Upon Another Column
I have a model built that calculates the daily Profit and loss ('Data for Pivot' tab, column M). I would like to calculate the cumulative profit and loss and am having a difficult time doing this with a simple formula. The Daily P/L column is empty (#NA value is in the cells) up until cell C138 as there is no data before that (which is the way it is supposed to be). However, once data has begun to be populated the daily profit and loss is shown (in this case from M138 onwards). The issue I am having is that I cannot seem to be able to create a formula that will capture the cumulative P/L. My desired result is evident in cells N179 onwards, as it simply captures the cumulative p/l. However, this only works if I change the formula once data is populated (in this case cell N177 is where i put in a new formula; the previous formula can be seen in cells N3-N176). If I attempt to put in the formula that is in cell N177 for all of the cells in column N, it gives me an error. Thus, I think that this may need to be programmed via VBA, but I have not been successful in doing this. The issue is that the data population in this model begins on different cells based on the different inputs that I put in ('Mov_Avg_Chart' tab, cells c6 and c8). Thus, I can't hard-code a formula based on a particular cell. If anyone can help me with this,
View Replies!
View Related
Comments On Column G Based On Information On Column A
Am trying to put comments on column G based on information on column A. Column A contains a grade eg CDEF picked up from another sheet using vlookup. This grade is suppossed to be the same, but I have cases where I add a different grade say GHIJ to fill up my 99 required grades.Column G is for comments such as "Transfered" (which at the moment am entering manually)How can I use a formular so that each time I transfer from another grade other that the one intended it returns a comment "tranfered" on column G? Is it also possible to indicate the cell from which vlookup picked that grade on the same comment column G.
View Replies!
View Related
Copy Range Column Based On Other Column
in column B of sheet1 i have every 3rd-4th row a name. i need to copy the name and the the data on the row below the name to sheet2, the list of names can be min 2 max 30. so sheet 1 has row 14- name in column B and C. in row 15 in columns d:z data for the name. i need to copy this to one row in sheet 2 copied for each name up to 30.
View Replies!
View Related
Autofill Column B Based On Column A Entries
I have a Listbox that outputs data to Column A. Is there a way to auto populate Column B with 4 items for each Entry in Column A? The 4 items that will populate in Column B will always be the same. I have attached an example to better explain what I am trying to describe.
View Replies!
View Related
Return Uniqe From Column B Based Column A
i tried to search on forum abou unique function but i didnt find what i need. I have 2 columns - column A - is "ID" and column B is "name". I want results in column C. I need formula, which look at ID and give me in C in the same row the unique „name“ from B. If is the name by this ID first time in column, it return the name. If it second or more, it return blank.
View Replies!
View Related
Fill Column Based On Column In Another Workbook
I am looking for a macro that will fill the column C by looking at column A at another workbook. For example: Full workbook: A(column that will look) B C blala blabla Blabla Workbook missing entries in C row: A B C blabla blabla Null So the workbook that missing entries in Column C,will look for it,by looking at at column A,wich is the same at full workbook. Here the workbook wich the values will be taken at Column A, named as archive And the the workbook (named 900066_027_2373.xls)wich the column C will be filled,by looking at column A.Then find that code in the "archive.xls".See what has in it's A cell.Copy it.Paste it in current cell. Attached Files
View Replies!
View Related
|
TRACKER
