Using data validation, I am limiting the number of characters which can be entered into various columns. However, I have four columns, each of which need to be able to take 400 characters, but be limited to a total of 400 characters for the four columns. (They are being concatenated and then loaded into a VARCHAR(400) field in SQL Server). Is there any way to do this without using a macro? I have tried adding another column with the sum of the characters for the four columns and then applying the data validation to that column, but it doesn't work in a satisfactory manner. Can this be done without programming?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel-Limit Size of Four Fields 1
- Thread starter grnzbra
- Start date
-
1
- #2
SkipVought
Programmer
Hi,
YES!
Use a Custom Data Validation formula.
Assuming that the 4 columns are A
in row 2, the DV formula in each column is...
[tt]
=LEN($A2&$B2&$C2&$D2) <= 400
[/tt]
Skip,
[sub]
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my OLD subtlety...
for a NUance!![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
[/sub]
YES!
Use a Custom Data Validation formula.
Assuming that the 4 columns are A
in row 2, the DV formula in each column is...[tt]
=LEN($A2&$B2&$C2&$D2) <= 400
[/tt]
Skip,
[sub]
Just traded in my OLD subtlety...for a NUance!
[/sub]- Thread starter
- #3
- Thread starter
- #4
OOPS!
While running the last test before sending this out into the field, I found that if any of the fields is empty, the validation rule doesn't see the problem. This is what has been coming in from the field; the entire address is in the first of the four fields and the rest are blank.
For the purpose of determining the length of the four fields, it's kinda like concatenating a null into a string in T-SQL.
While running the last test before sending this out into the field, I found that if any of the fields is empty, the validation rule doesn't see the problem. This is what has been coming in from the field; the entire address is in the first of the four fields and the rest are blank.
For the purpose of determining the length of the four fields, it's kinda like concatenating a null into a string in T-SQL.
SkipVought
Programmer
in the DV > Settings tab...
uncheck ignore blank
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
uncheck ignore blank
Skip,
[sub]
Just traded in my OLD subtlety...for a NUance!
[/sub]- Thread starter
- #6
- Status