Automatic Calculations

%3CLINGO-SUB%20id%3D%22lingo-sub-1267846%22%20slang%3D%22en-US%22%3EAutomatic%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1267846%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20keeping%20track%20of%20Machine%20Performance%20daily%20for%20each%20week.%20I%20have%20Weekly%20tabs%20(1%20for%201st%20shift%20and%201%20for%202nd%20shift)%20that%20are%20pulling%20from%20Month%20tabs.%20Can%20I%20get%20my%20Weekly%20tabs%20to%20automatically%20move%20to%20the%20next%20Month's%20tab%20when%20a%20new%20month%20starts%20and%20continue%20using%20that%20month%20until%20the%20next%20Month%20starts%20again%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1267846%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1268264%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1268264%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F602625%22%20target%3D%22_blank%22%3E%40KRHC2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20for%20you%20to%20upload%20either%20the%20full%20workbook%20or%20a%20sample%20of%20it%20(perhaps%20two%20months%20worth%2C%20with%20associated%20weekly%20tabs).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20tell%20you%20just%20upon%20reading%20your%20question%2C%20my%20first%20response%20is%20to%20wonder%20whether%20it's%20necessary%20to%20be%20separating%20the%20data%20into%20these%20separate%20tabs%20in%20the%20first%20place.%20I%20can%20understand%20the%20desire%20to%20monitor%20and%20report%20on%20machine%20performance%20on%20a%20week%20to%20week%20basis%2C%20but%20that%20doesn't%20necessitate%20putting%20all%20data%20into%20separate%20tabs.%20Excel%20is%20wonderfully%20equipped%20to%20take%20a%20single%20database%2C%20one%20containing%20the%20history%20of%20machine%20performance%20over%20a%20protracted%20period%2C%20and%20producing%20reports%20that%20highlight%20monthly%20or%20weekly%20performance%2C%20variances%2C%20etc.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20various%20on-line%20resources%20that%20could%20help%20produce%20such%20a%20%22dashboard%22%20report%2C%20with%20both%20data%20tables%20and%20graphs.%20So%20the%20main%20question%20back%20to%20you%20is%20%22Are%20you%20in%20a%20position%20to%20reassess%20how%20you%20go%20about%20producing%20this%20weekly%20analysis%3F%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1268928%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1268928%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BI%20don't%20see%20why%20not.%26nbsp%3B%20Can%20you%20lead%20me%20in%20the%20right%20direction%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1269118%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1269118%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F602625%22%20target%3D%22_blank%22%3E%40KRHC2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20point%20you%20in%20the%20right%20direction%2C%20let%20me%20first%20suggest%20that%20you%20check%20out%20some%20of%20the%20on-line%20resources%20that%20Microsoft%20makes%20available%20on%20this%20page.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fresources-and-community%2Fmodern-excel-webinars%2Fm-p%2F849491%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fresources-and-community%2Fmodern-excel-webinars%2Fm-p%2F849491%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20don't%20know%20how%20sophisticated%20you%20are%20already%20with%20Excel%2C%20so%20some%20of%20these%20might%20be%20too%20basic%2C%20others%20might%20be%20too%20advanced.%20Given%20what%20you've%20described%20so%20far%20I'd%20recommend%20starting%20with%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3EWorking%20with%20tables%20in%20Excel%2C%20%3C%2FSTRONG%3Eby%20Jan%20Karel%20Pieterse%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3Eand%20ultimately%20getting%20to%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3ECreating%20an%20effective%20business%20dashboard%20in%20modern%20Excel%2C%20%3C%2FSTRONG%3Eby%20Chandoo%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20may%20be%20others%20that%20would%20be%20relevant%20too.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20more%20suggestions%20(other%20than%20the%20suggestion%20that%20you%20acquire%2C%20if%20you%20don't%20already%20have%2C%20one%20of%20the%20many%20excellent%20reference%20books%20on%20Excel)%2C%20I'd%20ask%20again%20that%20you%20post%20a%20sample%20of%20your%20workbook%20so%20that%20some%20of%20us%20here%20could%20take%20a%20look%20at%20it%20and%20make%20specific%20recommendations.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1269169%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1269169%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F602625%22%20target%3D%22_blank%22%3E%40KRHC2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomething%20to%20consider%20before%20creating%20new%20tabs%20for%20the%20data.%26nbsp%3B%20This%20is%20an%20extension%20of%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%20's%20comment.%3C%2FP%3E%3CP%3EIf%20the%20data%20is%20the%20same%20with%20dates%20and%20shift%20types%20changing%2C%20for%20example%2C%20it%20would%20be%20best%20to%20keep%20it%20in%201%20sheet.%26nbsp%3B%20If%20the%20data%20is%20moved%20to%20multiple%20tabs%20the%20concern%20is%20analyzing%20data%20across%20multiple%20sheets%20potentially.%26nbsp%3B%20It's%20do-able%20but%20much%20easier%20when%20the%20data%20is%20in%201%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1269207%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1269207%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F602625%22%20target%3D%22_blank%22%3E%40KRHC2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20take%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E's%20point%20(with%20which%20I%20agree%20completely)%20one%20step%20further%2C%20in%20the%20converting%20the%20data%20on%20all%20of%20your%20tabs%20into%20a%20single%20table%2C%20a%20single%20database%2C%20you%20will%20want%20to%20make%20sure%20you%20have%20columns%20that%20clearly%20identify%20the%20date%20pertinent%20to%20each%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20single%2C%20complete%20date%20might%20suffice.%20There%20are%20times%20when%20it's%20actually%20helpful%20to%20have%20a%20column%20for%20week%20and%20another%20for%20month%2C%20as%20well%20as%20one%20for%20year%20(if%20you%20cover%20multiple%20years)....all%20of%20those%20can%20be%20derived%20via%20formula%20from%20the%20date%2C%20so%20they%20needn't%20be%20entered%20individually.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(And%20I%20say%20that%20now%2C%20before%20you%20do%20it%2C%20just%20so%20you%20don't%20combine%20data%20and%20then%20realize%20after%20you've%20done%20it%20that%20you%20can't%20tell%20which%20month%20or%20week%20a%20given%20row%20belongs%20to.)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1274872%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20Calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1274872%22%20slang%3D%22en-US%22%3EThank%20you!%20I%20will%20get%20to%20work%20on%20this.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am keeping track of Machine Performance daily for each week. I have Weekly tabs (1 for 1st shift and 1 for 2nd shift) that are pulling from Month tabs. Can I get my Weekly tabs to automatically move to the next Month's tab when a new month starts and continue using that month until the next Month starts again?

6 Replies
Highlighted

@KRHC2020 

 

Is it possible for you to upload either the full workbook or a sample of it (perhaps two months worth, with associated weekly tabs).

 

I will tell you just upon reading your question, my first response is to wonder whether it's necessary to be separating the data into these separate tabs in the first place. I can understand the desire to monitor and report on machine performance on a week to week basis, but that doesn't necessitate putting all data into separate tabs. Excel is wonderfully equipped to take a single database, one containing the history of machine performance over a protracted period, and producing reports that highlight monthly or weekly performance, variances, etc.  

 

There are various on-line resources that could help produce such a "dashboard" report, with both data tables and graphs. So the main question back to you is "Are you in a position to reassess how you go about producing this weekly analysis?"

@mathetes I don't see why not.  Can you lead me in the right direction?

Highlighted

@KRHC2020 

 

To point you in the right direction, let me first suggest that you check out some of the on-line resources that Microsoft makes available on this page.

https://techcommunity.microsoft.com/t5/resources-and-community/modern-excel-webinars/m-p/849491

I don't know how sophisticated you are already with Excel, so some of these might be too basic, others might be too advanced. Given what you've described so far I'd recommend starting with

Working with tables in Excel, by Jan Karel Pieterse

and ultimately getting to

Creating an effective business dashboard in modern Excel, by Chandoo

 

There may be others that would be relevant too.

 

If you want more suggestions (other than the suggestion that you acquire, if you don't already have, one of the many excellent reference books on Excel), I'd ask again that you post a sample of your workbook so that some of us here could take a look at it and make specific recommendations.

Highlighted

@KRHC2020 

Something to consider before creating new tabs for the data.  This is an extension of @mathetes 's comment.

If the data is the same with dates and shift types changing, for example, it would be best to keep it in 1 sheet.  If the data is moved to multiple tabs the concern is analyzing data across multiple sheets potentially.  It's do-able but much easier when the data is in 1 sheet.

Highlighted

@KRHC2020 

 

To take @Patrick2788's point (with which I agree completely) one step further, in the converting the data on all of your tabs into a single table, a single database, you will want to make sure you have columns that clearly identify the date pertinent to each row.

 

A single, complete date might suffice. There are times when it's actually helpful to have a column for week and another for month, as well as one for year (if you cover multiple years)....all of those can be derived via formula from the date, so they needn't be entered individually.

 

(And I say that now, before you do it, just so you don't combine data and then realize after you've done it that you can't tell which month or week a given row belongs to.)

Highlighted
Thank you! I will get to work on this.