SOLVED

Multi sheet spreadsheet with formulaes

%3CLINGO-SUB%20id%3D%22lingo-sub-1538043%22%20slang%3D%22en-US%22%3EMulti%20sheet%20spreadsheet%20with%20formulaes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538043%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%20-%3C%2FP%3E%3CP%3EI%20have%20NO%20idea%20how%20to%20do%20what%20I%20want%20-%20but%20I%20shall%20try%20and%20explain%20and%20ask%20if%20anyone%20might%20be%20able%20to%20give%20me%20%22an%20idiot%20guide%22%20on%20how%20to%20do%20it%20%3F%3C%2FP%3E%3CP%3EI%20need%20to%20create%20a%20spreadsheet%20that%20allows%20me%20to%20record%20both%20sales%20and%20stock%20levels%20per%20day%20over%20a%2013%20day%20period%3F%20It%20runs%20over%20weekdays%20and%20weekends%20and%20they%20are%20different%20to%20each%20other%20-%3C%2FP%3E%3CP%3EI%20have%20created%20templates%20for%20both%20weekdays%20and%20weekends%20as%20different%20sheets%20of%20the%20spreadsheet%20and%20have%20put%20in%20all%20the%20formulae%20I%20need%20on%20each%20template%20sheet%20-%3C%2FP%3E%3CP%3EI%20need%20each%20sheet%20to%20represent%20a%20day%20in%20order%20-%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20easily%20%22add%20another%20day%20template%20(with%20the%20formulae%20intact%22%22%20to%20my%20starting%20workbook%20%3F%3C%2FP%3E%3CP%3EAnd%20can%20I%20then%20perform%20calculations%20using%20data%20in%20cells%20from%20different%20sheets%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20MOST%20Welcome%20!!%3F%3C%2FP%3E%3CP%3EKind%20Regards%3C%2FP%3E%3CP%3EDerek%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1538043%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540874%22%20slang%3D%22en-US%22%3ERe%3A%20Multi%20sheet%20spreadsheet%20with%20formulaes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540874%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F736165%22%20target%3D%22_blank%22%3E%40Xerces1066%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETwo%20questions%20for%20you%3A%3C%2FP%3E%3COL%3E%3CLI%3EIs%20it%20possible%20for%20you%20to%20post%20a%20copy%20of%20the%20workbook%20you've%20created%20so%20far%3F%20(Without%20it%2C%20you're%20asking%20us%20to%20respond%20to%20an%20abstraction...it's%20a%20lot%20easier%20to%20work%20with%20the%20actual%20thing%20than%20with%20a%20description.%20And%20I%20mean%20the%20actual%20spreadsheet%2C%20not%20an%20image.%20Just%20make%20sure%20there's%20no%20confidential%20or%20private%20information%20on%20it.)%3C%2FLI%3E%3CLI%3EAre%20you%20open%20to%20entirely%20different%20ways%20to%20conceive%20of%20the%20project%3F%20(From%20your%20description%2C%20it%20sounds%20like%20you%20may%20be%20making%20a%20common%20beginner's%20mistake--designing%20the%20spreadsheet%20to%20mimic%20a%20paper%20process%20that%20may%20have%20worked%20in%20the%20past%2C%20where%20you%20copy%20some%20numbers%20from%20last%20week's%20or%20yesterday's%20ledger%20sheet%20to%20this%20week's%20or%20today's%20and%20then%20tally%20things%20through%20the%20week%20or%20day%2C%20and%20repeat...%26nbsp%3B%20Generally%2C%20although%20this%20can%20work%2C%20it's%20an%20error%20prone%20approach.%20Better%20to%20re-think%20the%20process%20as%20a%20data%20table%20that%20tracks%20sales%20or%20inventory%20continuously%20and%20then%20takes%20advantage%20of%20Excel's%20abilities%20to%20extract%20data%20for%20a%20week%20or%20a%20day%20and%20summarize%20it.)%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541051%22%20slang%3D%22en-US%22%3ERe%3A%20Multi%20sheet%20spreadsheet%20with%20formulaes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541051%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%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20for%20the%20response%20!%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3ESure%20-%20I%20can%20send%20it%20to%20you%20-%20and%20very%20happy%20to%20use%20new%20ideas%20and%20better%20ways%20to%20do%20things%20!!%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3CDIV%3EI%20can%20send%20the%20%22start%22%20of%20the%20workbook%2C%20together%20with%20some%20info%20of%20what%20I%20am%20trying%20to%20achieve%2C%20and%20yes%2C%20you%20are%20assuming%20correctly%20already%20!%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3CDIV%3EKindest%20Regards%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3EDerek%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541666%22%20slang%3D%22en-US%22%3ERe%3A%20Multi%20sheet%20spreadsheet%20with%20formulaes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541666%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F736165%22%20target%3D%22_blank%22%3E%40Xerces1066%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20here's%20a%20start%20at%20showing%20how%20Excel%20might%20handle%20that%20with%20a%20single%20table%20to%20handle%20sales%20and%20returns...and%20then%20using%20Pivot%20Table%20to%20report%20on%20the%20volume%20of%20related%20transactions%20by%20hour%20per%20day.%20I've%20not%20tried%20to%20deal%20with%20stock%20or%20inventory%20levels....because%20that%20requires%20more%20definition.%20Even%20this%20is%20a%20situation%20where%20I've%20kind%20of%20%22guessed%22%20at%20what%20might%20be%20relevant.%20It's%20meant%20primarily%20to%20demonstrate%20a%20way%20to%20think%20about%20the%20task.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20there%20are%20three%20new%20spreadsheets%20in%20this%20workbook%3A%3C%2FP%3E%3COL%3E%3CLI%3EA%20%3CSTRONG%3Ebusiness%20table%20sheet%3C%2FSTRONG%3E%20that%20provides%20some%20basic%20information%20used%20for%20data%20validation%20on%20the%20transaction%20sheet.%20Each%20of%20those%20tables%20can%20be%20extended%20to%20add%20other%20things%2C%20or%20other%20pieces%20of%20info%20per%20item%2C%20etc.%3C%2FLI%3E%3CLI%3EA%20%3CSTRONG%3Etransaction%20sheet%3C%2FSTRONG%3E%20to%20track%20sales%2C%20return%20to%20stock%2C%20bringbacks%2C%20by%20hour%2C%20by%20item.%20This%20can%20also%20be%20enlarged%20if%20you%20want%20to%20track%20by%20store%2C%20etc.%3C%2FLI%3E%3CLI%3EA%20%3CSTRONG%3EPivot%20Table%3C%2FSTRONG%3E%20that%20summarizes%20transactions.%20This%20can%20be%20fine-tuned%20and%20extended%20as%20well.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EWhat%20I%20want%20you%20to%20see%20is%20that%20instead%20of%20beginning%20with%20what%20is%20basically%20an%20%22output%22%20sheet%20into%20which%20you%20enter%20hourly%20data%2C%20already%20(in%20effect)%20summarizing%20raw%20data%20but%20doing%20so%20%22manually%2C%22%20Excel%20really%20is%20good%20doing%20that%20kind%20of%20summary%20for%20you.%20Just%20capture%20each%20transaction%20as%20it%20happens.....let%20Excel%20do%20the%20%22heavy%20lifting.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(As%20an%20aside%3A%20It%20probably%20would%20have%20been%20more%20discreet%20to%20leave%20the%20name%20%22Costco%22%20off%20your%20workbook--as%20a%20Costco%20member%2C%20I%20find%20it%20kind%20of%20surprising%20that%20Costco%20is%20asking%20an%20employee%20who%20is%20not%20in%20IT%20to%20create%20a%20spreadsheet%20to%20track%20sales%20and%20inventory.%20Have%20you%20checked%20to%20see%20whether%20something%20doesn't%20already%20exist%20along%20these%20lines%3F%20Hard%20to%20believe%20it%20doesn't%2C%20given%20Costco's%20fame%20at%20tracking%20all%20of%20this%20kind%20of%20stuff.)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541761%22%20slang%3D%22en-US%22%3ERe%3A%20Multi%20sheet%20spreadsheet%20with%20formulaes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541761%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%3B%3C%2FP%3E%3CP%3EHaHa%20-%3C%2FP%3E%3CP%3ENOT%20a%20Costco%20employee%20-%20Costco%20have%20asked%20us%20to%20run%20a%20series%20of%2013%20day%20%22Roadshows%22%20of%20our%20products%20in%20their%20stores%20!%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20the%20info%20so%20far%20-%20I%20will%20digest%20it%20and%20mess%20around%20with%20it%20and%20see%20how%20far%20I%20get%20%3F%3C%2FP%3E%3CP%3EShould%20be%20fun%20trying%20%3F!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDerek%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1542601%22%20slang%3D%22en-US%22%3ERe%3A%20Multi%20sheet%20spreadsheet%20with%20formulaes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1542601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F736165%22%20target%3D%22_blank%22%3E%40Xerces1066%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFeel%20free%20to%20come%20back%20and%20ask%20more%20questions.%20There%20are%20others%20here%20who%20can%20offer%20other%20perspectives.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi All -

