wowprompt

Shared Google Sheets tracker design with dropdowns and protected ranges

Designs a shared tracker sheet that several people can fill in without breaking it: column list, data types, dropdown values, validation rules, protected ranges, formulas and a summary tab. Use it before rolling out a team tracker for leads, tasks, orders, stock or applications.

A column specification table with types, dropdown values and validation rules, a list of protected ranges, the summary formulas, and setup steps
The output this prompt produced, unedited.

The prompt

What to give

  • What the tracker is for: [e.g. tracking sales leads across four reps]
  • Who fills it in and who only reads it: [e.g. reps add rows, the manager reviews, the owner only views]
  • Fields you need: [list in your own words, e.g. name, city, source, status, next follow-up date, value]
  • Status steps, if any: [e.g. New, Contacted, Quoted, Won, Lost]
  • Reports you want from it: [e.g. count by status, value by rep, overdue follow-ups]

Design checklist

  1. One row per record and one fact per column; no merged cells inside the data.
  2. A unique ID column filled by formula so rows can be referenced safely.
  3. Every column given a type: text, number, date, dropdown, checkbox or formula.
  4. Dropdowns for every repeated choice, with the full value list stored on a separate Lists tab.
  5. Validation that rejects bad input: dates as dates, numbers above zero, emails with an @.
  6. Formula columns and the header row protected so only the owner can edit them.
  7. Conditional formatting for overdue dates and stalled statuses, using at most three colours.
  8. A Summary tab built from formulas such as COUNTIFS, SUMIFS and QUERY, never typed numbers.
  9. A frozen header row and a filter view per person instead of shared filters.

Output format

  1. | Column | Type | Allowed values or rule | Filled by | Example |
  2. Lists tab contents.
  3. Protected ranges and who can edit each.
  4. Summary tab formulas, each written out in full with the cell it goes in.
  5. Setup steps in order, as short numbered instructions a non-expert can follow.

Rules

  • Keep it to the fields asked for; suggest at most three extra columns and say why.
  • Write formulas that work in Google Sheets as they are.
  • Never store passwords, ID numbers or bank details in the tracker.
google sheets trackerdata validationdropdown listprotected rangesshared spreadsheetteam tracker template

How to run this

Copy the prompt and paste it into Gemini, then set the aspect ratio to 3:4. No reference photo is needed for this one.

The prompt is written in Markdown with each part under its own heading, which is what the copy action hands over. Change one section at a time, keeping the rest intact, so you can tell which change did the work.

Related prompts

All productivity