CODEWITHSUNDEEP
excelexcel-2010
Marco Suter
Marco Suter

Reputation: 13

DataValidation, strange error

I am trying to validate an Excel column to have only values containing a value with length > 1 and ending with "d". My attempt was the following formula:

=AND(LEN(INDIRECT(ADDRESS(ROW(),COLUMN())))>1,RIGHT(INDIRECT(ADDRESS(ROW(),COLUMN())),1) = "d")

Excel says that the formula contains errors. The strange thing is that the first part itself works correct. But when i put it into an AND i get the error. Also when i replace the inderect reference with an absolut one it will work:

=LEN(INDIRECT(ADDRESS(ROW(),COLUMN())))>1 => works

=AND(LEN(F4)>1, TRUE) => works

=AND(LEN(INDIRECT(ADDRESS(ROW(),COLUMN())))>1, TRUE) => fails

Why is this? Is there another way or a workaround to do this?

Thanks in advance, Marco

Upvotes: 1

Views: 95

Answers (1)

Vinny Roe
Vinny Roe

Reputation: 901

Not sure why you need this to be so complicated. In data/validation Custom formula for the top cell in the column, put something like =AND(LEN(H1)>1,RIGHT(H1,1)="d") (for col H, for example). Then Copy that cell, and Paste Special/Validation for the rest of the cells in the column. Any good?

Upvotes: 1

Related Questions