I am trying to put together an interactive excel spreadsheet for my coworkers to help them create, what we call, a Construction Bid Manual to distribute to Subcontractors for bidding purposes in less time.
In a nutshell, we break up this Bid Manual into individual scope sections (i.e. individual scope section 2A-Structural Demolition, 9B-Tile Work, etc.). Within the individual scope section we reference scope specific specifications from the architect (i.e. the architect could have in his spec book Section 054000-Cold-Formed Metal Framing which we would assign to the 9A-Gypsum Board Assemblies scope). Also within each individual scope section, we have specific instructions to subcontractors on what to bid on. So under whatever individual scope title we use there are specific specifications from the architect and instructions assigned to that work scope.
I have an excel workbook with multiple worksheets where I need to extract information relating only to that specific individual workscope and organize that information on one row to prepare for export related to the mailmerge feature in Microsoft Word.
I will now explain how I am creating my interactive excel spreadsheet and asking for help on some problems I have encountered.
Within my workbook, on worksheet “Workscope Description”, I am having my co-workers write in what individual workscopes they will have on their specific project. So in column A something like this could appear:
2A - Structural Demolition
3A - Concrete Reinforcement
5A - Structural Steel Framing
5B - Steel Decking
6A - Interior Architectural Woodwork
7A - Bituminous Dampproofing
7B - Building Insulation & Firestopping
7C - Polymer-Modified Exterior Insulation and Finish System
7D - Ethylene-Propylene-Dienemonomer (EPDM) Roofing
7E - Applied Fireproofing
7F - Joint Sealants
7G - Expansion Control
8A - Doors, Frames and Hardware
8B - Glazing
9A - Gypsum Board Assemblies
9B - Tiling
9C - Acoustical Panel Ceilings
9D - Painting
10A - Visual Display Surfaces
10B - Wall and Door Protection
10C - Toilet, Bath, and Laundry Accessories
10D - Fire Extinguishers and Cabinets
10E - Metal Lockers
I have created another worksheet named “Specification List”. This is where my co-workers write in the specification sections found in the specifications book published by the architect. A small sample is found below:
Section 092216 - Non-Structural Metal Framing
Section 092900 - Gypsum Board
Section 093000 - Tiling
Section 095113 - Acoustical Panel Ceilings
Section 096513 - Resilient Base and Accessories
Section 096516 - Resilient Sheet Flooring
Section 096816 - Sheet Carpeting
Section 099113 - Exterior Painting
Section 099123 - Interior Painting
The column directly right of the specification sections will have a drop down list of the individual workscopes created from worksheet “Workscope Description”. [Data-Validation-Settings Tab-Validation criteria-Allow List-Source Scope_Description].
My co-workers will go through this specification list and assign an individual workscope to each listed item.
I need help at this point. Once you have assigned the items, how do you extract the information (gathering all sections assigned to 2A’s, 3B’s together) in a list using a macro? I just want my co-workers to push a button to have this done for them.
Bid Library
The other portion to my Bid Manual is that I will have a library of specific instructions to subcontractors. The rest of the worksheets will be labeled as one of the Construction Specifications Institute (CSI)’s Master Format Divisions where the specific instructions will be found.
Division 01 — General Requirements
Division 02 — Existing Conditions
Division 03 — Concrete
Division 04 — Masonry
Division 05 — Metals
Division 06 — Wood and Plastics
Division 07 — Thermal and Moisture Protection
Division 08 — Doors and Windows
Division 09 — Finishes
Division 10 — Specialties
Division 11 — Equipment
Division 12 — Furnishings
Division 13 — Special Construction
Division 14 — Conveying Systems
Division 15 — Mechanical
Division 16 — Electrical
There will be many headings within the worksheet (for example Division 9-Finishes=headings such as tile work, carpet, acoustical panel ceilings). For certain headings I want to assign an individual workscope and be able to click on the certain instructions, only those that apply, below that heading which will then be assigned to that workscope. How do you do that? This needs to be incorporated into mail merge feature.
I was wondering if there is a way to make a table of contents at the top of the page and when you click on (for example) tile it takes me directly to that section?
Thanks,
AJFul
In a nutshell, we break up this Bid Manual into individual scope sections (i.e. individual scope section 2A-Structural Demolition, 9B-Tile Work, etc.). Within the individual scope section we reference scope specific specifications from the architect (i.e. the architect could have in his spec book Section 054000-Cold-Formed Metal Framing which we would assign to the 9A-Gypsum Board Assemblies scope). Also within each individual scope section, we have specific instructions to subcontractors on what to bid on. So under whatever individual scope title we use there are specific specifications from the architect and instructions assigned to that work scope.
I have an excel workbook with multiple worksheets where I need to extract information relating only to that specific individual workscope and organize that information on one row to prepare for export related to the mailmerge feature in Microsoft Word.
I will now explain how I am creating my interactive excel spreadsheet and asking for help on some problems I have encountered.
Within my workbook, on worksheet “Workscope Description”, I am having my co-workers write in what individual workscopes they will have on their specific project. So in column A something like this could appear:
2A - Structural Demolition
3A - Concrete Reinforcement
5A - Structural Steel Framing
5B - Steel Decking
6A - Interior Architectural Woodwork
7A - Bituminous Dampproofing
7B - Building Insulation & Firestopping
7C - Polymer-Modified Exterior Insulation and Finish System
7D - Ethylene-Propylene-Dienemonomer (EPDM) Roofing
7E - Applied Fireproofing
7F - Joint Sealants
7G - Expansion Control
8A - Doors, Frames and Hardware
8B - Glazing
9A - Gypsum Board Assemblies
9B - Tiling
9C - Acoustical Panel Ceilings
9D - Painting
10A - Visual Display Surfaces
10B - Wall and Door Protection
10C - Toilet, Bath, and Laundry Accessories
10D - Fire Extinguishers and Cabinets
10E - Metal Lockers
I have created another worksheet named “Specification List”. This is where my co-workers write in the specification sections found in the specifications book published by the architect. A small sample is found below:
Section 092216 - Non-Structural Metal Framing
Section 092900 - Gypsum Board
Section 093000 - Tiling
Section 095113 - Acoustical Panel Ceilings
Section 096513 - Resilient Base and Accessories
Section 096516 - Resilient Sheet Flooring
Section 096816 - Sheet Carpeting
Section 099113 - Exterior Painting
Section 099123 - Interior Painting
The column directly right of the specification sections will have a drop down list of the individual workscopes created from worksheet “Workscope Description”. [Data-Validation-Settings Tab-Validation criteria-Allow List-Source Scope_Description].
My co-workers will go through this specification list and assign an individual workscope to each listed item.
I need help at this point. Once you have assigned the items, how do you extract the information (gathering all sections assigned to 2A’s, 3B’s together) in a list using a macro? I just want my co-workers to push a button to have this done for them.
Bid Library
The other portion to my Bid Manual is that I will have a library of specific instructions to subcontractors. The rest of the worksheets will be labeled as one of the Construction Specifications Institute (CSI)’s Master Format Divisions where the specific instructions will be found.
Division 01 — General Requirements
Division 02 — Existing Conditions
Division 03 — Concrete
Division 04 — Masonry
Division 05 — Metals
Division 06 — Wood and Plastics
Division 07 — Thermal and Moisture Protection
Division 08 — Doors and Windows
Division 09 — Finishes
Division 10 — Specialties
Division 11 — Equipment
Division 12 — Furnishings
Division 13 — Special Construction
Division 14 — Conveying Systems
Division 15 — Mechanical
Division 16 — Electrical
There will be many headings within the worksheet (for example Division 9-Finishes=headings such as tile work, carpet, acoustical panel ceilings). For certain headings I want to assign an individual workscope and be able to click on the certain instructions, only those that apply, below that heading which will then be assigned to that workscope. How do you do that? This needs to be incorporated into mail merge feature.
I was wondering if there is a way to make a table of contents at the top of the page and when you click on (for example) tile it takes me directly to that section?
Thanks,
AJFul
