Pull Data For A Discrete Month Into A Grid From A Source File Containing A Full Year

Feb 14, 2013

I'm trying to streamline some processes at work and have encountered a bit of an issue.

I have a source file containing a grid of data (9 columns) with a series of dates they relate to down the left side e.g. 13-Feb-2013. This data covers a whole year.

What I want to do is pull through data relating to a discrete month into a grid in a seperate workbook.

I have a feeling that date ranges and possibly some kind of lookup might be useful but am struggling to see how to pull it all together.

View 3 Replies


ADVERTISEMENT

Pull Month & Year From Dates

Oct 1, 2009

I have Month/Year dates in Column A. Eg. A7= Aug-2009.

I need this to be separated as follows:

Cell C7 must only show the month name in full eg. August
Cell E7 must show the year only eg 2009.

View 3 Replies View Related

Pull The Month And Year No Matter What Format The Dates Are In

Nov 16, 2009

I have a data chart that lists dates in various types of formats, for example:

1/1/10
1/1/09
Jan
Jan '10
Jan/Feb '10
Jan/Feb

I'm trying to figure out a formula that will just pull the month and year no matter what format the dates are in. Based on the above I'm looking for the following results:

1/1/10: Jan 10
1/1/09: Jan 09
Jan: Jan 09
Jan '10: Jan 10
Jan/Feb '10: Jan 10
Jan/Feb: Jan 09

This data is for the entire year so the same applies for all months.

View 9 Replies View Related

SUMIF Month & Year: Find Total Cost By Month Only For Year 2009

Dec 17, 2009

In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?

View 2 Replies View Related

Extracting Month And Year From File?

Sep 12, 2013

I have the Month and Year in the name of the workbook for eg Sales July 2013.xlsm

I would like a macro to extraxt the month and year from the file name and put this in shewet2 in cell E1

View 4 Replies View Related

Pull The Data From, Hence The Source Of The Pivot Table

Mar 27, 2009

I have a pivot table created. How do I determine which sheet it is pulling the data from, hence the source of the pivot table? Also, if can you go into source and modify data points, then in pivot table, will the results would be automatically modified with updated data points?

View 2 Replies View Related

Save File In Respective Month Folder As Per Current Year Vba Modification

Apr 3, 2014

In sub which will convert file to PDF and save it to assigned folder and then attach it to email in outlook. All works fine.

However is is possible to modify the code to save fole to respective month folder ( as per current date and year)?

e.g. I have created folder Named 'Trial' in C drive . This has sub folders 2014, 2015, 2016, 2017, 2018 Each year folder will have sub-sub folders month wise like This has sub folders as Jan , Feb , Mar , Apr .... till Dec Now e.g. if date when the pdf was created is 23/4/14 then it is saved in C:Trials2014Apr automatically. Currently every year I keep creating new folders etc... bit primitive though.

View 2 Replies View Related

Use Data Validation That Will Pull Data From A Source List

Jan 12, 2010

Is there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?

View 3 Replies View Related

How To Get Only Month Year If There Is DATE - MONTH - YEAR In A Cell

Aug 20, 2013

How to get only MONTH' YEAR if there is a DATE-MONTH-YEAR in a cell?

A1: 27-July-2012

Answer D1:JULY' 2012

Pl note "' " is suffixed after JULY (the month).

View 4 Replies View Related

Lookup: Pull Any Color Choices Associated With A Year From The Data Table On The First Worksheet

Jun 17, 2009

I have a data table that contains begin dates, end dates, and colors. The data is input starting with the earliest begin date. On a second worksheet, I have a listing of the years starting with the year of the first begin date and ending with the end of this year. I need to be able to pull any color choices associated with a year from the data table on the first worksheet. Some years may not be specifically listed in the data sheet as the person could have had the same color choice for several years. Sometimes a person could have more than one color choice in a year.

I am at a loss for how to get to the data I need. I created an example spreadsheet to demonstrate the data I am working with, and an example of what I need my results to look like in the end.

