digest
Short Summary
Main recommendation: Build a formula that keeps "AA-" and "-17" fixed while the middle number increments by copying the formula down, using ROWS with an expandable range and the TEXT function for leading zeros. The video also warns against using ROW instead of ROWS for number incrementers, because inserting rows can break the numbering.
Key Points
- The formula is built by joining fixed text ("AA-" and "-17") to a numeric element with ampersands, so only the middle part changes as it is copied down.
- ROWS counts the rows in a range; locking the first cell's row reference (for example C$4) and leaving the second free makes the range expand as the formula is copied down.
- Leading zeros are added inside the formula with TEXT(number,"0000"), because custom number formatting cannot be applied to a cell that contains text.
- Using ROW with a single cell reference (such as A1) can report incorrect numbers if rows are inserted, so the ROWS-with-expandable-range method is preferred.
Details Worth Noting
- Concrete instructions:
- Type =“AA-” in the first cell (the text must be in double quotes), then join it with an ampersand to the number element and the closing text "-17".
- In the number element, use ROWS with an expandable range: for the cell in row 4 (C4), enter ROWS(C$4:C4), then copy the formula down. The first reference stays locked and the second moves with the copy.
- In the top cell's formula, use ROWS(A$4) with a dollar sign locking the row to the cell the formula sits in, so the count matches that row.
- To pad the number with leading zeros, wrap the number element in TEXT(ROWS(...),"0000"). Four zeros give four-digit numbers.
- Verify the result by copying the formula down and checking the expanding range and the zero-padded output.
- The video notes that formatting with Ctrl+1 and a custom format fails on cells containing text, which is why TEXT is used instead.
- The spreadsheet example uses cells A4 down to around A8 for the sample pattern.
Bottom Line
Using ROWS with an expandable range and TEXT for padding gives a robust sequential middle number that survives row insertions, which ROW with a single cell reference does not.
