+ Reply to Thread
Results 1 to 2 of 2

pull data based on day of week from another workbook for previous three weeks

  1. #1
    Registered User
    Join Date
    02-04-2013
    Location
    Saint Paul, MN
    MS-Off Ver
    Excel 2007
    Posts
    3

    pull data based on day of week from another workbook for previous three weeks

    I have been looking around and trying to find a solution to this for a couple days and found some things that I am sure will be in the answer but I just am having trouble wrapping my head around how to tie it all together. Sorry if this is a bit long, but I am just trying to get it all across in one fell swoop. Apologies for the formatting from Excel to here.

    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):

    Time Transactions Total Sales Average Sales
    12:00 39 $352.97 $9.05
    13:00 53 $416.98 $7.87
    14:00 44 $339.42 $7.71
    15:00 43 $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).

    Thanks in advance for even reading that.


    **edit**
    I am somewhat fluent in excel and can do a good deal of things, just have gotten myself wanting to do something that is way over my head in this case! Let me know if you would like any files uploaded to tinker with.
    Last edited by DiznyOrdiz; 02-04-2013 at 08:35 AM.

  2. #2
    Registered User
    Join Date
    02-04-2013
    Location
    Saint Paul, MN
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: pull data based on day of week from another workbook for previous three weeks

    okay so after looking around some more forums and thinking about how to state this logically, this is what I came up with:

    day of week = C3
    date = C2

    I want to search an external workbook and look for matching data in C3 on every sheet
    THEN
    if C3="Tuesday" AND [DATE]<=[TODAY]-21
    THEN
    continue the search (it will always find 3) and repeat above steps twice
    take all the data that was found in the three C6:E25 arrays and average it
    RETURN that averaged data to C6:E25 in the originating workbook that the sheet is running the formula/script from


    a bit complicated, but that's why I'm here looking for help. there is also more to it, but if I can get the ball rolling on how to get that working, I can figure out the other things I am looking to take care of.


    and I am gonna upload the two books now. SalesReport2013 is where the data is held, DayByDayCharts is where the data will go to. I have nothing in the second one because I am at a loss for what to try.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1