[Solved] Automatically Totaling Various Entries

Discuss the spreadsheet application

[Solved] Automatically Totaling Various Entries

Postby freedhardwoods » Sun Dec 15, 2019 7:09 pm

I build kitchen cabinets. I have to cut parts in many different lengths of various widths (green shaded) for each job, and every job will have many new lengths

In the form shown below, the gray shaded cells in column A is the number I assigned to each cabinet.

I entered the various lengths for this job in the blue shaded area in columns B - F.

The blue shaded areas in column H is all the various lengths in order from shortest to longest for this job.

The red shaded area in column I is the total number of parts for each length.

Is there a way set this form up so that columns H and I will automatically fill when I put new entries in columns B - F?

OPEN OFFICE.png
Last edited by Hagar Delest on Mon Dec 16, 2019 1:13 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.0.1 on Windows 7
freedhardwoods
 
Posts: 8
Joined: Sat Feb 20, 2016 12:45 pm

Re: Automatically Totaling Various Entries

Postby RusselB » Sun Dec 15, 2019 8:09 pm

While I haven't used this option, I have seen it recommended for topics that seem similar to yours, namely the usage of a Pivot Table.
Another helper, with more experience in that area can (probably) give better feedback.
That said, if you post a copy of your spreadsheet, rather than a screenshot, it'd be easier to examine the structure and try to determine the best option.
If confidential data is a concern, there are steps in How to attach a document for anonymizing your data.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5854
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Automatically Totaling Various Entries

Postby freedhardwoods » Sun Dec 15, 2019 10:34 pm

Thanks for the reply. I looked up how to use pivot tables. All I can get it to do is give me random numbers from my cabinet parts table. I may not be doing something right.

Here is the actual spreadsheet. I didn't know I could upload that.

FRAME.ods
(13.67 KiB) Downloaded 10 times
OpenOffice 4.0.1 on Windows 7
freedhardwoods
 
Posts: 8
Joined: Sat Feb 20, 2016 12:45 pm

Re: Automatically Totaling Various Entries

Postby freedhardwoods » Mon Dec 16, 2019 12:11 am

Maybe I can explain a little more so everyone can see what I'm doing. I was going to upload my cabinet 1 drawing, but I can't get small enough to work.

Using my drawing, I enter two 30 1/2's, one 29,one 45, two 21's, and three 22's, for a total of 9 pieces in cabinet 1. I do the same for each cabinet in the set.

To get the summary in the spreadsheet I uploaded, I spent several minutes manually going through the chart.

I want the spreadsheet to automatically summarize all the lengths in each width, and how many of each.
OpenOffice 4.0.1 on Windows 7
freedhardwoods
 
Posts: 8
Joined: Sat Feb 20, 2016 12:45 pm

Re: Automatically Totaling Various Entries

Postby Lupp » Mon Dec 16, 2019 1:20 am

You may have a glance into the attached. I did it with LibO V 6.3.3.2, but it worked also in AOO V4.1.5.
Attachments
aoo100403_FRAME_1.ods
(37.58 KiB) Downloaded 11 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2669
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Automatically Totaling Various Entries

Postby freedhardwoods » Mon Dec 16, 2019 1:54 am

Lupp wrote:You may have a glance into the attached. I did it with LibO V 6.3.3.2, but it worked also in AOO V4.1.5.


Thank you very much for taking the time to work on this. Although I have used OO for years, I've never come close to using it to its full potential. I glanced through some of the entries you used. I have never used the "ISNUMBER" or "INDEX" part of this equation you used. =IF(ISNUMBER($Q11);INDEX(I$8:I$207;$Q11);"")

I will use this to help speed up my shop time and study it more to expand my knowledge of OO.

Thanks again. :super:
OpenOffice 4.0.1 on Windows 7
freedhardwoods
 
Posts: 8
Joined: Sat Feb 20, 2016 12:45 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 12 guests