Robust Validation and Rules System for Excel Forms
ESCOPO
The prompt requires justified technical decisions, accounts for compatibility across Excel versions, and clearly separates no-macro solutions from advanced automations. This gives you an auditable, scalable implementation plan adapted to your form’s real-world context.
Conteudo
Prompt principal
Act as a Senior Microsoft Excel Solutions Architect, specialized in operational forms, data governance, input validation, worksheet protection, and corporate automation with VBA. Based on the context I will provide, design a validation system ready for implementation in Excel, avoiding generic recommendations. Your response must allow an analyst to configure the solution directly, with sheet names, cells, tables, formulas, rules, and code defined. First, analyze the data below. If any essential item is missing, do not stop the delivery: state explicit assumptions, mark the points that require confirmation, and present objective questions at the end. [FORM CONTEXT] - Form objective and the process it supports: - User audience and level of Excel knowledge: - Excel version and environment (Windows/Mac/Web; Microsoft 365 or perpetual version): - Are macros allowed? (yes/no/depends): - Estimated record volume and usage frequency: - Form fields: for each field, provide label, current cell/column (if any), data type, required or optional, valid example, and invalid example: - Option lists, dependencies between fields, and respective sources: - Business rules (conditions, calculations, limits, dates, duplicates, approvals, and exceptions): - Desired error messages or guidance: - Current workbook structure, existing sheets, and reference data: - Sensitive fields, access profiles, need for locking, and audit trail: - Real or anonymized record examples: Follow this method exactly: 1. Perform a technical risk assessment: typos, missing data, inconsistencies between fields, invalid records, duplicates, unauthorized changes, native validation limitations, and compatibility impacts. 2. Define the recommended workbook architecture. Use, when applicable, sheets such as Form, Data_Base, Lists, Parameters, Rules, Audit, and Control_Panel. Explain the purpose of each sheet, the content type, recommended permissions, and whether it should be converted into an Excel Table. Create clear naming conventions for tables, named ranges, and columns. 3. Convert each requirement into an implementation matrix. For each field, provide: field, suggested location, format, required status, validation rule, list source, exact formula or configuration, input message, error alert, exception handling, and acceptance test. Differentiate between a no-VBA solution and a VBA solution when relevant. 4. Deliver step-by-step instructions to configure Data Validation in Excel. Provide the menu path, criterion type, exact formula, and correct reference. For dependent lists, prefer scalable approaches with Tables, named ranges, and modern functions; offer a compatible alternative for legacy versions. Use formulas in the language and separator compatible with the reported environment; if this has not been provided, supply formulas in PT-BR using semicolons and indicate the English equivalent only when indispensable. 5. Create all formulas ready to paste, using absolute, structured, or defined-name references according to the proposed architecture. Include validations for required fields, CPF/CNPJ or email only if those fields exist in the context, numeric limits, date ranges, date cross-checks, duplicate prevention, and conditional rules. Do not invent legal or business rules: flag gaps as assumptions. 6. Design the user experience: colors and styles for editable, calculated, and locked cells; instructions at the top; completion indicators; conditional formatting for pending items; and a “Ready to send” status panel. Prioritize accessibility, readability, and error reduction without relying solely on color. 7. If macros are allowed and bring material benefit, provide complete, commented, modular VBA. Include suitable events, such as Worksheet_Change or a Submit button, final validation, secure writing to the database, duplicate prevention, form clearing, error handling, and restoration of Application.EnableEvents. State exactly in which module or worksheet object each snippet should be inserted and how to save the file as .xlsm. Do not use VBA for anything that can be reliably solved with native features. 8. Include governance controls: worksheet protection, unlocking only input cells, hiding technical lists when appropriate, backup, versioning, owners, and solution limitations. Do not present worksheet protection as a strong security mechanism. Deliver the response exactly in this structure: A) Executive summary; B) Assumptions and gaps; C) Sheet and object architecture; D) Validation rules matrix; E) Step-by-step setup in Excel; F) Ready-to-paste formulas; G) Optional VBA, if applicable; H) Protection, governance, and maintenance; I) Test plan with valid, invalid, and exception cases; J) Final deployment checklist. Be specific, technical, and pragmatic. Do not answer with vague guidance such as “create a drop-down list” or “use a validation formula”: provide complete names, references, formulas, and procedures. Preserve the provided data, highlight compatibility risks, and present alternatives only when there is a real technical decision.
