Thanks :) Well, I tried to make a suduko (9 rows x 9 column) solver in calc. Now when I changed from swedish to english the problem dissaper, formula got little shorter. But here it is: If '1' is in same row, column or same 3x3 area, do't show '1'. Every small test return '1' if no '1' is found and multiplied together, so if product of multiplication is zero, there is a '1' present.

```
=IF(IF(ROW()=1,1,ISERROR(VLOOKUP(1,INDIRECT(CONCATENATE(CHAR(64+COLUMN()),1,":",CHAR(65+COLUMN()),ROW()-1)),2,0)))*IF(ROW()=9,1,ISERROR(VLOOKUP(1,INDIRECT(CONCATENATE(CHAR(64+COLUMN()),ROW()+1,":",CHAR(65+COLUMN()),9)),2,0)))*IF(COLUMN()=1,1,ISERROR(HLOOKUP(1,INDIRECT(CONCATENATE("A",ROW(),":",CHAR(63+COLUMN()),ROW()+1)),2,0)))*IF(COLUMN()=9,1,ISERROR(HLOOKUP(1,INDIRECT(CONCATENATE(CHAR(65+COLUMN()),ROW(),":I",ROW()+1)),2,0)))*IF(INDIRECT(CONCATENATE(CHAR(3*INT((COLUMN()-1)/3)+65+MOD(COLUMN(),3)),1+3*INT((ROW()-1)/3)+MOD(ROW(),3)))=1,0,1)*IF(INDIRECT(CONCATENATE(CHAR(3*INT((COLUMN()-1)/3)+65+MOD(COLUMN()+1,3)),1+3*INT((ROW()-1)/3)+MOD(ROW(),3)))=1,0,1)*IF(INDIRECT(CONCATENATE(CHAR(3*INT((COLUMN()-1)/3)+65+MOD(COLUMN(),3)),1+3*INT((ROW()-1)/3)+MOD(ROW()+1,3)))=1,0,1)*IF(INDIRECT(CONCATENATE(CHAR(3*INT((COLUMN()-1)/3)+65+MOD(COLUMN()+1,3)),1+3*INT((ROW()-1)/3)+MOD(ROW()+1,3)))=1,0,1),"1","")
```

Divided into smaller parts : If this cell is first row, 'return' 1, else search for '1' in this column from row 1 to this row minus one.

```
IF(ROW()=1,1,ISERROR(VLOOKUP(1,INDIRECT(CONCATENATE(CHAR(64+COLUMN()),1,":",CHAR(65+COLUMN()),ROW()-1)),2,0)))
```

If row is '9' (last) return '1', else search for '1' in this row plus 1 to row 9

```
IF(ROW()=9,1,ISERROR(VLOOKUP(1,INDIRECT(CONCATENATE(CHAR(64+COLUMN()),ROW()+1,":",CHAR(65+COLUMN()),9)),2,0)))
```

then two similar but for columns.
After that there are four checks for the four other cells that has not been checked with above formula in 3x3 area.

Search the upper left cell that has not been cheacked earlier

`IF(INDIRECT(CONCATENATE(CHAR(3*INT((COLUMN()-1)/3)+65+MOD(COLUMN(),3)),1+3*INT((ROW()-1 ...`

(more)
Maybe exposing the formula, someone can help make it shorter. It seems too long to fight with.

Libre Office 4.0.2 on Ubuntu 13.04: formula in a cell: up to 1300 characters (not clear if it depends on character set) Plain text in a cell: up to 65535 characters (not clear if it depends on character set)