Formula For Working Out And Populating Table Using Previous Month Totals?
Jun 24, 2014
I have an issue whereby I have thousands of lines of data. I need to bring back the "previous month" worth of data (along with some additional information) but not sure how to go about this.
The attached shows the example file. I need the following :
1. Take the data to populate the "Populate" tab
2. Using Column F (Invoice Date Created) to only bring back the "previous month data" - in this case it would be May 2014
3. The following formula is what I have currently for Column B of the "Populate" tab: =SUMPRODUCT(SUMIFS('Data'!$N:$N, 'Data'!$M:$M, "Invoice Payment Processed", 'Data'!$A:$A, A2))
4. The above formula brings back all the values - I only want the previous month.
5. I am then not sure how to calculate Column C - which is a count of the amount of invoices that make up the total
View 3 Replies
ADVERTISEMENT
Jan 9, 2010
Trying to word this right. I have one cell with a date of 01/01/2010. I have other cells that I want to be equal to this cell plus 1 or more months.
For example A1=01/01/2010
I want A2 to = 02/01/2010 based on one calendar month entered into A1. So if A1 changes 03/01/2010, A2 will = 04/01/2010.
View 3 Replies
View Related
Mar 14, 2007
Yesterday (13/03/2007) was the 9th working day of the month (basing a week on Mon – Fri) – today is the 10th working day
Is there any formula, or mix of formula’s I can use to automatically calculate this?
View 9 Replies
View Related
Feb 19, 2008
I have a month number in H2 (1-12). I want a formula that will give me the previous month number. So, if h2 = 1. I need my formula cell to equal 12.
View 9 Replies
View Related
Jan 22, 2010
I am using the following formula to show the first day of previous 6 month cycle.
=DATE(YEAR(TODAY()),MONTH(TODAY())-6,1)
That formula would display: 07/01/2009
I want to have another formula show the last day of the previous month.
Basically it would display: 12/31/2009
View 6 Replies
View Related
Jan 28, 2010
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
View 9 Replies
View Related
Jan 19, 2009
I am trying to create a formula to work out the last working day of each month, but I can't come up with anything that is less than the 1,024 formula limit. I am using a UK calendar so the only two holidays to effect the last working day of the month will be Easter and the last Monday in May.
This is the formula I am using which takes account of Easter, the checking for the last Monday in May is simple enough in another column, but I would rather keep it in one column, just wondering whether there is a simpler way of doing this.
A1 is a specified date and then I would like to work out the last working day of the month, on a new row, for up to the next 50 years....
View 9 Replies
View Related
Jun 27, 2008
formula using if's and vlookup's to populate a calendar with x's for day a person is gone. however the last couple of people that are gone are not being marked on the calendar even though i use the exact same formula (except for cell numbers and such) for previous, working entries. Here is my formula
View 9 Replies
View Related
Oct 2, 2009
I have to keep a record of the running totals of school house points for each week. The problem is that teachers are very lazy and don't record data every week so I have many blank cells which my current formula can't cope with. I've tried using N/A but it doesn’t seem to work? (Have thought about threats of violence but would probably lose my job) I’m if there is no data (blank or 0) then I need it to keep the same total in the cell as the previous week and so on until new data is entered and updates the total. I have attached a simplified copy: Teachers enter points in the HP sheet, the Running Totals sheet (TAB) contains the formula.
View 4 Replies
View Related
Jan 20, 2014
I need a formula to automatically change the summary column according to the month we are in.
Ie:
Last Months Data
Nov
Dec
Jan
Feb
1
8
1
7
4
7
9
2
9
'Last Months Data' column should show Dec. However, as we move into February and I complete the 'Jan' column, I would like 'Last Months Data' to automatically change to show Jan's data - is this possible?
I was previously told to use the following formula but this would automatically select the current months data and not the previous months data which is what i need - =INDEX(B2:L2,MONTH(TODAY()))
View 2 Replies
View Related
Oct 22, 2008
I have a file that tracks the number of days my employees take off each month. I want to be able to go back 6 months on a rolling basis to get the total number of days taken off.
I am trying to set my file up so that I can type in the month in cell R1 and have column S update for each employee with the total number of days he/she has taken off for the past six months. For example, if I type in OCT in R1, I would like to see S2 change to 6 and S3 change to 2.
View 2 Replies
View Related
Apr 11, 2009
I’m no expert with excel, so I might be going in the wrong direction with this completely, but I thought I had this working right, until I applied it to the next month. when going from January to February, I get two entries of 400 that I don’t want there, I understand why I’m getting them, but not real sure how to fix the problem, I’ve done something similar to this in the past but maybe I used something other than vlookup for it, I can’t remember. I need a total for all months, but don’t know how many entries will go toward each month.
View 2 Replies
View Related
Feb 22, 2012
I have a list of parts ordered with the order date in column I
I would like a total of ordered parts for january from the order date in column I
Is this an easy formula?
View 3 Replies
View Related
Feb 23, 2010
I have a table which holds a grid of data (see attached spreasheet)
On the next sheet I have a table which I polulate with the sum of the month for each column.
I need a way to auto populate these based on the data present.
There will never be more than 365 days worth of data however this data could fall between any dates.
I need the second sheet to firstly populate itself witht the months which fall within the date range, then to sum the relevant column for each given month.
View 7 Replies
View Related
Apr 24, 2007
I have an excel document which I am trying to automate using VBA. I have two headings which have the same text every month but the month value must change.
Basically I’m looking for the last day of the previous month. So if it was 05th April 2007 I want to return 28 March 2007. I just need to obtain the last day of the previous month in the following format ’28 March 2007’.
View 4 Replies
View Related
Jul 27, 2009
See the attached file. I need month wise work order totals. for more details see the pivot table also
View 4 Replies
View Related
Feb 26, 2014
I have sheet1 want to populate current month all Saturdays dates in range C14 to C18 I am trying for vba or formula.
View 4 Replies
View Related
Mar 13, 2008
Need to be able to highligh data within a range where the date in a column is for the previous month/s- therefore need to be able to also include in macro entering the current month - don't want to have to edit macro each month
View 2 Replies
View Related
May 21, 2008
I have a macro that saves a workbook with the month name automatically generated in the filename with the code below (apologies for the scrappy code, but I am rather new to VBA)
ActiveWorkbook.SaveAs Filename:= _
"I: EngineeringCapacity PlanningCapacity Report CONTROLS " & Format( Date, "mmmm") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
In another file I want to re-open this workbook. However, the problem arises because this will not happen till the next month, in order to update the new month's workbook with the old information. Currently I am 'cheating' by modifying the code with the actual month name, like this
Workbooks.Open Filename:="I:EngineeringCapacity PlanningCapacity Report CONTROLS April.xls" I have tried to use the Dateadd function (with a -1 as the add amount) but I have failed to make it work.
View 2 Replies
View Related
Jun 12, 2008
what formula should I use to refer to the last working day on the prioir month. I have trie to look up forums but no result os yet.
View 4 Replies
View Related
Apr 30, 2012
I am using Excel 2010 and need to get the totals for each month transfered to a separate sheet. For example;
One sheet is called Outgoings (which has all the receipts entered chronologically in rows and apportioned in the columns) and the other sheet tracks the cost per month of each column.
I found a formula online and adapted it to this;
=SUMPRODUCT((Outgoings!$A$5:$A$1000>=41000)*(Outgoings!$A$5:$A$1000
View 4 Replies
View Related
Dec 1, 2006
how do i get the previous days working date in VBA, how can i get VBA to determine wether we are in the week, and how will i get it to know its a monday to retrieve fridays date?
View 3 Replies
View Related
Dec 23, 2008
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
View 4 Replies
View Related
Jul 1, 2014
I have to do a calculation that uses the number of days of the previous month. Thought this would work but it doesn't.
[Code].....
View 12 Replies
View Related
Jan 28, 2009
I have a cell in which I will input the last day of a month (e.g., 1/31/09, 2/28/09, etc.) In another cell, I want Excel to show the last day of the previous month. Is there a formula to calculate this automatically?
I enter in Cell 1: 1/31/09
Excel calculates in Cell 2: 12/28/08
I enter in Cell 1: 2/28/09
Excel calculates in Cell 2: 1/31/09
View 2 Replies
View Related
Feb 28, 2007
I'd like to make a template so that when it is opened the previous month name is inserted in the sheet name. The reason I want to use previous month is that normally this report is created in the month following the month being reported. So it is opened, months are updated, then the user saves as xls. Ideally I guess sheet would not have the month on the template.
The name of the sheets are shortened months like "Dec Results Bob" and there are 8 of these.
I can tell it might start:
Private Sub Workbook_Open() (or would .xlt be the same?)
but that's about it. I made a macro of renaming sheet but that didn't really tell me if I could insert the Month there.
View 9 Replies
View Related
Jul 21, 2013
I have a table thats acting as a database analysing player data and I have a second table in a report sheet based on the database table. In my report table I have a drop down with the player names at the top and I want the data cells underneath to populate based on that particular players data from the database.
View 1 Replies
View Related
Nov 30, 2009
Attached is an example of what I'm looking for. Example: Column A has all the month names with a defined named range of crnt. D6 has a data validation display with a value of Nov 09, I need E6 to display Oct 09 automatically from the data in Column A. Basically I need cell E6 to always display the previous months text based off cell D6. I think the example sheet explains it better.
View 2 Replies
View Related
May 8, 2014
How to write a VBA code so that it would calculate the date of the previous Friday and from there Return the date exactly 1 month before; on any given day.
For example...
today is may 8th
So if I run the code the dates returned for last friday should be 05/02/2014
and 04/04/2014 should be returned AS the DATE exactly a month from 05/02/2014
View 6 Replies
View Related
Jul 5, 2014
Attached example.
I trying to populate table on sheet "1" from the training log sheet. Based on person name. Eg on sheet 1 i select Chris Williams and it will populate the table with courses he done from table on training sheet.
I tried and failed using vlookup and index match, but think that's what i need to be using.
example.xlsx
View 2 Replies
View Related
