[Doug McLean]

David, you might try this.

I have a few custom tool chests that are a little generic. Each only holds a few tools. What I have is a custom column called Product that holds the different types of what is represented in the tool chest. For example, in my Casework – Wall tool chest there are only about 6 tools, but once I place that, I have about 20 specific Products associated with that markup.
In my Custom Column data, I have the various products set to specific subjects, so only those products associated with a specific subject appear in the dropdown list.

Would something like that help you out at all?
I know its not exactly what you’re looking for, but it does make finding stuff easier.

[Doug McLean]

I’ve used this in the past as well. Its a great trick… one that can confuse the average user

[Doug McLean]

Here are two files that show how it all done. If you want to run the test, they’ll need to be placed in the same folder (as that’s how the PQ is written). This is a pretty small data set, but the speed for a larger one isn’t much different.
If you want to see the steps in the Query, click on the Data tab and then hit Queries and Connections. This will open a list of the left.
The sheet also has two macros that will break the Space column down into Tabs, and another one which saves the tabs a individual csv files so that we can upload those to our Estimating Software.

Any questions, please don’t hesitate to ask.

Bluebeam-to-Takeoff.xlsm 60 KB Excel Spreadsheet (Macro Enabled) - Click to view

Options

• Copy Download Link

Markup-Summary.csv 4 KB CSV - Click to view

Options

• Copy Download Link

Space,Work Category,Subject,Label,Quantity,Product,Length,Width,Height,Depth,Construction,Finish,Hardware,Options,Comments,Floor Multiplier,Front Type,Pull Option,Adjustable Shelf System,Quantity Adjustable Shelves,EB Tape Options,Quantity Door Locks,Quantity Drawer Locks,Recycle Bins,Quantity Finished Ends,Diewall Top Cap,Diewall Removable Panel,Quantity Diewall Removable Panels,French Cleats,Zed Clips,Page Label Level 1 Area 1,Base Bid,Casework - Base,PL-1 Casework - Full Height,1,BFH-2,821.33,205.33,205.33,600,MEL-PB-WHT,PL1-MDF,HRDWR1,,PL-1 Casework - Full Height,1,Front_type_choice,Pull_drilled,Adj_shelf_sys_choice,Qty_shelves_base,,0,,,,,,,,,1 Level 1 Area 1,Base Bid,Casework - Base,PL-1 Casework - Drawerline,1,BD-1,1162.77,224.75,356.63,600,MEL-PB-WHT,PL1-MDF,HRDWR1,,PL-1 Casework - Drawerline,1,Front_type_choice,Pull_drilled,Adj_shelf_sys_choice,Qty_shelves_base,,0,0,,,,,,,,1 Level 1 Area 1,Base Bid,Casework - Base Drawers,PL-1 Casework,1,DB3,1263.55,275.15,356.63,600,MEL-PB-WHT,PL1-MDF,HRDWR1,,PL-1 Casework,1,Front_type_choice,Pull_drilled,,,,,3,,,,,,,,1 Level 1 Area 1,Base Bid,Casework - Wall,PL-1 Casework,1,W-1,1504.32,235.44,516.72,305,MEL-PB-WHT,PL1-MDF,HRDWR1,,PL-1 Casework,1,Front_type_choice,Pull_drilled,Adj_shelf_sys_choice,Qty_shelves_wall,,0,,,0,,,,,,1 Level 1 Area 1,Base Bid,Casework - Base,PL-1 Casework - Full Height,1,BFH-1,965.43,224.75,257.96,600,MEL-PB-WHT,PL1-MDF,HRDWR1,,PL-1 Casework - Full Height,1,Front_type_choice,Pull_drilled,Adj_shelf_sys_choice,Qty_shelves_base,,0,,,,,,,,,1 Level 1 Area 1,Base Bid,Casework - Base,PL-1 Casework - Full Height,1,BFH-1,1460.26,409.84,320.29,600,MEL-PB-WHT,PL1-MDF,HRDWR1,,PL-1 Casework - Full Height,1,Front_type_choice,Pull_drilled,Adj_shelf_sys_choice,0,,0,,,,,,,,,1 Level 1 Area 2,Base Bid,Casework - Base,PL-1 Casework - Full Height,1,BFH-2,821.33,205.33,205.33,600,MEL-PB-WHT,PL1-MDF,HRDWR1,,PL-1 Casework - Full Height,1,Front_type_choice,Pull_drilled,Adj_shelf_sys_choice,Qty_shelves_base,,0,,,,,,,,,1 Level 1 Area 2,Base Bid,Casework - Base,PL-1 Casework - Sink,1,BFH-2_Sink,886.17,237.75,205.33,600,MEL-PB-WHT,PL1-MDF,HRDWR1,,PL-1 Casework - Sink,1,Front_type_choice,Pull_drilled,Adj_sh_sys_not_incl,0,,0,,,,,,,,,1 Level 1 Area 2,Base Bid,Casework - Base,PL-1 Casework - Drawerline,1,BD-1,1162.77,224.75,356.63,600,MEL-PB-WHT,PL1-MDF,HRDWR1,,PL-1 Casework - Drawerline,1,Front_type_choice,Pull_drilled,Adj_shelf_sys_choice,Qty_shelves_base,,0,0,,,,,,,,1 Level 1 Area 2,Base Bid,Casework - Base Drawers,PL-1 Casework,1,DB3,1263.55,275.15,356.63,600,MEL-PB-WHT,PL1-MDF,HRDWR1,,PL-1 Casework,1,Front_type_choice,Pull_drilled,,,,,0,,,,,,,,1 Level 1 Area 2,Base Bid,Casework - Tall,PL-1 Casework - Tall,1,TFH-1,1703.95,273.77,578.21,600,MEL-PB-WHT,PL1-MDF,HRDWR1,,PL-1 Casework - Tall,1,Front_type_choice,Pull_drilled,Adj_shelf_sys_choice,Qty_shelves_tall,,0,,,0,,,,,,1 Level 1 Area 2,Base Bid,Casework - Base,PL-1 Casework - Full Height,1,BFH-1,965.43,224.75,257.96,600,MEL-PB-WHT,PL1-MDF,HRDWR1,,PL-1 Casework - Full Height,1,Front_type_choice,Pull_drilled,Adj_shelf_sys_choice,Qty_shelves_base,,0,,,,,,,,,1 Level 1 Area 3,Base Bid,Casework - Base,PL-1 Casework - Sink,1,BFH-2_Sink,886.17,237.75,205.33,600,MEL-PB-WHT,PL2-MDF,HRDWR1,,PL-1 Casework - Sink,1,Front_type_choice,Pull_drilled,Adj_sh_sys_not_incl,0,,0,,,,,,,,,1 Level 1 Area 3,Base Bid,Casework - Base,PL-1 Casework - Drawerline,1,BD-1,1162.77,224.75,356.63,600,MEL-PB-WHT,PL2-MDF,HRDWR1,,PL-1 Casework - Drawerline,1,Front_type_choice,Pull_drilled,Adj_shelf_sys_choice,Qty_shelves_base,,0,0,,,,,,,,1 Level 1 Area 3,Base Bid,Casework - Base Drawers,PL-1 Casework,1,DB3,1263.55,275.15,356.63,600,MEL-PB-WHT,PL2-MDF,HRDWR1,,PL-1 Casework,1,Front_type_choice,Pull_drilled,,,,,1,,,,,,,,1 Level 1 Area 3,Base Bid,Casework - Base,PL-1 Casework - Full Height,1,BFH-1,965.43,224.75,257.96,600,MEL-PB-WHT,PL2-MDF,HRDWR1,,PL-1 Casework - Full Height,1,Front_type_choice,Pull_drilled,Adj_shelf_sys_choice,Qty_shelves_base,,0,,,,,,,,,1 Level 2 Area 4,Base Bid,Wall Paneling,WD-1 Wall Panels,1,Wall Panelling MDF,1814.44,337.86,569.36,19,MDF,WD1-MDF,HRDWR1,Chamfer_panel_edges=False;PreDrill_Panel_Holes=False,2.07 sf,1,,,,,E4s,,,,,,,,FALSE,TRUE,1 Level 2 Area 4,Base Bid,Wall Paneling,WD-1 Wall Panels,1,Wall Panelling MDF,1401.5,300.32,400.43,19,MDF,WD1-MDF,HRDWR1,Chamfer_panel_edges=False;PreDrill_Panel_Holes=False,1.29 sf,1,,,,,E4s,,,,,,,,FALSE,FALSE,1

