Substitute reference in cell formula with another reference.

Hello everyone

I have found a Data Validation formula that works well with what I need. However, the formula exceeds the 255 character limit. So I am pasting this formula on a cell on another sheet in the same workbook. So in the Data Validation field, I would like to substitute the formula with the appropriate cell reference for each cell to be validated.

This is the validation formula that exceeds 255 characters:

Please Login or Register  to view this content.

Where xxx is to be substituted with D2, D3, and so on.

In the Data Validation field, I typed

Please Login or Register  to view this content.

However it evaluates to a #NAME? error.

How do I replace the formula with the appropriate cell reference for each cell to be validated?

What exactly "xxx" be? a cell reference?
I believe SUBSTITUTE establishs a text string, not a formula to work in Data Validation.
Post a small piece of sample worksheet.

Yes, xxx is a cell reference.

I've attached a sample.

Column B would be the validation check for the column A.


I could simply type =B2 in the Validation field, however, the validation formula fills up all the rows and causes my file to become very big and becomes a memory hog. I thought it would save much more space and memory to just refer to a single cell with a formula and replace the formula with the appropriate cell reference

In actual file, is List alphabet characters from a-z?

I guess your format is:
"VB000000-0000xxx"
with xxx is character, not value?

If yes, there should be AND with 3 statements:

EXACT(LEFT(A2,2),"VB")
ISNUMBER(MID(A2,3,6)+MID(A2,10,4))
NOT(ISNUMBER(RIGHT(A2,3)+0))

combine together:

=AND(EXACT(LEFT(A2,2),"VB"),ISNUMBER(MID(A2,3,6)+MID(A2,10,4)),NOT(ISNUMBER(RIGHT(A2,3)+0)))

put it in to data validation, with Allow:Custom

Opp, I forgot the "-" in 9th position. Just add.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Will still evaluate to true if one or two out of the 3 characters are non-alphabets. They must evaluate to FALSE if one of them is a non-alphabet, so I'll stick to the old formula, but thanks for your suggestions, it's further shortened.