View 5 Replies View Related

Average If Data Is In Same Month Of Same Year?

Jun 4, 2014

So I have some data that I would like to have average only if that data was entered in the same month and year as specified in another cell. What I have tried so far is:

[Code] .......

-RenewalMonths is a dynamic range where each cell in the range shows the month of the date in that row.
-RenewalYears is the same but for the years.
-RenewalOverallStuff is a dynamic range where I would need to average the data that meets the criteria.

View 2 Replies View Related

Message To Apear It The User Selects A Year AND Month Less Than The Current Year

Sep 25, 2006

I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.

'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen

ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here

View 3 Replies View Related

Picking Up The Month And Year From A Data Field

Jul 29, 2009

i need to populate a field to show the month and year as follows: the data is entered in one field like this: 01/09/2006. i need another field to populate this: Jun 06. i have tried to use a few formulae but to avail. look forward to your response

View 4 Replies View Related

Macro/function To Take Data From Source File Into Base File

Jun 24, 2006

I have a base excel file for summarizing some data, the problem is that the data comes from a different excel spreadsheet. What I want to do is make a function that pulls the data from another spreadsheet into my base file. It would be easy if it were just one excel sheet, but this job would require where the data is pulled from a data file which has many modified versions.

Can anyone tell me how to do this? The files with the data will be structured the exact same but with different data entered in. I just want a button so I can click the file I want the data from and have it show up on my summarizing base file.

View 9 Replies View Related

Change Year But Keep Month The Same Based On Current Year

Mar 8, 2014

I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.

The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.

Attached is an example : Budget Example.xlsx‎

View 4 Replies View Related

Date And Month From A Column And Year Should Take Current Year

May 14, 2009

I have dates in my column A, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)

View 3 Replies View Related

SUM Data In Columns By Month And Year For Multiple Worksheets?

Jun 3, 2014

I have been trying to sum the data in each column on each tab of my workbook by month and year into one pivot table on a new summary worksheet within the workbook. In the summary pivot table, I would like to be able to select the description as labeled on the tab and a year or month and have the respective totals for each tab and set of columns displayed. The column headings may or may not be the same for each tab. I've been able to separate the data for each tab and summarize without dates or sum the data in columns by date via a pivot table, but I have not been able to consolidate the data for both tabs, with the option to select the month and year, in one pivot table.

View 2 Replies View Related

Formula Year Month To Last Day Of Month, Month And Year

Jan 29, 2010

I'm after a formula this time ... i've searched the board and can't find what i need.

a cell shows 2009 December

and i'd like a formula to covert this to 31st December 2009 .... i.e. for any cell i'd like to know last day of month... and month and year ..

View 9 Replies View Related

Average Daily Data Into Month And Year Data

Feb 26, 2014

I want it to average based on month and year. I have daily data and want to average everything for say January 2000 into one cell and February of 2000 and so on. Column A has date (mm/dd/yy) and column B has data.

View 13 Replies View Related

How To Group Data (rows) Using Month And Year In Date Column Using Macro

May 11, 2013

I want to group data (rows) using month and year in date column using macro.

For example.

Date ID Amount
01/01/2013 12345 $100
02/01/2013 13452 $73
03/01/2013 12232 $50
04/01/2013 34232 $125
01/02/2013 12322 $67
02/02/2013 12345 $100
03/02/2013 13452 $73
04/02/2013 12232 $50
05/02/2013 34232 $125
01/03/2013 12345 $100
02/03/2013 13452 $73
03/03/2013 12232 $50
04/03/2013 34232 $125

I want to group rows by mm/yyyy in date column. Also I want sum amount column by month.

View 5 Replies View Related

Copy Data Based Off Of Fill Color (month) And Year From Another Workbook

Aug 3, 2006

I have a detailed note inside biweekly.xls, it explains everything in context.

