Troubleshoot my spreadsheet ...

[KneesUp]

I'm trying to get a spreadsheet to check if a number is repeated

So the formula in L4 (as shown) should, I believe

Check that there is no 4 is cells B2, C2 and D2 =IF(AND($B2<>L$1,$C2<>L$1,$D2<>L$1)

and if this is true (there are no 4s) it should show what's in L1 ,L1

and if this is false, it should show an the word Invalid cell ,"Invalid")

The 'false' bit works, bit the true bit does not - as you can see I just get 'True' rather than the value. In fact even if I replace the reference with a number, I just get 'True' - only if I put it is as a string (e.g. put it in speech marks so the spreadsheet thinks it is text) does it return anything else.

I expect I've misplaced a comma or something - I've done similar before with no issues, but I can't for the life of me see what I've done wrong here, so if I could borrow some more eyes that'd be grand.

Ta.

 

[MichaelO]

I've just pasted the formula into a spreadsheet, and it works fine. Is L1 formatted as text, or as a number (although, that shouldn't have an impact).

 

[MacB]

You can use an array formula to do the embedded if statements but it has to be enclosed in square brackets to work. If you look up array in help it'll tell you, if not I'll try and post later. I think it was someone on here that gave me the array formula solution as I couldn't get a multiple if/and thing to work.

 

[Bollo]

I can't get too distracted right now so sorry for the lack of example, but if the cell values are numbers then you could use the FREQUENCY function, and then test the highest value in the resulting array (MAX function?). If this is 2 or greater then there's a duplicate.

So something like =IF(MAX(FREQUENCY(....)) > 1,"Boo","Yay")

This approach has the advantage that it can extended easily to more than three columns by extending the cells over which FREQUENCY operates.

Apologies if there's some devil in the detail.

 

[AndyRM]

I can't help I'm afraid, but I will contribute one of my favourite Modern Toss cartoons:

 

[KneesUp]

I've just pasted the formula into a spreadsheet, and it works fine. Is L1 formatted as text, or as a number (although, that shouldn't have an impact).

Bingo! In getting the formula wrong initially (think I didn't press Shift so put in an = where there should have been a + when I was doing something else) I'd inadvertently formatted the cell as Logical, or somesuch - I cleared all formatting and it worked fine, which reassures me that with the formula at least I do have *some* idea after all

Thanks all!

 

[Bollo]

[QUOTE 4986191, member: 9609"]you could always do it with a wee sub -
View attachment 377171 [/QUOTE]
Aarghhhhh VBA!!! My eyes, my eyes.....!!!!