Pull Data Based On Day Of Week From Another Workbook For Previous Three Weeks

Feb 4, 2013

Here is what I have and am trying to do:

I entered the date and day in this format in multiple sheets in workbook 1 (named 0101, 0102, 0103) from the start of the year to last night (B2:C4):

Date:January 01, 2013
Day:Tuesday

I then entered all the data needed below that (B5:E26):

TimeTransactionsTotal SalesAverage Sales
12:0039 $352.97 $9.05
13:0053 $416.98 $7.87
14:0044 $339.42 $7.71
15:0043 $304.96 $7.09
etc.....

In workbook 2, I have sheets named for each day of the week. I would like to take the information from the previous three weeks for each corresponding day from workbook 1 and put the averaged data into another table with the data all formatted the same in workbook 2. This way, each week when I go into the aggregate file (workbook 2), I can look at the chart I'll create and see the average of the past three weeks transactions, total sales, and average sales for each hour of the business day and be able to staff accordingly (assuming the trend continues).

View 1 Replies


ADVERTISEMENT

Create Graph Showing Previous 8 Weeks Of Data?

Sep 29, 2011

I have used sumif statement to set up a value by week and then used the drop down list cell reference to display what i want ie week 25 data

What I would like is to display the 8 previous weeks and then use this in a graph, so every time i change the drop down week I see all the data showing the current week and previous weeks.

Now the problem is which formula do i use to to identify previous weeks

ie

WK No 21 22 23 24 25
Visits 100 200 250 300 400

View 1 Replies View Related

Pull Data From Closed Workbook Based On Cell Values

Apr 28, 2008

I want to pull data from closed workbook based on cell values of open workbook of column B and the source file name is on cell J1. Actually I save monthly files and opening balnce of current month should take vakues from previous month file.

Suppose current month is May 2008. Then Column Column D for May month shold take value from column G of April 2008. For simplicity the previous monthís name and thus source file name will be placed on cell J1.

The code should loop from column B of source file and current May 2008 file and should pull values for only those items which are in the current file in the Column B. Thus those products which are deleted or newly added item in the current item should not copied. Though for new item no name will be thre in the source file but for deleted items the item might be there in the source file but the code should ignore those value.

View 4 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

Perform Statistical Analysis On Day Of The Week And For The Last X Weeks?

Jun 26, 2014

I was working with formula array

I have named ranges

Graphdaterange has all of my dates
GS1 has the value I want to analyze

Ultimately I want to look at the last say 10 weeks of Mondays, Normalize the data removing high and low values, finally perform Sum, Min, Max, STDEV average total. formula array would work for me but when I try to use it the "And" does not appear to work correctly

I can settle for just statistical analysis of Mondays for the last 10 weeks so If normalizing makes this impossible we can eliminate the large portion of the formula.

Named ranges
GS1_1 - D14-D(X) as defined by a formula is my data
GraphDateRange - C14-C(X) as defined by a formula is the column containing my dates.[code]...

I am not using a macro due to the nature of the tool. I can not get iterative processes to run near as quickly a formula array.

View 3 Replies View Related

How To Summarize 52 Weeks Worth Of Data Into One Workbook

May 15, 2013

I am using Excel 2010 on windows 7 PC. I work for a railroad and one of my coworkers gets data once a week about how many carloads we ship to a certain company. They save each week in its own worksheet so at the end of the year they will have 52 worksheets worth of carload data. What they would like to do is take these 52 worksheets and then combine this data onto one worksheet so they can see all of it going from week 1 to week 52.

Here is what 1 week's worth of data looks like...

Here is what the 52 week summary page looks like...

And here is what the summary page looks like with all of the data on it....

Although we have compiled all of this data, it was done by copying and pasting data from 52 different worksheets (took some time as you could imagine). So, my coworker asked me if I knew of a faster way to do this. After spending multiple hours on the net researching this and trying a bunch of different options (Data Consolidation, Summary/Array Functions, and varying Macros) I have not been able to produce a worksheet that takes all of the data and displays it like the last picture above. Because I only know the very basics of excel I'm sure I'm probably doing something wrong. Trying to decipher the code for macros hasn't been easy and although I got data consolidation to work it was adding up all of the numbers instead of taking them from one sheet and placing them on another with all of the worksheets' data side by side.

View 6 Replies View Related

Flowing Month Data Into A Chart Based On Weeks?

Feb 27, 2009

I have a spreadsheet that I am using to try to track Project Man Hours. What happens is:

I enter in the amount of weeks a Project Phase is in and when the Project Starts. I also enter in how many "Men" we are going to need.

i.e. Project Start - January 2009
Phase 1 - 4 weeks with 1.5 Men
Phase 2 - 20 weeks with 2 Men.

