Forum Discussion
automatic fill for subtotal and GST calculations
You asked: Mathetes do you know if I lock specific columns in the database table, (the automated columns such as day, week, month, year) does that inhibit the pivot table? Because when I protect parts of the sheet (some columns) in the database the pivot table seems to not update anymore...Dont know if its just me or is this standard?
I don't know for sure. I can't imagine why protecting the sheet (or parts of it) would inhibit a pivot table. You also are dealing with an incomplete database at this point (I don't see any dates in the database itself, for example), so unless there's something in another area altogether...my recommendation would be to get a more complete database (both more rows and filled in in all the columns)...but I'd also remove your calculated columns for the time being-- let Excel do the "heavy lifting" in summarizing and reporting.
I watched a really interesting video yesterday on how Pivot Table (which was an absolutely wonderful tool when it was introduced in the 70s or 80s; I can still remember the excitement of discovering it, and how it made cross-tabulated reports so easy, and I had to do a lot of those during my career)....but Dynamic Arrays, and the new Array functions (FILTER, UNIQUE, etc) can do much of the same if not all of it, more reliably and easily. It's almost an hour long, but it may change your whole approach.
https://www.youtube.com/watch?v=9I9DtFOVPIg
I'm not sure how you created your formula, but I re-wrote it as follows and it immediately populated to the cells below
=[@[invoice amount]]*SalesTx
In the process also replaced the figure 0.1 with a named range, naming it "SalesTx" and put the value into what could be the start of a "business table" where you should put any other "fixed variables" because "fixed variables" can often become unfixed, and you don't want to have to go searching for all the places where they appear.
Mathetes, if I may also ask,
I wanted to have a totals box for GST and the subtally as it adds up on the right of the table.
(attached update).
however the subtotal box as I created it, seems to add two lots of the final figure in the subtotal column, because my formula is using K:K.. Is there another way to add this up into the sum box without doubling up on the final figure?
And I wanted those two tally boxes to be replicated on the 2nd tab as well (analyze tab), I have made those fields available there.
I frankly think you're trying to do too much summarization and analysis at the level of your basic table. Let your table be a simple database. Excel can produce those summary reports from a basic database containing the information from each invoice (or whatever the basic piece of granular data is). You haven't entered that data (at least not in what I see) but are trying to assemble summary reports from fragments of the whole.
Just conceptually, the subtotal column is already a tally (that's what a sub-total is), so having another called tally is redundant (unless I'm missing something). But that can serve as an example of where you appear to be trying to analyze on a row-by-row basis rather than just letting tools and functions in Excel, built for that very purpose, do the work for you.
Have you done any research (in books or on-line) on such topics as Pivot Table? And then there's Power Query. But it looks to me as if the Pivot Table alone would serve to produce your analysis sheet, with a lot less labor. Here's a place to start. https://exceljet.net/search?query=pivot+table
Thanks mathetes
Appreciate the conceptual workflow suggestions. I guess i`m trying to turn the database into an automated app!
Actually I do have a pivot table and linked pivot chart on tab 2, and it works very well,
Mathetes do you know if I lock specific columns in the database table, (the automated columns such as day, week, month, year) does that inhibit the pivot table? Because when I protect parts of the sheet (some columns) in the database the pivot table seems to not update anymore...Dont know if its just me or is this standard?
I`ll look into power query,
mathetes Thanks Mathetes for the help,
looks good,
I notice that once the column hits the double blue line the calculations stop,and there are drop down menu`s (Filters) below it, I was able to continuously add data in the date and invoice amount columns before, without having to drag down the lower right corner of the table manually, are we able to get it back that way?
Kind regards,
