Summary Page

%3CLINGO-SUB%20id%3D%22lingo-sub-2735581%22%20slang%3D%22en-US%22%3ESummary%20Page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2735581%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20attached%20a%20spreadsheet%20I%20created%20a%20while%20ago%20as%20a%20spares%20control%20file.%20It's%20a%20simple%20sheet%20that%20has%20Opening%20and%20closing%20stock%20with%20columns%20for%20stock%20received%20and%20issued.%20I%20found%20a%20formula%20to%20keep%20the%20opening%20and%20closing%20balances%20blank%20until%20stock%20is%20received%20or%20issued.%20I%20now%20would%20like%20to%20add%20a%20summary%20page%20that%20list%20all%20the%20items%20on%20the%20multiple%20stock%20sheets%20that%20will%20show%20me%20the%20balances%20of%20each%20sheet%20on%20the%20summary%20tab.%20I%20have%20included%20an%20example%20on%20the%20attachment.%20The%20problem%20I%20have%20is%20how%20to%20overcome%20the%20ISBLANK%20function%20I%20used.%20The%20formula%20would%20need%20to%20recognize%20the%20new%20balance%20each%20time%20that%20happens.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2735581%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I have attached a spreadsheet I created a while ago as a spares control file. It's a simple sheet that has Opening and closing stock with columns for stock received and issued. I found a formula to keep the opening and closing balances blank until stock is received or issued. I now would like to add a summary page that list all the items on the multiple stock sheets that will show me the balances of each sheet on the summary tab. I have included an example on the attachment. The problem I have is how to overcome the ISBLANK function I used. The formula would need to recognize the new balance each time that happens.

1 Reply

@ianfourie Rather then answering your question, I would suggest that you change the approach to the issue. It's not very effective to use one sheet for every part (you currently have 83 such sheets) and roll these up to a summary report. Begin by creating a parts list that contains the header information that are now spread over 83 sheets. It could look like this:

Screenshot 2021-09-09 at 12.11.24.png 

Then, record all transactions in one table. For instance, like this:

Screenshot 2021-09-09 at 12.18.08.png

..... and use built-in functionality (a pivot table) to summarize the transactions and combine it with parts information.

Screenshot 2021-09-09 at 12.12.55.png

Now this is just a very crude example and will probably have to be re-worked quite a bit to make it work four your particular situation/needs. And if you are not familiar with pivot tables and the Data Model, it will take some learning. But, in the long run, it will be easier to use and maintain than working with one sheet for every single part number.

 

You'll find source for the screenshots in the attached file.