What I want to happen is:
Excel then adds that data to a chart showing EACH MONTH and the amount of men needed for each phase.

Project 1 showing a line chart with January-December and the lines correlating with the amount of "Men" needed each week based on the Start Date and Sequential Phase Week Amounts.....

View 2 Replies View Related

Pull Data From One Closed Workbook Into Active Workbook With Command Button

Feb 27, 2014

I've been using the following code to bring in individual cell values from one closed workbook to an active one. I would like to modify this is possible to bring in multiple cells at once and also pull them into a different worksheet in the active workbook. Basically, my command button is on Sheet1 but I'd like the data to pull into a cell on Sheet2.

Private Sub CommandButton1_Click()
With Range("Q9")
.Formula = "='C:Users[Workbook Name.xlsm]Worksheet Name'! N27"
.Value = .Value
End With

View 6 Replies View Related

Looking Up Data From A Previous Workbook?

Apr 23, 2014

I have built a compiled a workbook which figures out the daily business in my club, Staff Rota, business done, wage slips, and everything else I need. Well, everything except the stock element.

I need the current week to look up the previous weeks stock levels on each item, when the previous weeks workbook changes it's name every week. At present I name the sheets by the week ending date, i.e. 12-04-14.

View 2 Replies View Related

Find Date Of Weekday In Previous Week.

Sep 27, 2009

I've been racking my head on this one.

I need a function where for a given date will return the date of a specified weekday in the previous week.

Example: If today is Friday Sept. 25th 2009 and I want to find the date of
the Wednesday in the previous week I would need something
like DATEPREVIOUSWEEK("09/25/2009", vbWednesday)

View 9 Replies View Related

Pull Data Into Workbook

Sep 27, 2007

I have a workbook I created that is full of macros, graphs, and formulas. Five of the tabs in the workbook are where I will be putting my data. This workbook will also be used by others that don't even know what a macro is or does so I need to make this as simple as possible. How I get my data:

I have a website on my intranet where I have to login and then I have five templates that I run. I can have the results given to me either in HTML or in an excel spreadsheet. The excel results are very basic. Heading for each column and the data below. I have the report generated with blank columns since my page the data will go on has formulas. Thus far I have been copying all rows starting with row2 and doing a paste special (values, skip blanks). I had a macro in my personal.xls file to do this for me but most users will not know how to import it so I need a resolution that will be saved in the WB I send out.

View 5 Replies View Related

Pull Or Push Data From One Workbook To Another

Feb 3, 2010

I'm looking for something that will be able to push or pull data from a workbook (named "Personen - test.xls" = persons) to an other workbook (named "Personen per maand.xls" = persons/month). In attached documents i tried to make clear (sorry, it's in dutch) what i would like to do. In English; the marked data in "Personen - test.xls" should automatically be shown in the marked area in "Personen per maand.xls". I think the biggest problem is the fact that "Personen per maand.xls" is a standard-document which should be filled -time after time- with many different data's from several "Personen - test.xls" documents. These documents are all named different, the data is always given in the same way. So in my opinion i should make something where i can tell excell which document to use and something i can tell where to put the data. But i really don't know how to do this...

View 11 Replies View Related

Auto Pull Data From One Workbook To Another?

Sep 3, 2013

I need a formula or a macro here, or something completely different. I have one spreadsheet that tracks all records that I produce. In one of the columns (F), I have indicated whether certain forms were issued with the record. This column can either contain an "x" or nothing at all. On a separate spreadsheet, I track and record the results from all these forms. I need something that will make the second spreadsheet (TrackTrend) automatically search column (F) on spreadsheet one (RecordTracking) and if "x" is found, return the information for columns (A-C) for that given line. If nothing is found, nothing should be returned.

View 3 Replies View Related

Using ISERROR Pull Data From Other Sheets In My Workbook

Oct 30, 2008

I'm having some difficulty getting ISERROR to work in certain cells that pull data from other sheets in my workbook. I have had no trouble getting it to work in cells that do not take data from other sheets.

This formula: =IF(ISERROR(DATEDIF($E$2,D18,"d")),"-",(DATEDIF($E$2,D18,"d"))) is an example of how I successfully got the function to work. All is well with this.

However, on a different sheet using these two formula: ='Mike Anderson'!A68 and ='Mike Anderson'!J536:L536 I could not get the ISERROR formula to work.

Unfortunately, I'm enough of a novice using Excel, that I don't know if I'm doing something wrong with the formula or if it's just not designed to work with values from another sheet. This is how I was trying to make the formula work =IF(ISERROR('Mike Anderson'!J536:L536,"",'Mike Anderson'!J536:L536)). Doing so this way created an error in which the little Excel helper guy that pops up highlighted the quotes inside the formula.

The error message I'm trying to get to not display using the "" is just a #DIV/0 message that fills the cell until some data is entered into the sheet.

