+ Reply to Thread
Results 1 to 28 of 28

Help creating moving averages pulling data from different tabs?

  1. #1
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Question Help creating moving averages pulling data from different tabs?

    Help! I am trying to figure out how (if it's even possible) to create a moving average for a workbook, pulling one number from each of the last X worksheets in the workbook.

    We have the weekly number of hours sold for each employee (technician) listed by week (one spreadsheet per week), and need to see their averages of one of these numbers (the weekly 'sold' total) for the past 4 weeks, 3 months, 6 months, etc. The managers just add a new sheet to their file each week and send it out. I need to add a sheet named AVERAGES that will stay at the end of the workbook and will give the averages for each employee, updated automatically to include the most recent week's numbers.

    I know I would need to do a VLOOKUP to find the sales numbers by technician since employees come and go and the list is not always in the same order, but since the sheets that the formula needs to look at change every week, I don't know how to handle that condition. Is it possible? Thanks in advance!! This would be extremely valuable if it can be done.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    First point: it would be better to have the technician number in a separate column.

    As employees come and go, how do you want to handle any calculation where an employees no longer has data for any of the periods you are calculating.

    e.g. new employees will only have data in week 5 (end of week 4): leaving employee will gradually reduce periods for data.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    Perhaps the easiest way to create a summary sheet with the weeks (tab names) across columns and technicians down the rows. You can VLOOKUP each week's data and use this table to work out your SUMs and AVERAGESs.

  4. #4
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: Help creating moving averages pulling data from different tabs?

    Thank you John!

    Ok, for the first point, that would be easy to do. They have a master tab they use to create a copy for the new week, so I could edit that in addition to adding the AVERAGES sheet, and they would use this file going forward. I would need to figure out a faster way to split them than manually, since there are about 53 weeks in the workbook and I don't want to have to do that manually to be able to get the 1yr, 6 mo, etc. averages from those sheets.

    If the employee is gone, they will be deleted from the AVE sheet. This is to keep a better eye on averages per employee over time and for reference in performance reviews. As new people are added, I was going to try to set up conditional formatting to highlight any averages that are missing data (ie someone who has been there for 4 months would have highlighted cells for 6 mo and 1 yr averages.)

    If I create a summary sheet with the weeks across columns and use VLOOKUP, is there a way to have it automatically add a new column when a new sheet is created? (To get the moving average). I am trying to set this up for someone who reviews it who has minimal Excel skills and would prefer not to have to do anything manually each week. There are actually 6 of these workbooks, one for each department. I'm just using this as the example and then I was going to update the others the same way.

    Thanks again!!

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    See the attached sheet "SUMMARY".

    I was hoping the WEEK names would be consistent so I could devise the tab names: you will see what i mean in the tab,

    Row 1 has dates increment by 7 days as we move across columns

    row 2 has the following

    =TEXT(B1,"MMMd")&"-"&TEXT(B1+5,"mmmd") to create a tab name.

    in B3

    =IFERROR(VLOOKUP($A3,INDIRECT("'" &B$2 &"'!A5:Z30"),23,0),"")

    Copy across and down.

    If you can align your existing tabs to my format then job done!
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    To extract Technician number insert column and use:

    =TRIM(MID(A3,FIND("#",A3)+1,30))

    to extract name

    =TRIM(LEFT(A3,FIND("#",A3)-1)
    Attached Files Attached Files
    Last edited by JohnTopley; 01-11-2017 at 04:57 PM.

  7. #7
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: Help creating moving averages pulling data from different tabs?

    Sounds like you found a solution! I'll take a look at this in the morning. Thank you again!!!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    You're welcome. I'll be signing off now but will check in again tomorrow.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    I took the liberty of changing the tabs to show a "full" solution. You can now add future tabs using the data in row 2 to copy/paste tab names to avoid any mismatches with the VLOOKUP formula.

    Use

    =TEXT(AU1,"mmmdd")&"-"&TEXT(AU1+5,"mmmdd") if you want "JAN07" rather than "JAN7".

    With regard to your calculations, you should be able to use the dates in row 1 to determine the correct periods to SUM/AVERAGE.
    Attached Files Attached Files
    Last edited by JohnTopley; 01-12-2017 at 04:09 AM.

  10. #10
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: Help creating moving averages pulling data from different tabs?

    (ignore this)
    Last edited by AmyV1; 01-12-2017 at 08:35 PM.

  11. #11
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: Help creating moving averages pulling data from different tabs?

    Ok, I want to make sure I'm following correctly. This is all way over my head.

    Looking at the most recent file's SUMMARY tab, it looks like it uses the single column technician name and number format. So I don't need to split them all?

    I can just insert columns after column A for the averages so I don't need to have a separate tab, correct? I'm having some trouble getting the formula to work for several people. I think it's taking into account the blank cells from before they were hired?

    The managers just need to make sure to name the new tabs correctly for the formulas to work. Is there a way to make it fool proof?

    How do I add conditional formatting to highlight the cell or change the font color to indicate that the average or total doesn't reflect the full period? (Ie they were hired 5 months ago so their 6 month numbers don't reflect a full 6 months).
    Attached Files Attached Files
    Last edited by AmyV1; 01-12-2017 at 09:17 PM.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    Calculation of 4 week average:

    =IFERROR(AVERAGE(OFFSET($J3,0,MATCH(LOOKUP(9999,$J3:$ZZ3),$J3:$ZZ3,0)-1,1,-4)),"")

    ... need to find the last value ..LOOKUP(9999,$J3:$ZZ3) ...and then the column number MATCH(LOOKUP(9999,$J3:$ZZ3),$J3:$ZZ3,0

    and go back 4 weeks (-4)

    4 week Total

    =IFERROR(SUM(OFFSET($J3,0,MATCH(LOOKUP(9999,$J3:$ZZ3),$J3:$ZZ3,0)-1,1,-4)),"")

    for 3 months, change -4 to -13 (weeks): for 6 months to -26 (weeks) and -52 to the year

    As we haven't yet 1 year's data, it is 0. You will get "Circular Reference" error until 52 weeks reached.

    Re CF for length of service: you need to add start data column rather than relying on counting the number of weeks with data: the latter will only work if any week's absence e.g. holiday, is recorded as zero rather than blank.So no CF added in the attached file.

    I have added a CF to highlight the next week in row 2 which should be the TAB name.

    =AND(TODAY()>=J$1,TODAY()<=J$1+6)

    Applied to row 2 from J onward.
    Last edited by JohnTopley; 01-13-2017 at 05:06 AM.

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    I have added a "Workbook Open" macro which checks today's date (NOW() function) against the date is row 1 of summary and finds the first date which is less than today's date. It then checks if the corresponding "generated" tab name in row 2 exists: if not, it copies the "Master" tab and re-names it to the appropriate tab names. If the tab exists, it puts out a message (highlighted in red) which can easily be moved if necessary.

    The attached has "JAN9_JAN14" added. If you test on Monday it should add the next .week (JAN16-JAN21)

    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: Help creating moving averages pulling data from different tabs?

    This looks great! Wow!!

    Because it doesn't return a value for partial periods, I don't need to add the CF. That was only to be a visual alert to let the reviewer know a number wasn't based on the full period.

    Is it possible to change the average columns to not factor in weeks with a 0? (ie when the employee was on vacation or out sick all week, it doesn't bring down their average)

    So for the other workbooks I just need to copy and paste in the Workbook Open macro for the other workbooks and copy over the SUMMARY tab with the correct employees' names?

    I just want to say I am DEEPLY grateful for your help. I hope the good karma you're putting out into the world comes back to you tenfold.

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    Used AVERAGIF ...

    =IFERROR(AVERAGEIF(OFFSET($I3,0,MATCH(TODAY(),$J$1:$ZZ$1,1)-1,1,-4),"<>0"),"")

    Highlighted number change to 13, 26 and 52 respecivley for other averages.

    I also use the date field in row 1 to determine the last data entry date based on TODAY's date..

    The code (WORBOOK OPEN) must go in "This Workbook": in VBE click on "This Workbook" to see the code .
    Attached Files Attached Files
    Last edited by JohnTopley; 01-14-2017 at 04:29 AM.

  16. #16
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: Help creating moving averages pulling data from different tabs?

    Is there a way to have it not return a result if there are blank cells, but at the same time not factor in any 0 hour weeks? For example, Yunje doesn't have any hours until the week of Sept. 26th when he was hired but he now has averages for everything including 6 months. That should be blank still until he has hours listed for the last 26 weeks.

    I can delete the MASTER sheet because the workbook will automatically add a new tab each week, correct? I'm trying to make this foolproof for the managers, otherwise they will be messed up in a matter of weeks.

    I see the WORKBOOK OPEN coding I need for the other workbooks. I will copy and paste. Thanks!

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    DON'T delete the MASTER as it used (copied/renamed) to create a new tab

    Re the averages:

    You could use the following ...the example is the 6 month formula

    =IFERROR(IF(COUNT(J3:ZZ3)<26,0,AVERAGEIF(OFFSET($I3,0,MATCH(TODAY(),$I$1:$ZZ$1,1)-1,1,-26),"<>0")),"")

    change the 26 to 4, 12, 52 if required for other averages.

    results is zero if not enough weeks.

  18. #18
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: Help creating moving averages pulling data from different tabs?

    Yikes, I won't touch the master tab. I thought it copied from the previous week's tab.

    One last question: should it be <=26? (4, 12, 52) in case they've been there exactly long enough to calculate the average?

    THANKS AGAIN!!!

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    You don't calculate AVERAGE if it is less than 26: if it is equal to (or greater than) 26 you want to calculate the average. (?)

    I used the MASTER because (a) is blank so there is no data to clear and (b) I "assumed" this tab would be updated if people are added/deleted i.e. join/leave.

  20. #20
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: Help creating moving averages pulling data from different tabs?

    Yes, sorry. That should have said >=26. I was following what you typed in the message. If they've been there exactly 26 weeks (4, 12, 52) we want to return an average, right? I wanted to make sure just adding the = would work.

    I'll leave the master tab. That will be easiest for them to add/remove people during the week without waiting for the next tab to be added. I will email them short instructions letting them know that new tabs will be created automatically each week, and to add/remove employees on the master tab only.

  21. #21
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: Help creating moving averages pulling data from different tabs?

    I'm working my way through the other workbooks. Unfortunately they weren't formatted exactly the same, but I'm figuring it out.

    I added the WORKBOOK OPEN VBA and it's working. I wanted to make a small adjustment though and I'm not sure how. I added a note on line 27 of the MASTER tab, so when it copies that sheet for the new week, it includes the note, which I don't want. How do I change the code to only copy rows 1-26 each week?

    Thanks!!!

  22. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    Would an option be to clear line 27 from the created tab (NOT Master)?

  23. #23
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: Help creating moving averages pulling data from different tabs?

    Sure! That works too.

  24. #24
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    Have you added the deletion of row 27?

  25. #25
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: Help creating moving averages pulling data from different tabs?

    No, I'm not familiar at all with writing VBA code.

  26. #26
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help creating moving averages pulling data from different tabs?

    Updated code: change highlighted

    Please Login or Register  to view this content.

  27. #27
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: Help creating moving averages pulling data from different tabs?

    THANK YOU!!! Have a great weekend.

  28. #28
    Registered User
    Join Date
    01-11-2017
    Location
    Chicago, IL
    MS-Off Ver
    2010
    Posts
    80

    Re: Help creating moving averages pulling data from different tabs?

    I'm having trouble with some of the workbooks. It seems that I somehow ended up with 2 or 3 copies of them in the VBA but it won't let me remove any of the projects. This is causing the workbooks with more than one project to not create a new tab each week (I'm still getting the error that JAN30-FEB04 already exists. How do I clear this up? VBA Projects.jpg
    Thanks!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro help: Separating data into tabs and creating a table of content of those tabs
    By AntiPivotTable in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-15-2015, 11:57 AM
  2. Moving Averages And the First Few Data Points
    By ashleys.nl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-16-2012, 02:00 AM
  3. Moving averages by date, not data points
    By jumbles in forum Excel General
    Replies: 1
    Last Post: 05-14-2011, 01:29 AM
  4. Pulling Data from Multiple Tabs using (2) Criteria
    By kashyshi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-25-2011, 05:59 PM
  5. Pulling data from moving cell
    By jaikin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-11-2009, 10:31 AM
  6. Pulling data from multiple tabs
    By RevJeff in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-22-2008, 11:08 AM
  7. [SOLVED] pulling data into a month of worksheets, two tabs per day
    By lillian in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-25-2005, 08:05 PM

Tags for this Thread

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