I have NO idea how to do what I want - but I shall try and explain and ask if anyone might be able to give me "an idiot guide" on how to do it ?

I need to create a spreadsheet that allows me to record both sales and stock levels per day over a 13 day period? It runs over weekdays and weekends and they are different to each other -

I have created templates for both weekdays and weekends as different sheets of the spreadsheet and have put in all the formulae I need on each template sheet -

I need each sheet to represent a day in order - 

How do I easily "add another day template (with the formulae intact"" to my starting workbook ?

And can I then perform calculations using data in cells from different sheets ?

 

Any help would be MOST Welcome !!?

Kind Regards

Derek

5 Replies

@Xerces1066 

 

Two questions for you:

  1. Is it possible for you to post a copy of the workbook you've created so far? (Without it, you're asking us to respond to an abstraction...it's a lot easier to work with the actual thing than with a description. And I mean the actual spreadsheet, not an image. Just make sure there's no confidential or private information on it.)
  2. Are you open to entirely different ways to conceive of the project? (From your description, it sounds like you may be making a common beginner's mistake--designing the spreadsheet to mimic a paper process that may have worked in the past, where you copy some numbers from last week's or yesterday's ledger sheet to this week's or today's and then tally things through the week or day, and repeat...  Generally, although this can work, it's an error prone approach. Better to re-think the process as a data table that tracks sales or inventory continuously and then takes advantage of Excel's abilities to extract data for a week or a day and summarize it.)

 

@mathetes 

Thanks for the response !

 

 

 
Sure - I can send it to you - and very happy to use new ideas and better ways to do things !!
 

 

I can send the "start" of the workbook, together with some info of what I am trying to achieve, and yes, you are assuming correctly already !
 

 

Kindest Regards 

 

Derek
Best Response confirmed by Xerces1066 (New Contributor)
Solution

@Xerces1066 

 

Well, here's a start at showing how Excel might handle that with a single table to handle sales and returns...and then using Pivot Table to report on the volume of related transactions by hour per day. I've not tried to deal with stock or inventory levels....because that requires more definition. Even this is a situation where I've kind of "guessed" at what might be relevant. It's meant primarily to demonstrate a way to think about the task.

 

So there are three new spreadsheets in this workbook:

  1. A business table sheet that provides some basic information used for data validation on the transaction sheet. Each of those tables can be extended to add other things, or other pieces of info per item, etc.
  2. A transaction sheet to track sales, return to stock, bringbacks, by hour, by item. This can also be enlarged if you want to track by store, etc.
  3. A Pivot Table that summarizes transactions. This can be fine-tuned and extended as well.

What I want you to see is that instead of beginning with what is basically an "output" sheet into which you enter hourly data, already (in effect) summarizing raw data but doing so "manually," Excel really is good doing that kind of summary for you. Just capture each transaction as it happens.....let Excel do the "heavy lifting."

 

(As an aside: It probably would have been more discreet to leave the name "Costco" off your workbook--as a Costco member, I find it kind of surprising that Costco is asking an employee who is not in IT to create a spreadsheet to track sales and inventory. Have you checked to see whether something doesn't already exist along these lines? Hard to believe it doesn't, given Costco's fame at tracking all of this kind of stuff.)

@mathetes 

HaHa -

NOT a Costco employee - Costco have asked us to run a series of 13 day "Roadshows" of our products in their stores !

Thank you so much for the info so far - I will digest it and mess around with it and see how far I get ?

Should be fun trying ?!

 

Derek

@Xerces1066 

 

Feel free to come back and ask more questions. There are others here who can offer other perspectives.