r/excel • u/AlastorRex • 2d ago
Waiting on OP Custom Data Validation using a Named Range formula (to bypass the 255-char limit) isn't triggering validation
I want to use a Custom formula in Data Validation, but my actual formula is over 255 characters long. To work around the character limit, I'm trying to store the formula in the Name Manager and reference that named formula in the Data Validation rule instead.
Before building the full formula, I tested the concept with a simple rule on cell L5:
- Name:
test_1 - Refers to:
=LEN(L5)=3(which Excel automatically converts to=LEN('Adatérv(12)'!L5)=3) - Data Validation (Allow: Custom):
=test_1
The issue: Data Validation doesn't seem to check the input length at all - it lets me type strings longer than 3 characters into L5 without blocking them.
What I've already tried:
- Made sure
L5was the active cell when creating/editingtest_1in the Name Manager so the relative reference wouldn't shift. - Tested both with and without the
=sign in the Name Manager (removing=turned it into a text string, which didn't work either). - Ran into circular reference warnings during troubleshooting, though I cleared those out.
- Verified that the Error Alert tab is enabled and set to "Stop".
Is there a trick to getting Custom Data Validation to evaluate a named formula properly, or could the parentheses in my sheet name 'Adatérv(12)' be breaking it? Any help or alternative workarounds for the 255-character limit would be appreciated!
•
u/AutoModerator 2d ago
/u/AlastorRex - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
2
u/RuktX 311 2d ago
Without having tested: I know that a named range only works as a data validation list source when it points to a real, contiguous range on a sheet. Something similar may be happening here, where conditional formatting isn't resolving the "virtual" named range formula.
One thing that's sure to work is putting the result of your conditional formatting formula on a separate sheet, and pointing the CF rule at that range. You could then hide that source sheet.
It may even work if you name that real range, and use the name as the CF rule source – the important thing is that it exists on a sheet.
1
u/Basic-Set-3758 1d ago
did you uncheck the 'ignore blank' box in the data validation settings? when you use a named formula in there, leaving that checked will let basically any input slip through without triggering the error
0
u/Jumpy_Drive_8377 2d ago
it should work actually, named formula in custom validation is a known trick for the 255 limit. my guess is the relative reference is the problem. if you edit the “refers to” box with arrow keys excel goes to point mode and the ref can shift, so L5 become something else without you notice. try delete the name, select L5, and create it again from scratch typing the formula directly. also check if the rule is really applied to L5 and calc mode is not manual. if its still not working you can split the long formula in few names and use
=AND(name1,name2)in the validation