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