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

Populating A Cell With A Calendar Month Based On The Previous Date In Another Cell.

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

Formula- Previous Working Day

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

Previous Month Formula

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

Formula To Display The Last Day Of The Previous Month

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

Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month

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

Last Working Day Of Month Formula

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

Cells Not Being Populated With Exact Same Formula As Previous Working Cells

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

Running Totals In The Cell As The Previous Week And So On Until New Data Is Entered And Updates The Total

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

Excel Formula To Automatically Select The Previous Months Data For Summary Table

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

Updating Totals By Month.

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

Vlookup For Month Totals

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

Totals Divided By Month

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

Auto Lookup And Sum Month Totals

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

Last Day Of Previous Month

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

Month Wise Work Order Totals

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

Populating Current Month Saturdays Dates Vba?

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

Highlight Date If Previous Month

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

Open File With Previous Month In Name

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

Last Business Day Of Prior/Previous Month

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

Excel 2010 :: Get Totals For Each Month Transferred To Separate Sheet

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

Macro: Return Previous Working Days Date

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

Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)

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

Calculation That Uses Number Of Days Of Previous Month?

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

Auto Calculate End Date Of Previous Month

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

VBA For Insert Previous Month In Sheet Name On Open

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

Populating Cells On A Table Using Data From Another Table

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

Display Previous Month Text From A Named Range

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

VBA Code To Return Previous Friday And Month Before Date

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

Auto-Populating Table From Another Table

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







Copyrights 2005-15 www.BigResource.com, All rights reserved