Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

List help

Copper Contributor

I'm not sure exactly what to call it...
just for some background information i work for a construction/Metal Fabrication company i have spent the last 20 years in the shop on the fabrication side of things just last year i moved in to the office in a purchasing position part of my responsibilities is making the material cut lists for all the handrail jobs
so with minimal experience with excel i created my cut list with a calculator and a part list sheet the cut list automatically pulls information from the part list
my limited knowledge of excel and lots of time spent searching the internet has got me this far ... so
what i want to do is add another sheet
and in that sheet i want it to auto populate a "list" of parts and quantities and the length and cope information
Example:
Part # Qty Size Cope
11       16   16"    No
12        1    14"    C1E

important to note that
Hand Rail 1 has matching parts on Hand Rail 2 and Hand Rail 3 ...so i would like it to add the quantities together so i get a total number

I attached Images of all three sheets i wish i could just attach the actual file but oh well
just as a side note i have really enjoyed learning about excel so if you have a solution it would be appreciated if you could explain it so that i might learn something

Thank You

8 Replies
The images somehow aren't there.

@jmaxwell85 

i wish i could just attach the actual file but oh well

 

Well, it is possible to post an actual file: just put that actual file on OneDrive or GoogleDrive and paste a link here that grants edit access to the file.

@mathetes 

for some reason i could not upload images 

but here is the link from my google drive

https://docs.google.com/spreadsheets/d/1vLrmOmAI0iI4w1DImnoG3XFL7SLi54iu/edit?usp=sharing&ouid=11146...

 

Thank You 

for some reason i could not upload images
here is a link to the file in my google drive
https://docs.google.com/spreadsheets/d/1vLrmOmAI0iI4w1DImnoG3XFL7SLi54iu/edit?usp=sharing&ouid=11146...
thank you
i don't know what happened but when i click that link all the formulas say ERROR formula parse error but the original on my hard drive is just fine
here is a One drive link should work better
https://1drv.ms/x/s!AnhidNVvIZFffDlIOYYOsj5NGfo?e=Cqi90N
Have you had a chance to look over the file?
I still have not found a solution
I sent you a link to the file on onedrive
thank you

@jmaxwell85 

 

I just took a look at it. I'm afraid I'm not able to figure out what to make of the various tabs within that workbook, nor how to connect what I see there with what you describe in your initial post. 

 

It's a serious mistake to put so much energy into making a spreadsheet look great before you've gotten it to function as you want it to. As you've done it here, for example, you have set up sections that refer to variations in hard elbow parts (or some such), each much the same thing, but with some differences that lead you to create separate sections.. In general, you'd be better served with a single database of comparable parts, with column entries that take care of differentiating the specific variations that you have handled visually by separating them. Excel is wonderful with functions like XLOOKUP, INDEX and MATCH or FILTER (etc) in extracting data from a single consolidated database. Our human eye thinks it's clearer to separate the subsets, but doing so actually interferes with Excel's abilities.

mathetes_1-1706740986085.png

 

Anyway, I'd be happy to try to help--you clearly have done some good learning, by the way--but you're going to have to do some more work to orient us to the way your workbook is organized, and specifically where the help is needed.

 

@mathetes 

Thank You it has been a lot of research in how to use functions in excel.
so first off the first sheet labeled Cut List Template that was the original sheet that i created and i did all the math by hand and it was very time consuming this sheet is just to list the the handrail and all the parts that need to be cut as you see Handrail 20871H1 has parts 11, 12, 270. Because this is part of a larger job with more handrails every week i created a the part list tab so i when handrails have the same parts i don't have multiple part number for the same part. The Calculator Sheet is just so i can do the math faster it is essentially self contained as in nothing is pulling data off that sheet that is just where i can input the dimensions from the blueprint and it will give me the correct length that the material has to be cut for example the hard elbow measurement is 1-7/8" that is the amount of material that needs to be cut off from the dimensions on the blueprints this sheet is complete that is why it looks like i spent so much time on it because i wanted this sheet to be able to be used by its self and some of the shop guys use this as well nothing on this sheet needs to be changed i hope that helped to clarify the document some
Now what i am trying to do is on the QTY Totals List sheet
i would like this sheet to auto populate if possible from the Cut List Template Sheet
and all i need is a list with quantity length and cope
VLOOKUP is what i used to get the Cut List Template to Auto Populate with most of the information from the Part List Sheet (I know this sounds confusing)
But i would like the Qty Totals List Not to have Duplicates EX. Hand Rail 1 and Hand Rail 2 Both Have 3 - part 11 so on the Qty Totals List it should have Part 11 - 6pcs as one entry
I think this could be done with VLOOKUP and perhaps a filter or another function

Well i hope that clarified the sheets and what i need help with i am happy to answer any questions you might have to further help
and thank you for taking the time to help me