View 2 Replies View Related

Pull Found Data From Multiple Workbook

Jul 5, 2007

I am trying to Loop Through a Folder Of Excel Workbooks and get required Info from one of the workbooks, back to my Master File.

I thought of "VLOOKUP" but I am not shore if it will work over Approx. 60 workbooks? if there is an other or better approach, I am open to all Ideas.

So far I have code to Open my Master and declare the criteria for the VLOOKUP with the help from:


Re: Run A Macro On All Files In A Directory
--------------------------------------------------------------------------------
See also this page on how to loop through a folder
Loop Through Folder Of Excel Workbooks
_________________

I am now able to open all Files in the sub directory, now I need to know how to approach my problem of looking for and getting the Info back to my Master File with approx. 5000 entrys to look for?

View 9 Replies View Related

Pull Data From Closed Workbook Through Assigned Path

May 6, 2014

I want vba on pulling data from closedworkbook from certain path with range of end column values

I have two workbook one is Opencall_06-05-2014...

File string contains Half today date...

I want pull data from Opencall_06-05-2014.xlsb ,Sheename is "Opencall" Paste into Master file...with range of (A:BC).

Find the attachment of two workbook...

View 5 Replies View Related

Automatically Generate Template Sheet Based On Previous Data

Apr 17, 2014

I currently have a large spreadsheet that multiple people fill out. Each person fills out all the information in a row. At the end of the row, I would like a button that says "Generate Form" so that when clicked, a new sheet automatically opens with a template form that I created and is already filled out with the information that was just inputted into the spreadsheet. Also, there are four different template forms that could generate. For example, there are forms A, B, C and D. If the user inputs "B" into the first column of the row, then when he goes to click "Generate Form", a new sheet is created with all the information filled out in Form B.

View 3 Replies View Related

Pull Data From Sheet Based On Criteria - Populate UserForm And Ask For Missing Data

Feb 8, 2014

I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...

First I was using a msgbox function to get the info:

HTML Code: 

For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b

But it can be up to 30 different new employees... and that is time consuming.

I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....

HTML Code: 

Private Sub CloseButton_Click()
Unload UserForm1
End Sub

Private Sub ComboBox1_Change()

[Code] ......

View 2 Replies View Related

Using If Function To Pull Data From One Cell Based On Data From Merged Cells

Jul 24, 2014

Looking for a formula to accomplish the following:

I'm trying to populate cell A31 on a worksheet titled "VolumeTotals" with the data in Cell E23 from a worksheet titled "CurrentCustomers" if the merged cells F3-F22 on worksheet "CurrentCustomers" are equal to the word "Contract".

View 6 Replies View Related

Delete Workbook After Two Weeks

May 17, 2009

Is it possible for a workbook to delete itself after two weeks or by date since putting in VBA codes?

Or does it have to be active to be able to delete itself.

I'll be going away for a holiday in two weeks (yepeee) and the person who's taking over my place will only use it on first day of my absent.

I do not want others to use my file (of course I'll back it up before I'll go).

View 7 Replies View Related

Pull Data Based On Two Criteria?

Nov 14, 2012

i have a sheet that i have been creating to pull information based on two things. The criteria is an emplyee # and the second is a date.

The data needs to be organized onto Sheet4. The Employee # will already be on Sheet4, so i have been trying to use that with Vlookup / hlookup with no success as i can't seem to get it to find the correct Date and place the data correctly.

Sheet4 layout:
Completed (with respective data from Sheet2)

A
B
C
D
E

1

[code]....

