Goal Create a single “master” Excel pricing calculator for a Philadelphia-based union hoist company that bids jobs across PA, NJ, DE, MD, and VA. The tool must generate accurate pricing for quotes/bids and populate Proposal Summary and Proposal Detail outputs automatically based on Inputs. This file is equipment + labor + service + logistics driven and must work reliably in Excel desktop. What the calculator currently does (intended functionality) 1) Inputs-driven estimating workflow The workbook is structured so the estimator fills in key fields on the Inputs tab, including: Job info (client, address/location, duration, dates, etc.) Labor selection: Union vs Prevailing vs Non-Union Market/jurisdiction selection (built to support county/market structure) Equipment selection (hoist types, models/capacities, install configuration, quantities) Optional cost drivers: trucking, crane, mobilization, operator, etc. All of the above should flow through to pricing, totals, and proposal outputs without manual editing. 2) Equipment pricing (rate card based) The calculator pulls equipment rates from a Hoist Rate Card tab. Rates are differentiated by: Hoist model/capacity Configuration: Single vs Dual Hoist category/type (Material vs Personnel if applicable) The tool calculates: Monthly rental per hoist line item Monthly service per hoist line item Totals for the full contract duration 3) Multi-hoist capability (required) The tool is intended to support at least two hoists on one job with independent configuration. Example: Hoist #1: 6000 Dual Material, Qty 1 Hoist #2: 7000 Single Personnel, Qty 1 Each hoist must be priced independently and then rolled up into totals. 4) Labor pricing (union and prevailing options) The calculator includes a Labor_Rates structure designed to support: Market (county/metro) Labor type (Union / Prevailing / Non-Union) Role rates: Superintendent Foreman Journeyman Operator Laborer Labor pricing should calculate fully burdened sell rates and blended crew rates using: Base wage Fringe/burden % Markup % Optional crew blend weights 5) Pricing output and proposal generation The Pricing tab aggregates the equipment, labor, and adders into: Monthly totals Contract duration totals Grand total The calculator then populates: Proposal Summary Proposal Detail These proposal tabs must reflect the correct line items, quantities, duration, and totals, and should not require manual edits. Required fixes / remaining work (critical) A) Ensure both hoists display correctly on Pricing + Proposal tabs Issue: The workbook currently calculates combined totals but does not consistently show both hoists as separate line items on: Pricing Proposal Detail Proposal Summary (if it summarizes equipment) Requirement: Pricing tab must show: Hoist 1 Monthly Rental (based on Hoist 1’s Single/Dual + Model + Type + Qty) Hoist 2 Monthly Rental (based on Hoist 2’s Single/Dual + Model + Type + Qty) Same for service Proposal Detail must list each hoist explicitly (not a “split” of combined totals) B) Restore and harden ALL dropdowns / data validation Issue: Dropdowns have been unstable and sometimes stop working (especially if the file touches Google Sheets). Requirement: Inputs should have working dropdowns for: Market/jurisdiction Labor type Hoist model Hoist type (material/personnel) Single/Dual per hoist Other key selections Use named ranges and consistent validation sources Ensure dropdowns persist after save/reopen in Excel desktop C) Add Google Maps Distance Matrix API for travel/trucking distance Requirement: On Inputs, user enters: Office address (origin) OR office ZIP Job site address or ZIP (destination) Tool returns driving distance (miles) automatically using Google Distance Matrix API Distance must populate a single cell that can drive: Trucking cost logic Mobilization adders Any per-mile assumptions Preferred implementation options: Power Query pulling from Google Distance Matrix VBA call to API Either way: must be reliable and not require constant reconfiguration Note: User will provide their own Google API Key (freelancer should not create it). D) Confirm all formulas are error-safe (no #N/A / broken lookups) Issue: Some pricing cells have returned #N/A historically due to mismatch between dropdown values and lookup tables. Requirement: All lookups should be protected with IFERROR() / XLOOKUP default return Blank selections should return 0 (not errors) Rate card ranges should include all dropdown options, or dropdown lists should be constrained to the rate card E) Ensure this can be used as the “master tool” Final requirement: Estimator fills out Inputs → all tabs populate and calculate Pricing tab totals are correct Proposal Summary and Proposal Detail are correct, professional, and print-ready File is stable in Excel desktop and expandable for new counties/states/rate cards later Platform note This should be built and maintained in Excel desktop as the master file. Google Sheets should only be used for view-only copies because it can break Excel data validation and named ranges.