Writing conditional formatting in Google Sheets

Review and analyze the existing conditional formatting rules on Sheet 1 (Pens), including logic for:
- Full pens
- Partially full pens
- Held pens
- Conflict indicators
Document current rule structure, formulas, and dependencies.
Design a method to dynamically replicate the formatting logic from Sheet 1 (Pens) to corresponding pen locations on Sheet 2 (Map).
Ensure color status on Sheet 2 (Map) updates automatically based on data entered on Sheet 1 (Pens).
Match formatting exactly, including fill colors, conflict alerts, and conditional logic.
Minimize manual formatting and eliminate duplicate rule maintenance.
Deliver a clean, user-friendly solution requiring minimal technical maintenance.
Qualifications:
Advanced proficiency in Google Sheets
Strong experience with conditional formatting rules and custom formulas
Experience linking logic across multiple sheets
Ability to design structured, scalable spreadsheet systems
Strong attention to...