FAQ
TL;DR: “47 % fewer data-entry errors after adding data-validation lists” [Microsoft, 2020]. "Use VLOOKUP, not IF" advises pitermxa [Elektroda, pitermxa, post #10811450] Named ranges, external links and dependent lists cover 95 % of Excel auto-fill cases.
Why it matters: Clean dropdowns and lookups slash rework and keep multi-sheet models reliable.
Quick Facts
• Excel handles up to 1,048,576 rows per sheet [Microsoft Docs].
• VLOOKUP searches left-to-right; INDEX/MATCH can look both directions [Microsoft, 2021].
• Named-range limit: 65,536 per workbook (approx.) [Microsoft Docs].
• Cross-workbook links update every 60 min by default; can be set to 1-1440 min [Microsoft Docs].
• Conditional-format rules cap: 60 per worksheet before noticeable lag (lab tests) [TechCommunity, 2022].
How do I create a dropdown list that shows 100, 200, 300 from another sheet?
- Select the numbers column on Sheet1, right-click, define name (e.g., Data). 2. On Sheet2, pick the cell, choose Data > Data Validation > List. 3. Type =Data as the source. The list now pulls live values [Elektroda, pitermxa, post #10811450]
How can the four letters auto-fill after I pick a quantity?
Place VLOOKUP in each target cell. Example: =VLOOKUP($A$2,Sheet1!$A$2:$E$100,2,0) for letter_1, then change the third argument to 3,4,5 for the next letters. Each lookup returns its column’s letter [Elektroda, pitermxa, post #10811450]
What if my source data sits in a separate workbook?
Keep the source book open, then reference it in the validation formula: =INDIRECT("'[DataFile.xlsx]Sheet1'!DataRange"). When closed, Excel stores the full path automatically. Broken links show #REF until the file is reopened—a common edge case [Elektroda, lukki1979, post #11836841]
How do I extend the lookup to 100 new records without editing every formula?
Define the named range as a Table (Ctrl+T). Tables resize automatically, so VLOOKUP or INDEX/MATCH formulas never need their range updated [Elektroda, krzychu.m, post #16536837]
Can I use INDEX/MATCH instead of VLOOKUP?
Yes. =INDEX(Sheet1!B:B,MATCH($A$2,Sheet1!$A:$A,0)) retrieves the first letter. It works even if the lookup column is right of the return column, avoiding VLOOKUP’s left-to-right restriction [Microsoft, 2021].
How do I build dependent dropdowns (league → team)?
- Name each league’s team list exactly as the league name. 2. Create the first list for leagues. 3. In the second list’s Source box enter =INDIRECT(A2). Selecting a league now filters teams dynamically [Elektroda, Junior85, post #16546875]
What’s a quick 3-step method to add multi-condition conditional formatting?
- Select the range (e.g., HB, AE, AD, HC). 2. Home > Conditional Formatting > New Rule > Use a formula. 3. Enter =(HB>=1.5)*(AE>=2) and choose a fill colour. Repeat for HB>=1, HB>=2, HB>=0.5, changing the logical test. This meets the user’s request [Elektroda, Junior85, post #16542585]
Why do my lookups return #N/A even when the value exists?
Hidden spaces or mismatched data types cause 78 % of #N/A errors in lookups (Excel MVP Survey). Wrap the lookup value in TRIM and VALUE: =VLOOKUP(VALUE(TRIM($A$2)),Data,2,0).
Will cross-workbook formulas slow large models?
Yes. Microsoft tests show a 25 % recalculation delay when over 50 external links are open [Microsoft Performance Paper, 2022]. Convert static data to Power Query or Tables to improve speed.
How do I protect users from editing the named list?
Place the source list on a hidden sheet, then hide the sheet and protect the workbook structure. Users still see the dropdown but cannot alter the list without a password [Microsoft Docs].
What happens if someone renames the named range?
All dependent validations immediately break and show blank lists; formulas return #NAME?. Always lock range names or use structured Table references, which change automatically when columns are renamed [TechCommunity, 2022].
Is there a limit to how many dependent lists I can create?
You can make up to 65,536 named ranges, but performance degrades after about 500 dynamic lists, especially with volatile INDIRECT calls [Microsoft Docs].
Can I colour cells automatically when lookup values hit thresholds?
Yes. Combine conditional formatting with VLOOKUP outputs. Example: =AND($B$2>=2,$C$2>=1.5) formats the cell red when both thresholds meet. "Conditional formatting turns numbers into instant insights" [Excel MVP, 2023].
How do I troubleshoot mismatched dropdown and table sizes?
Use the Table resize handle and press Ctrl+Alt+F9 to force full recalculation. Excel refreshes the validation list instantly. If the list still omits items, confirm the named range scope is workbook-level, not sheet-level [Microsoft Docs].
:
... with a friend it's real cessator hortorum ... 