Two excel sheets, one is updated manually (Vikki's Yearly Averages.xls), while the other is for reporting (biweekly.xls)

The coding will all be in biweekly.xls.

First, it needs to take a date to the right of a name from biweekly.xls and use the month and year from that date for the search.

Then it needs to look in VYK.xls under the name and copy every entry starting in the year specified and month specified and ending at the current date.

View 6 Replies View Related

Create Year To Date Sales Comparing 4 Years Month By Month?

Dec 25, 2013

Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.

View 10 Replies View Related

Insert All Dates For A Month Weekly When User Inputs Month And Year?

Jul 10, 2014

I have created a time sheet in excel (see attached) that will be part of the larger workbook that will be linked with other sheets to auto fill in most fields. I am wondering if there is a way for an user to enter a Month and a Year at the top of the page and that in turn automatically fills in the days of the month by week.

So in attached sheet there are 5 boxes representing 5 weeks in a month. So if we used May 2014 as an example I would like to know if there is a way that once May 2014 is entered in up to top that. Excel fills in the dates in Week #1 with under Thursday showing 1st, under Friday showing 2nd as on for the entire month...

So as the month go by all user has to do is state the month and year and excel fills in the weekly dates for each day in month.

Attached File : Time and Attendance.xlsx‎

View 2 Replies View Related

Formula To Distinguish Month Year From Prior Month Years

Feb 13, 2010

This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.

View 4 Replies View Related

Automatically Get All The Dates Of A Month Entering Specific Year And Month

Dec 1, 2012

In a sheet I enter the following:

... in A1 a year (say 2012)
... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)

How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?

View 3 Replies View Related

Attached Worksheet Automatically Shade Out All The Saturdays & Sundays In Any Given Month Everytime You Change The Month/Year Cell

Jul 19, 2006

Is there a way to make the attached worksheet automatically shade out all the Saturdays & Sundays in any given month everytime you change the Month/Year cell at the top of the worksheet, as example? I've tried using the weekday/Weekend formula, but can't quite get it right.

View 2 Replies View Related

How To Create A Graph Where Date Starts On July 1st And Runs Through A Full Year To June 30

Jan 14, 2014

I am trying to create a graph where the date starts on July 1st and runs through a full year to June 30. No matter how I sort the dates in the cells, the graph still wants to start in January. You will see from the attached picture the very right lines up with the very left, where these should actually be meeting in the middle. as to what I need to do? Please see photo and dataset.

Chart.jpg
DataSet.xlsx

View 3 Replies View Related

Extract Data From A Source File

Mar 7, 2007

I need to extract data from another source file using VBA. I have problems copying the extracted data and format into the required data format. And also, how do i delete the row that is not required in the output file, in the below example: The row, D0, is not needed. An Example Data Format From the SOURCE file:...............

View 2 Replies View Related

Pull Data From Another File

Dec 8, 2008

Basically what I want to do is pull data from another file and have it copy it down about 10 lines onto another file. With a seperate sheet for each vehicle. For example:

File 1: Has 2 columns with data

Vehicle Number 1000 Vehicle Type Ford
1001 Chevy
1002 Dodge

File 2: Has a vehicle log sheet, in which i want it to take the data above and copy it down x amount of lines.

Vehicle sheet | Date issued | Hours used

Vehicle Number| (data from file one here) | Vehicle Type (data from other column in file one here)

And have it copy itself down from like A1 to whatever.

These also have to be on seperate sheets within the workspace, and each "tab" be named after the vehicle number,

View 9 Replies View Related

Forcing Month / Year Entry To Last Day Of Month?

Jun 10, 2013

I use a certain portion of our workbooks to enter the due dates of certain equipment, which are always listed in month/year (e.g. 4/17), and which means that they are good until the last day of said month. That is, a due date of "5/16" is good through May 31, 2016.

I'd like for my users to be able to enter "5/16" and have that cell properly identify as May, 2016 (instead of May 16 of the current year)...more specifically, the last day of May, 2016. All of this so that I can conditionally format any cell where that date has past - that is, if my user enters "5/16" on May 15, 2016, it won't flag.

View 8 Replies View Related







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