Jehster1
Member
So, because I now have free time and idle hands are the devil's playground 
... I created an Excel workbook based on the 20 company chart Wayne put out in 2016 (found in the IAP Library). I imagine Wayne and others took an extraordinary amount of time to gather the information and make the chart. I just used that info to create a format that worked better for me. I imagine some others may find it helpful as well.
I did this originally to track the different pens I was working on and was tired of having random notebook pages scattered about. I needed a way to stay organized and a quick reference sheet to return to when I invariably got sidetracked. I came up with a form I could quickly print out that had the info I needed on it. That made me wonder if I could add some automation to the process which led me to the IAP chart which led me to over-engineering this whole thing lol.
All of this information (and more) can be found on the IAP App but since I do most of my research/thinking on my desktop, I wanted a way I could print up a working ticket and take it into the shop where I can write notes or make adjustments as necessary. I don't use bushings, everything is turned between centers directly on the live/dead centers, and I micro it when I get close so I just hang the ticket above my lathe for a quick reference sheet on measurements. I also included the pen ticket blanks I use while working on a pen.
Breakdown:
Info Sheet - Just a blow-up of the graphics used on the Master List sheet.
Master List - This is the main info dump. This is a sortable version of the complete chart. To sort, just use the drop-down arrows in the table header cells. For example, you can use it to find compatible bushings. You want to know what other pens you can make with your 0.331" bushing, sort one of the bushing columns numerically and it will sort everything by bushing size. You can also see what bushing might be "close enough". Another Excel trick would be to use the drop-down arrow, uncheck the 'Select All' button, and then only check the size you want. It will then only display results from that selection instead of the whole list. To get all the results back again, just use the same drop-down arrow and check 'Select All'.
Every column can be sorted.
The list begins sorted by Supplier (alphabetical) and Kit Name (alphabetical). To return to this default state, first drop-down Kit Name and Sort A to Z THEN drop-down Supplier and sort A to Z.
Pen Search - You can pull up the info for a single pen or a couple of them instead of looking at the whole list. Click on any cell in the Kit Name column and you will see a drop-down arrow that will open a list of kit names, choose what you want and the rest of the fields will auto-populate.
Bushing Finder - 2 tables that give quick info on bushings. First one by Supplier - Pen Kit - Bushing part number (if available). Second table by Supplier - Bushing Part Number - Which pens they fit.
Pen Ticket Full - A working ticket that you can print up and put wherever it is convenient for reference as you work on a pen. If you use the drop-down box to fill in the Body Kit, it will auto-populate the Drill Size Body, Drill Size Cap, and all the measurements as listed in the chart in the Listed Measurements section. A good habit is to verify those measurements yourself and write the info in the Actual Measurements section.
Pen Ticket 2X - This is originally all this workbook was for. Simple ticket 2 per page, print up an cut in half, fill out as you measure. I did add the ability to use the Body Kit drop-down to fill in the kit name and it will auto-populate the Drill sizes from the chart. I mainly just print up a bunch of these blanks and keep a pile in the shed-shop.
Disclaimers: This info is only current as of 8/2016 when the chart came out. If anyone has a more updated chart, I could add it. If there's interest I may set up a GoogleDoc so people can add the relevant info and I can update and give out that version. I am by NO means an Excel expert but taught myself a few things when I re-wrote the Check-In/Out process and Payroll spreadsheets at work (long story lol). Gotta love Google!
Possible Future Ideas: Updates? Inventory and sales tracking?
Charlie
Files deleted - updated versions coming soon...
... I created an Excel workbook based on the 20 company chart Wayne put out in 2016 (found in the IAP Library). I imagine Wayne and others took an extraordinary amount of time to gather the information and make the chart. I just used that info to create a format that worked better for me. I imagine some others may find it helpful as well.I did this originally to track the different pens I was working on and was tired of having random notebook pages scattered about. I needed a way to stay organized and a quick reference sheet to return to when I invariably got sidetracked. I came up with a form I could quickly print out that had the info I needed on it. That made me wonder if I could add some automation to the process which led me to the IAP chart which led me to over-engineering this whole thing lol.
All of this information (and more) can be found on the IAP App but since I do most of my research/thinking on my desktop, I wanted a way I could print up a working ticket and take it into the shop where I can write notes or make adjustments as necessary. I don't use bushings, everything is turned between centers directly on the live/dead centers, and I micro it when I get close so I just hang the ticket above my lathe for a quick reference sheet on measurements. I also included the pen ticket blanks I use while working on a pen.
Breakdown:
Info Sheet - Just a blow-up of the graphics used on the Master List sheet.
Master List - This is the main info dump. This is a sortable version of the complete chart. To sort, just use the drop-down arrows in the table header cells. For example, you can use it to find compatible bushings. You want to know what other pens you can make with your 0.331" bushing, sort one of the bushing columns numerically and it will sort everything by bushing size. You can also see what bushing might be "close enough". Another Excel trick would be to use the drop-down arrow, uncheck the 'Select All' button, and then only check the size you want. It will then only display results from that selection instead of the whole list. To get all the results back again, just use the same drop-down arrow and check 'Select All'.
Every column can be sorted.
The list begins sorted by Supplier (alphabetical) and Kit Name (alphabetical). To return to this default state, first drop-down Kit Name and Sort A to Z THEN drop-down Supplier and sort A to Z.
Pen Search - You can pull up the info for a single pen or a couple of them instead of looking at the whole list. Click on any cell in the Kit Name column and you will see a drop-down arrow that will open a list of kit names, choose what you want and the rest of the fields will auto-populate.
Bushing Finder - 2 tables that give quick info on bushings. First one by Supplier - Pen Kit - Bushing part number (if available). Second table by Supplier - Bushing Part Number - Which pens they fit.
Pen Ticket Full - A working ticket that you can print up and put wherever it is convenient for reference as you work on a pen. If you use the drop-down box to fill in the Body Kit, it will auto-populate the Drill Size Body, Drill Size Cap, and all the measurements as listed in the chart in the Listed Measurements section. A good habit is to verify those measurements yourself and write the info in the Actual Measurements section.
Pen Ticket 2X - This is originally all this workbook was for. Simple ticket 2 per page, print up an cut in half, fill out as you measure. I did add the ability to use the Body Kit drop-down to fill in the kit name and it will auto-populate the Drill sizes from the chart. I mainly just print up a bunch of these blanks and keep a pile in the shed-shop.
Disclaimers: This info is only current as of 8/2016 when the chart came out. If anyone has a more updated chart, I could add it. If there's interest I may set up a GoogleDoc so people can add the relevant info and I can update and give out that version. I am by NO means an Excel expert but taught myself a few things when I re-wrote the Check-In/Out process and Payroll spreadsheets at work (long story lol). Gotta love Google!
Possible Future Ideas: Updates? Inventory and sales tracking?
Charlie
Files deleted - updated versions coming soon...
Last edited by a moderator:
