automatic fill for subtotal and GST calculations

[jitterbug888]

Hello!

I`m using a table here, and when I add a date, the day, week, month, year automatically populate,

When I add an invoice amount (column F) I would like the GST and subtotal columns to automatically update as well, however if I use the current formula`s in the GST and Subtotal columns, when I delete an invoice amount, the formula`s in GST and subtotal columns do not repopulate down, however I dont want them to show any results until I enter a new invoice amount (in F).

attached workbook.

[#2]

@jitterbug888 

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.

[#3]

@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, 

[#4]

@mathetes 

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.

[#5]

@jitterbug888 

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

[#6]

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, 

[#7 · Accepted solution]

@jitterbug888 

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

[#8]

Great request! For seamless subtotal and GST calculations, you might consider using IF statements in the formula to trigger calculation only when an invoice amount is entered. This way, the columns stay empty until you input new data. Let me know if you need help with the formula.

[#9]

@mathetes  Thankyou !! appreciated all this help, thought I replied at the time. Great video and insights you have provided.