Data validation formula not working when applied to table column

DryBSMT

New Member
Joined
Dec 22, 2018
Messages
3
I have created a formula for use in validation entries in a table column. Both versions below evaluate properly to true/false when entered into a cell outside the table.

Code:
=OR(Calculator[@[Shop/WO]]="SHOP",AND(LEFT(Calculator[@[Shop/WO]],2)="HQ",LEN(Calculator[@[Shop/WO]])-3=5),AND(LEN(Calculator[@[Shop/WO]])=6,LEFT(Calculator[@[Shop/WO]],2)>RIGHT(YEAR(Calculator[@Date]),2)-2))
When I insert this into a custom type validation formula rule and apply it to the column Shop/WO, everything comes back with a validation error.

If I change the table column references to cell references, there are no errors reported even if I deliberately enter wrong data.

Code:
=OR(B5="SHOP",AND(LEFT('Calculator'!B5,2)="HQ",LEN('Calculator'!B5)-3=5),AND(LEN('Calculator'!B5)=6,LEFT('Calculator'!B5,2)>RIGHT(YEAR('Calculator'!A5),2)-2))
Am I doing something wrong or is there another way to accomplish this?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

DryBSMT

New Member
Joined
Dec 22, 2018
Messages
3
Well after posting this, I was rethinking the problem and realized that when I was first trying this, I did not have the = sign starting the validation formula. I tried it again and it does work. Not sure hoiw to delete this thread though. Sorry.
 

DryBSMT

New Member
Joined
Dec 22, 2018
Messages
3
I also discovered I had the wrong comparator in the last statement. My bad.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,164
Messages
5,467,010
Members
406,518
Latest member
SilverSixx

This Week's Hot Topics

Top