the data is located in Sheet2 (Emplyoee # is always in Column A, and the Date is always in Column B).

the data needs to be pulled into Sheet4. Essentially making the horizontal data of Sheet2, Vertical on Sheet4 under the respective date column.

Sheet2 Layout:

A
B
C
D
E
F
G
H
I
J

[code].....

View 3 Replies View Related

Macro - Auto Populate The Data, Based On The Previous Cell Values

Aug 3, 2009

the post 5 for the actual issue. This being my first post could not update it correctly. I have put my views int he 5th post which will be more clear.

View 9 Replies View Related

Capture And Calculate Data Based On Work Week In VBA Or Formulas

Jul 21, 2014

I have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas
but i wanted to automate the work week calculation specially if the number of days in a month has been change.

Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.

For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.

here is the distribution of columns per week as reference.

(31 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day24 (8)
wk4 - Day25 to day 31 (7) columns

(30 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day23 (7)
wk4 - Day23 to day 30 (7) columns

View 1 Replies View Related

Formula To Pull Data Based On One Cells Value

Apr 23, 2014

I am trying to have my spreadsheet pull data from one tab and cell if they match another.

Tab "Ag Orders" column H, I enter data in lets say cell H37. Whatever I enter, if it matches tab "reference List" C:C, then "ag orders" column G (specifically G37) should enter the data in "reference List" D:D

So since in H37 I entered 34000 - 314620, G37 should pull in Farm Operations since on the referene list tab that string of numbers (C11 matches up with D11)

a little confusing but I hope it makes sense.

13-14 budget-AG.xlsm

This isn't the exact formula I used but something like it and it wasn't working. It wouldn't match the data up correctly on a few of the department names

=IFERROR(LOOKUP(H35,("11000 - 311010","11000 - 311020","11000 - 311510","11000 - 311610","11000 - 312010","17800 - 311010","17800 - 311510","17800 - 312010","34000 - 314610","34000 - 314620","34000 - 314640","34000 - 314660","34000 - 314680","34000 - 314690","81008 - 840008","81009 - 840009","13863 - 312040","13864 - 312050","71060 - 521620"),

[Code] ..........

View 1 Replies View Related

Pull Data From One Sheet To Another Based On 4 Conditions

Jul 7, 2014

I am moving along in my masters project and I am having a problem organizing some data I got dumped with.

What I have is a 196 x 196 matrix of the worlds countries and the distance between each one. So each Row has a title header of a country and each column has a country and the cells between the two are the distance of said countries.

On another sheet I have about 10 000 different combinations of dates/years/months ect that I need to populate in order for Stata to work properly.

Rather than manually enter thse in one by one or copy paste what I would like to do is write a code that would do something like this.

If on sheet 2 (the combination sheet) A3 = Afghanistain and C3 = China than F3 = The China/Afghanistain intersection (In this case AN3 on the Distance Sheet)

I am having problems because I can't see to figure out a way to have a program lookup the intersection and give me the China/Afghanistan number based on all the conditions. I am assuming I need to use a matrix lookup of some sort. This way when I do some like Canada/Denmark on the Combination sheet, it will automatically look up and populate the distance from the distance sheet (That being AZ33).

View 10 Replies View Related

How To Pull Certain Data Based On Date Range

Apr 22, 2014

I'm compiling data from field reps that comes in a big spread sheet. I want to pull the rows out that fall into a certain date range. For example, if it falls between January and March. How would I do this?

Company 1
3/5/14
$54,000
Q1
Phase I
10%

Company 2
2/1/14
$16,000
Q4
Phase II
80%

Company 3
12/1/13
$18,000
Q2
Phase I
20%

View 4 Replies View Related

Macro / VBA To Pull Data From Worksheets Based On Name

Jul 10, 2014

I have a bunch of different worksheets that I need to pull a single cell from (lets say A1). On my summary sheet (first sheet) I have a list of all the worksheet names. Is there a macro that can pull data by referencing their names in the summary sheet.

Example: Column A has a list of all the worksheet names in my workbook. I want to extract cell A1 from each worksheet based off the name in Column A and be put into Column B. Otherwise, you would have to go through each tab and link it (I am working with 100+ tabs).

Column A Column B
PIN
CID
SIP

View 9 Replies View Related

Pull Data From Worksheet Based On ID Number

Nov 8, 2006

I have 2 worksheets (Sheet1 and Sheet2). Sheet 1 contains around 3000 records with multiple fields (columns). Column A contains the ID number. Sheet 2 contains some records with different fields except for the ID number which is common.

I need a macro to retrieve information from sheet1 and copy it to sheet 2 based on the ID Number; i.e the macro needs to get the ID number from sheet2, locate it in sheet1 and copy the data from column D in sheet1 for that particular record and paste it in sheet2.

View 7 Replies View Related

Create Macro That Filter Date In A Column From One Week Previous Till Current Date

Mar 14, 2014

I have an excel sheet wherin there is a column that has the data where in the dates are displayed and many other columns.

I get this excel every Thursday so i want to filter this date column in such a way that it give me the data related to the date of the previous week only yet there is a catch here. When i say previous week i mean.

Suppose today is 03/14/14 then i want the data from 03/07/14 till today ie Last week friday to this week full( so cant use Current week option) and then paste it in a new sheet.

I tried the Record part but in that it is taking a hard coded value as i am selecting the date myself. I dont want to change the date manually every time.

this was the macro that was created

Code:
ActiveSheet.Range("$A$1:$BX$58").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, "3/10/2014")
Range("A59").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[-4]C:R[-1]C)"
Range("A60").Select

View 8 Replies View Related

Pull Data Out Of A Closed File Based On A Date Range

Jun 15, 2006

I am trying to make Excel pull data out of a closed file based on a date range. The closed file is a log where people input data and enter the day they are doing it. I need to pull this data in my file and make it print into my worksheets so I can pass a report on to others with only the data they request from the log.

The range would be for 7 days and would have to be able to choose a few columns off this report.

View 9 Replies View Related







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