Hi,
First time, so not sure how this works. I’m just transferring a spreadsheet from MS Office, in which I check whether a value in one sheet (Sheet2) can be found within a range of cells in another sheet (Sheet1). In MS Office, the conditional formatting was: =ISERROR(MATCH(B4,Sheet1!$B$3:$B$150,0))
Where B4 in Sheet2 was being checked against the cells B3 to B150 in Sheet1. I’ve tried all the conditional formatting options, but can’t make it check properly to the cell range in the other sheet.
Any help appreciated.
You are using MSOffice addressing for sheet, try MATCH(B4,$Sheet1.B3:B150,0)
Earnest,
Many thanks for that. It is working, which is brilliant. However, it is working the wrong way! So it is highlighting the cell if it IS found in the column in Sheet1, and isn’t highlighting it if it’s NOT found in that column. I have tried to look it up in the internet help, but can’t find it. What do I need to change to make it change the format of the cell if it CAN’T find a match in the other sheet?(Is there such a thing as a “manual” that explains in full the format of all the LibreOffice spreadsheet commands?)
Tom
NOT(ISERROR(MATCH(…)))
or
ISNUMBER(MATCH(…))
Many thanks Andreas - what finally worked is NOT(ISNUMBER(MATCH(…))) - the NOT making it work the way round that I wanted it.
Thanks again
Tom
… because you were advised to only change the MATCH part (the address notation used there) of the whole =ISERROR(MATCH(B4,... thing, not drop the ISERROR part 
Villeroy,
It would be great if you could solve a mystery for me, but I don’t know if you ever deal with actual spreadsheets.
It’s such a simple thing, with a diary, and a list of dogs. I’m essentially using the conditional format idea that you sent back before.
All it does (on the diary sheet) is to check whether the name of any dog that is put into a cell is in the list of dogs on the dogs sheet, and put an error format if not. But for no reason that I can see, it returns errors for a few of the dog names that are in the list. I’ve checked the formatting etc, but it seems all OK. So it’s a complete mystery to me.
Many thanks, in anticipation.
Tom
Libre Puzzle.ods (29.6 KB)
Change formula
NOT(ISNUMBER(MATCH(B4;$DOGS.$A3:$A100;0)))
to
NOT(ISNUMBER(MATCH(B4;$DOGS.$A$3:$A$100;0)))
95% of today’s spreadsheet users try desparately to mis-use a spreadsheet as a database without having the expertise to use any spreadsheet application in unusual ways.
Your document is formatted to death without providing any functionality.
LibrePuzzle2.ods (39.4 KB)
2 Likes
Or use COUNTIF($DOGS.$A$2:$A$100;B4) directly