Expand

[Doug McLean]

I can send anyone links to places on YouTube to learn PQ if they want.

[Doug McLean]

Thanks to a tip from Vince, I just updated this entire workflow.
I took a bunch of the common Options and broke them down into individual Custom Columns. So now I essentially have a dropdown list for all of my common options.

Of course I have to completely re-write the Power Query to stich everything back together, but that’s the fun part.

Along the way I’ve learned how to do a couple of really cool things with PQ, like Find/ Replace from an existing table (which is pretty cool btw).

The whole process took about 3 days, and I’m now testing it out in the world.
I’ll keep you posted

[Doug McLean]

Vince gave me the idea to put our options into their own Custom Columns, which seems to be working. (My team loves the initial changes because it easier to set up rather than editing one small multiline text box.)
We have options like a few options that are common across multiple tools, and for those I’m just leaving the subject blank.
It would be nice to not be able to choose the option for adding a Door Lock to a Diewall though. Our Estimating software will reject it regardless, because a lock isn’t an option for that product, but I’d rather not give the option to even select it.

[Doug McLean]

I’d like a Choice item to be available to multiple subjects, but not necessarily all. (i.e. Casework and Wall Panels). Revu won’t let you have the same Item name even if it has a different subject.
Michael had it correct.

[Doug McLean]

We can do a lot of that within O365, its just a bit trickier. We capture a lot with Dynamics

[Doug McLean]

I figured it out. You double pasted the link.

[Doug McLean]

I got a 404 error on that link

[Doug McLean]

yep

[Doug McLean]

lol, yep.
Happens to me all the time. Especially in Power Automate.

[Doug McLean]

I used an Index column in Power Query instead

[Doug McLean]

it lives with the Markup.
I actually JUST finished up the Power Query to combine them all and its pretty tricky, but it works the way I want it too

I just have to add in a couple of filter tables and do a full test to see if its working.

[Doug McLean]

Options.jpg 42 KB Image File - Click to view

Options

• Copy Download Link