Excel validation formula referring to another cell

Question

I have a big, complicated workbook, recalculation takes several seconds . I'm trying to validate (e.g.) [J2] depending on the content of other cells.

I have the logic in another cell, AE2.
=OR(AD2="",IF(AND(AD2=AD1,G2 = G1, H2 = H1,J2 = "same items"), TRUE, IF(ISNUMBER(J2), ROUND(ABS(J2-1),0) = J2-1, FALSE)))

AE2 displays TRUE or FALSE as I want.
I'm using AE2 to conditionally format J2 and that works as I want.

Ideally, to ease comprehension and maintenance, I'd like the custom validation on J2 to refer to AE2;
Error- Alert Style: Stop
Settings Allow: Custom Formula: =AE2.
That doesn't restrict data entry, you can enter anything with no messages.

I've tried entering the full formula into Settings Formula. It still doesn't restrict data entry, you can enter anything with no messages.

Any hints?

Edited to Add:

Valid entries in J2 depend on other cells.

• Integers > 1 are always valid.
• If AND(AD2 = AD1,G2 = G1, H2 = H1) is TRUE then text value same items is also valid.

Edited to Add:
The formula given above works if none of AD1, AD2, G1, G2, H1, H2 are blank. I'm beginning to think the calculation of formulae inside a custom data validation differs from that in a worksheet cell. Back to trying

Answer 1

A somewhat different approach worked quite neatly.

First, a correction to my original question: when I said "Integers > 1 are always valid" I meant "Integers > 0 are always valid", that doesn't substantially change anything.

1) "same item" validity
In a spare column (AF) I put the formula to assess whether "same item" is a valid entry
= AND(AD2 = AD1, G2 = G1, H2 = H1).
This had the minor advantage of simplifying the formula in AE2 which now becomes
=OR(AD2 = "", AND(AF2, J2 = "same items"), IFERROR(ROUND(ABS(J2-1),0) = J2-1, FALSE))
(thanks to @FoxfireAndBurnsAndBurns for reminding me about IFERROR)

2) column of valid items
In another spare column (AH) I listed all the valid entries, including the text "same items" as the first value in the list. This did place an upper limit on valid integers, but in my case a few thousand is plenty (it's numbers of items tested).

3) named range of valid items
Now I created a named range as a formula. I find formula named ranges very useful and I quite often use this construction to make the range auto-adjust to numbers of used rows.
n_items =OFFSET($AH$1, 0,0, COUNTA($AH:$AH))

4) list validation on target cell
Finally I set the validation on J2 to
Allow: List
Source: =IF($AF2, n_items, OFFSET(n_items,1,0))

Job done! Minor advantage, users can(when valid) select "same items" via drop-down rather than typing.

I still don't understand the behaviour of my original attempt or that of @FoxfireAndBurnsAndBurns, but I've found a solution which works for me and don't currently have time to go down that rabbit hole :-{

Answer 2

Ok, I've used the customized version of data validation and i got something

I used this formula:

`=IF(OR(AND(G2=G1;H2=H1;AD2=AD1;J2="same items");AND(J2>1;ISNUMBER(J2)=TRUE;IFERROR(INTEGER(J2)=J2;FALSE)))=TRUE;TRUE;FALSE)=TRUE`

I have translated from my spanish version of Excel, so I hope i did not mistake anything, but just in case, got a screenshot where you have to use this formula.

I typed it in the data validation box of J2

And it works for me:

Case 1: Only integers over 1

As you can see in the image above, values less than 2 or not integer are not valid

Case 2: Text same items but only if AND(AD2 = AD1,G2 = G1, H2 = H1)

As you can see, text value same items is accepted because conditions are met

If the conditions are not met, then the text value same items cannot be accepted

In the image above H2 is not equal to H1 so text value same items is not accepted.

Hope you can adapt this to your needs. I never thought you could do this with only Excel, but yeah! I learned something new.

I uploaded the file to mi Gdrive, if you want to check it out.

https://drive.google.com/open?id=1-ssBJgCHV1nsc91XYRuQXFoIQh1AB7Ca