Halloway Plant Hire invoiced £6,933.00 for October's job sheets. The month was worth £10,982.00. Every machine went out, every rate on the card was right, and three of the hires were invoiced at nought a day.
Each of those rows was filled in from a dropdown, and the dropdown approved every one of them. It approved them in September, when the depot cell beside them still said something else.
A validation rule runs once, at the moment a value is typed. Change the cell it depends on afterwards and nothing re-runs. The child keeps a value its parent no longer allows, and it keeps it in a cell that still shows an arrow.
| Measure | Figure |
|---|---|
| Job sheets raised | 12 |
| Invoiced | £6,933.00 |
| Owed | £10,982.00 |
| Hires invoiced at £0.00 a day | 3 |
| Days hired at nothing | 21 |
| Never invoiced | £3,329.00 |
| Under-charged on one machine | £720.00 |
A dependent dropdown is two things: a list that narrows, and a pair that has to stay true. Excel gives you the first. It gives you nothing at all for the second.
What you need.
INDIRECT,SUBSTITUTE,COUNTIF,COUNTIFS,SUMIFS,COUNTA,TRIMandLENwork in every version of Excel this century, as do named ranges, Data Validation and Circle Invalid Data.
UNIQUE,FILTER,SORT,LET,XLOOKUPand spill references such as=$K$2#need Microsoft 365 or Excel 2021 and later. Section 7 says what to do when an older build refuses them.Plant hire is only the example. A supplier and its part numbers, a client and its matters, a site and its cost codes, a country and its tax rates: same two cells, same silence.
01Twelve Job Sheets, Three Hired Out at Nought
Twelve Job Sheets, Three Hired Out at £0.00 a Day
Halloway Plant Hire's October job sheets, in A2:I13. Column B is the depot and column C the machine, both chosen from dropdowns where the second list is narrowed by the first. Column D is the day rate the sheet worked out with `=SUMIFS(Rates!D:D,Rates!B:B,$B2,Rates!C:C,$C2)`, E is the days hired, F is what went on the invoice and G and H are the rate and the money owed. JS-4019, JS-4026 and JS-4031 hold a machine their depot does not hire, so SUMIFS found no row to add and returned 0.00: 21 days of plant hire invoiced at nothing, £3,329.00 in all. JS-4022 is the quiet one, because its pair exists: a 13-tonne excavator went out, the Midlands list stopped short of it, the desk chose the 8T at £175.00 and the row is £720.00 light. SUM(F2:F13) invoiced £6,933.00, SUM(H2:H13) is the £10,982.00 owed, and the £4,049.00 between them is three hires at nought and one machine billed as a smaller one.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Column B is the depot and column C the machine. The machine list is narrowed by the depot, because a Telehandler in North East is £148.00 a day and the same machine in South West is £142.00.
Column D is the rate the sheet worked out, E is the days, F is what went on the invoice. Eight rows are right. Four are not, and they are wrong in two different ways.
| Job | Depot | Machine | Invoiced | Owed |
|---|---|---|---|---|
| JS-4019 | Midlands | Telehandler | £0.00 | £1,278.00 |
| JS-4022 | Midlands | 8T Excavator | £1,400.00 | £2,120.00 |
| JS-4026 | North East | 8T Excavator | £0.00 | £1,176.00 |
| JS-4031 | Midlands | 8T Excavator | £0.00 | £875.00 |
Three of them invoiced nothing, because the depot and the machine name a pair the rate card does not have. The fourth invoiced an 8-tonne excavator at £175.00 for a 13-tonne machine at £265.00.
Scenario: Open your own sheet and put =COUNTIFS(Rates!$B:$B,$B2,Rates!$C:$C,$C2) beside the first row of it, then fill it down the column. Every row should return 1. Count the noughts before you read any further.
02A Rule Runs Once, When the Value Is Typed
Data Validation is an entry check. Excel evaluates the rule when somebody types or picks a value in that cell, and it stores the rule, not the verdict. Nothing re-runs it afterwards, and nothing watches the cells the rule depends on.
So a dependent dropdown is only true at the instant it is used. JS-4019 was raised against South West and a Telehandler, which was a legal pair that day. The job moved to Midlands in September, the depot cell was changed, and the machine cell sat untouched.
Excel has no event that clears a child when its parent changes, and a cell holding a typed value has no formula to recalculate. The row still shows a dropdown arrow, the arrow now offers Midlands' four machines, and the cell still reads Telehandler.
| What happens | Does the rule re-run? |
|---|---|
| You type a value in the cell | Yes |
| You pick one from the dropdown | Yes |
| You change the parent cell | No |
| You paste into the cell | No, the rule is replaced |
| The source list changes | No |
| The workbook recalculates | No |
Scenario: Pick any dependent dropdown in your own workbook, change its parent cell to another value, and look at the child. It will still be sitting there. Now click the child's dropdown arrow and watch the list offer something completely different.
Try it in the grid03The List That Narrows: INDIRECT and Names
The classic build is two rules and one trick. The parent cell gets a List rule over the depots. The child cell gets a List rule whose Source is =INDIRECT($B2), and INDIRECT turns the text in B2 into the named range of the same name.
That means a named range per depot. Lay the rate card out with the depot names as headings, select the block, and use Formulas → Create from Selection → Top row: Excel writes one name per column in a single step.
Rates sheet Names created
South_West Midlands North_East South_West = $A$2:$A$5
3T Excavator Dumper 6T 3T Excavator Midlands = $B$2:$B$5
8T Excavator 8T Excavator Telehandler North_East = $C$2:$C$5
Telehandler 13T Excavator Tandem Roller
The Source box is not the place to test the idea. Type =INDIRECT($B2) into an empty cell first: if it returns #REF!, the name does not exist, and the dropdown will be empty for exactly the same reason.
Scenario: Open Formulas → Name Manager and read the Refers to column for each of your lists. Then put =COUNTA(INDIRECT($B2)) beside a job row: it is the number of machines that dropdown will offer, and a nought is a name that is not there.
04$C$2 Gives Every Row the First Row's List
Validation rules are written for the active cell and copied to the rest of the range the way a formula is, so the dollars mean what they always mean. $B2 locks the column and lets the row travel. $B$2 locks both.
Write =INDIRECT($B$2) and every row in the column asks row 2 what its list should be. Row 2 said South West, so row 27 offers South West's machines no matter what row 27's own depot cell says.
That is JS-4026. The depot cell reads North East, the list offered 8T Excavator because the top of the column was a South West job, and North East does not hire an 8T.
| Source box | What row 9 offers |
|---|---|
=INDIRECT($B$2) | Row 2's depot, always |
=INDIRECT(B2) | Whatever is one column left and seven rows up |
=INDIRECT($B9) | Row 9's depot, if written while row 9 is active |
=INDIRECT($B2) written at row 2 | Row 9's depot, correctly |
Select the whole column before you open the dialog, and write the rule for its top cell. Excel then offsets it down the selection, exactly as it would a formula.
Scenario: Click any child cell well down your sheet, open Data → Data Validation, and read the Source box. If the row number in it is not that cell's row, the rule is anchored to the top of the column and every row below it is being offered the wrong list.
Try it in the grid05A Name Cannot Hold a Space
Defined names allow letters, numbers, underscores and full stops, and nothing else. South West is a depot, but South West cannot be a name, so Create from Selection quietly writes South_West instead and =INDIRECT($B2) asks for a name that does not exist.
The fix is one more function in the Source box:
=INDIRECT(SUBSTITUTE($B2," ","_"))
The same applies to a name starting with a digit, to a name that collides with a cell address such as Q1, and to punctuation of any kind. Ampersands in supplier names are the usual culprit, and they fail the same silent way: an empty dropdown and no error.
An empty dropdown is a missing name nine times in ten. The arrow still appears, the list has nothing in it, and anybody in a hurry types the value instead — which is how a machine the depot does not hire ends up in the cell.
Scenario: Put =SUBSTITUTE($B2," ","_") in a spare cell beside your parent column and compare the result with the names in Name Manager, character for character. Mind the depots whose names end in a space from an old import.
06The List That Stopped Short
A named range is a fixed address. Midlands = Rates!$B$2:$B$5 is four cells, and it is four cells for good, however many machines somebody adds underneath.
Halloway added a 13-tonne excavator to the Midlands column in August, in row 6. The name still ended at row 5. The list offered three machines, the 13T was not among them, and the desk picked the 8T at £175.00 for a machine that hires at £265.00.
Nothing failed. The pair the desk chose exists on the rate card, so the rate found it and the row invoiced £1,400.00 against £2,120.00 owed. The checks in section 12 do not catch this row, which is why the list has to be able to grow.
| How the list is defined | Does it grow? |
|---|---|
=Rates!$B$2:$B$5 | No |
=Rates!$B:$B | Yes, with a blank for every empty cell |
=OFFSET(Rates!$B$2,0,0,COUNTA(Rates!$B:$B)-1,1) | Yes, and recalculates constantly |
A table column, =Rates[Midlands] | Yes |
=SORT(UNIQUE(FILTER(...))) | Yes |
Format the rate card as a table with Ctrl+T and the column name grows with the data. A dropdown built on UNIQUE and FILTER, in the next section, grows as well, and needs no names at all.
Scenario: Add one row to the bottom of your own source list, then open a child cell's dropdown. If the new value is missing, open Name Manager and read the last row number in Refers to — that is the date your list stopped keeping up.
Try it in the grid07UNIQUE, FILTER and a Spill Reference
A tidy rate card does not need a column per depot at all. Keep it as three plain columns — depot, machine, rate — and let the list come out of it:
=SORT(UNIQUE(FILTER(Rates!$C$2:$C$40,Rates!$B$2:$B$40=$B2)))
In Microsoft 365, that formula goes straight into the Source box with $B2 relative, and each row of the column gets its own list, sorted and free of duplicates. One rule, no names, nothing to extend.
If your build refuses a formula there, spill the list into a helper cell instead — say K2 — and point Source at =$K$2#. Older builds that will not take the # either take a defined name whose Refers to is =Sheet1!$K$2#.
| Approach | Needs | Grows |
|---|---|---|
INDIRECT over names | A name per parent | Only as a table column |
INDIRECT plus SUBSTITUTE | The same, with spaces handled | Only as a table column |
FILTER in the Source box | 365 | Yes |
A spill reference, =$K$2# | 365 | Yes |
The rate card is then one block to maintain, and the same block feeds the lookup in column D. A list and a lookup built from different ranges will disagree eventually, and the disagreement is what a job sheet invoices.
Scenario: Put =SORT(UNIQUE(FILTER(Rates!$C$2:$C$40,Rates!$B$2:$B$40=$B2))) in a spare cell beside one job row and look at what spills. That is exactly what the dropdown should offer; if the two differ, the dropdown is reading something older.
08Paste Walks Straight Through Validation
Paste does not test a value against the destination's rule. It replaces the rule with the source cell's, or with no rule at all, and copies the value in regardless. One Ctrl+V can strip validation from a block and leave the arrows gone.
JS-4031 arrived that way, from the hire desk's email: 8T Excavator , with a trailing space. It is a legal entry nowhere, it looks identical to the row above it on screen, and SUMIFS matched nothing — the row invoiced £0.00 for five days.
=LEN(C9) → 13
=LEN(TRIM(C9)) → 12
=EXACT(C9,"8T Excavator") → FALSE
Paste Special → Values pastes the value without touching the rule, but it still does not test it, so the stale pair arrives all the same. Nothing in Excel validates on paste.
What does find it afterwards is Data → Data Validation → Circle Invalid Data, which rings every cell whose current value fails its own rule. It is the one click that reads the whole sheet, and it would have ringed JS-4019, JS-4026 and JS-4031 together.
Scenario: Run Circle Invalid Data on your own sheet now. Count the circles, fix them, then run =SUMPRODUCT(--(LEN(C2:C13)<>LEN(TRIM(C2:C13)))) to catch the ones a trailing space hides.
09Validate the Pair, Not the Two Cells
A cell holds one validation rule. A List rule fills the dropdown; a Custom rule can test anything you like; you cannot have both in the same cell. So the pair test has to live somewhere else, and a column of its own is the cheapest place.
=COUNTIFS(Rates!$B:$B,$B2,Rates!$C:$C,$C2)=1
TRUE means the rate card has exactly one row for this depot and this machine. FALSE means the row is unbillable, whether because the depot changed, the paste was dirty or somebody typed it by hand.
LET makes the same test readable when it carries the consequence as well:
=LET(pairs,COUNTIFS(Rates!$B:$B,$B2,Rates!$C:$C,$C2),
IF(pairs=1,"OK",IF(pairs=0,"No such pair","Duplicate on the card")))
A count above 1 is worth catching too. Two rows on the rate card for one depot and machine means SUMIFS adds both and the row invoices double, which is the only version of this fault that bills too much.
Scenario: Add that column to your own sheet, apply a conditional format of =$J2<>"OK" across the row, and the stale pairs turn red as they appear rather than at invoicing. Then sort by it before you export.
10Clearing the Child When the Parent Changes
The honest answer is that a typed cell cannot clear itself. A dropdown cell holds a value, not a formula, so there is nothing to recalculate when the depot beside it changes. Excel will not do it for you.
What you can do is make the staleness visible the moment it happens. The pair column from section 9 does that in one cell, and a conditional format on it does it in colour, across the whole row, without anybody running anything.
| Approach | What it costs |
|---|---|
| The pair column, red on FALSE | One column, no code |
| A Stop error alert on the child | Nothing, but only on entry |
A Worksheet_Change macro that clears the child | A macro-enabled file |
| Deriving the child from the parent with a formula | The child stops being a choice |
If the child can be derived, it should not be a dropdown. Where a depot only ever hires one machine class, =XLOOKUP($B2,Rates!$B:$B,Rates!$C:$C,"") is a cell nobody can leave stale, and there is no rule to keep in step.
Scenario: Decide which of your dependent cells are genuinely choices and which are consequences. Replace one consequence with XLOOKUP this week, and put the pair column beside the ones that stay.
11Why the Rate Was Nought and Not an Error
SUMIFS answers how much, over the rows that match. When nothing matches, the honest answer is nought, and nought is a number: it formats like money, totals like money and prints on an invoice like money.
A lookup asks a different question. XLOOKUP and INDEX/MATCH return #N/A when the key is not there, and #N/A is loud: it spreads to every total above it and somebody has to deal with it before the file goes out.
=SUMIFS(Rates!D:D,Rates!B:B,$B2,Rates!C:C,$C2) → 0.00
=XLOOKUP($B2&"|"&$C2,Rates!$F:$F,Rates!$D:$D) → #N/A
The second one builds a key out of the pair with & and looks it up in a key column on the rate card. Prefer the formula that fails loudly on a sheet that bills somebody, and keep IFERROR and IFNA off it until the noughts are understood.
Where SUMIFS has to stay — and it often does, because it adds and a lookup does not — guard it with the count:
=IF(COUNTIFS(Rates!$B:$B,$B2,Rates!$C:$C,$C2)=0,NA(),
SUMIFS(Rates!D:D,Rates!B:B,$B2,Rates!C:C,$C2))
Scenario: Find every SUMIFS in your workbook that feeds an invoice, a payment or a total somebody acts on, and ask what each one returns when nothing matches. Wrap the first one you find in the guard above.
12The Checks That Catch It Before Invoicing
Five cells above the table would have caught October, and each one is a question with an answer you already know.
- Does every row name a pair that exists?
=SUMPRODUCT(--(COUNTIFS(Rates!$B:$B,$B2:$B13,Rates!$C:$C,$C2:$C13)=0))must be nought. It is the whole of JS-4019, JS-4026 and JS-4031 in one cell. - Is any rate nought?
=COUNTIF(D2:D13,0)must be nought. A hire at nothing a day is never a hire; it is a pair that did not match. - Is any machine carrying spaces?
=SUMPRODUCT(--(LEN(C2:C13)<>LEN(TRIM(C2:C13))))must be nought, and it is the check that finds a pasted row. - Is any machine not on the card at all?
=SUMPRODUCT(--(COUNTIF(Rates!$C:$C,C2:C13)=0))must be nought. It separates a typo from a machine at the wrong depot. - Does the invoice reconcile to the rates?
=SUM(F2:F13)-SUMPRODUCT(D2:D13,E2:E13)must be nought, or a rate has been overtyped on a row.
Run Circle Invalid Data before the export, not after the query. It costs one click, it reads every validated cell on the sheet, and the circles disappear as soon as each value is put right.
Scenario: Put those five cells above your own table with the expected answer typed in the cell beside each one. Check them before the invoice run, and type today's date in the cell after that so the next person knows when they last passed.
Try it in the grid13Ten Things That Bite
- Validation runs on entry only. Change the parent and the child keeps a value its own list no longer offers.
$C$2anchors the list to the top row. Write the rule for the first cell of the selection with the row left relative.- A name cannot hold a space.
South WestbecomesSouth_West, andINDIRECTneedsSUBSTITUTEto find it. - A named range does not grow. The machine added in August never reaches the dropdown written in July.
- Paste bypasses validation entirely. It replaces the rule as well as the value, and no error is raised.
- A List rule leaves no room for a pair test. One cell, one rule: the pair check belongs in a column.
SUMIFSreturns nought when nothing matches. On an invoice, nought is a number and nobody queries it.- A Warning alert is not a Stop alert. It lets the value through on a click, and the row looks identical afterwards.
- Two rate-card rows for one pair bill double. Test for
=1, not for>0. - An empty dropdown gets typed around. A missing name shows an arrow and no list, and the next entry is free text.
14Mini Exercises
- Break it on purpose. Build the two dropdowns over the example sheet, fill a row, then change its depot. Watch the machine stay put and the arrow offer a different list.
- Find the three. Write
=COUNTIFS(Rates!$B:$B,$B2,Rates!$C:$C,$C2)=1down a spare column and confirm exactly three rows come back FALSE. - Anchor it wrongly. Write the child rule as
=INDIRECT($B$2), then open the dropdown on row 9 and read what it offers. Fix it to=INDIRECT($B2)and read it again. - Grow the list. Add a machine to the bottom of one depot's column and confirm the dropdown ignores it. Format the card with Ctrl+T, rebuild the name over the table column, and confirm it appears.
- Count the money. Total column F and column H on the example sheet and reconcile the £4,049.00 between them: £3,329.00 of it is three pairs that do not exist, and £720.00 is a 13-tonne excavator billed as an 8.
What to Take Away
Nothing on this sheet was broken. The dropdowns worked, the rate card was right, the rate formula did what SUMIFS does, and every row had been approved by a rule at the moment somebody typed it.
What nobody had written down is that the two cells are one fact. A depot and a machine are a pair, the pair has to exist on the rate card, and the only thing that keeps checking is a column that asks.
=COUNTIFS(Rates!$B:$B,$B2,Rates!$C:$C,$C2)=1 is that column. It costs one cell per row, it goes red the day a job moves depot, and it is the difference between a hire invoiced at £142.00 a day and a hire invoiced at nothing.