Totaling/summing data from a variable range/dataset

Discuss the spreadsheet application

Totaling/summing data from a variable range/dataset

Postby WMWForest » Wed May 05, 2021 5:28 pm

Beginner here - I have been developing a volume totaling spreadsheet for tax purposes (I work in the forest products industry; timber is taxed by volume/value, depending on the product)

I have the table that aggregates volumes built, and I have a basic layout for entering data from individual "trip tickets" (slips detailing the specific product/volume - one per truckload of timber), so that individual tickets can be associated with time/date/purchaser etc. info....

I'm looking for a way to:

#1 - Duplicate a "trip ticket" entry table infinitely (some jobs have 10 loads of wood on them, some have 150) - perhaps w/a button or keyboard command(?)

#2 - Total the data from individual tickets into the appropriate categories in the aggregate table, even though the data range is variable.

Apologies for my forum illiteracy, this is a side project I've been picking away at, and I've hit a dead end. File is attached for reference - Sheet 1 is the "trip ticket" entry table that I wish to duplicate; Sheet 2 is the aggregate table.
RoC Volumes Template - 2.0.ods
(21.24 KiB) Downloaded 43 times
OpenOffice 4.1.3
Windows 10
Posts: 1
Joined: Thu Apr 22, 2021 2:53 pm

Re: Totaling/summing data from a variable range/dataset

Postby Matareuz » Mon Jun 14, 2021 9:22 pm

Hey WMWForest,

It's been a long time since you asked for help and I know that probably you had find a way to do what are you asking for. :oops:

Anyway, I modified the file to make it do what you were asking to do (If I don't misunderstood).

#1 Ready, there is a form in the Sheet2 who works like a button, but you can change it if you want. Take care about the cells A2 and B2 from the Sheet "Caulculation", the name of the new duplicate come from there, you can modify the name in A2 careless, but if you modify the number in B2 you'll need to find a way to make the formulas work with the new value (I reccomend a completely regular ascending or descending count). Also, all the new duplicates will be inserted before the "sheet2".

#2 I'm not sure if you want to aggregate a row for each ticket in the Sheet2 or you just want the total of all the tickets and also, I don't know if each ticket will have just a row of data or will have more that one row of data, then the file have 2 methods to find the total, in the left side there are the formulas to find the values in each ticket if the ticket have a static range, and in the right side there are the formulas to find the sum of the values in each ticket if each ticket have a variable range. :super:

The formulas in the file can works up to 200 tickets, but if someone really will to use a calc file with 200 sheets, maybe should consider change the application or the method. :knock:

PD: You can change the name of the Sheet2 Careless, but if you change the name of the sheets "Template" and "Calculation" you'll need change its name in the Macro as well.
RoC Volumes Template - 2.0 test.ods
(71.18 KiB) Downloaded 7 times
LibreOffice Windows 7 Ultimate
Posts: 6
Joined: Fri Nov 20, 2020 4:33 pm
Location: Venezuela

Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests