Hello. I am using the latest version of LO in Windows 10. I am attaching a condensed version of my .ods file for reference. Columns A to F are my data, and the formulas are in columns H to N and O to U. The formulas in columns H to N refer to data in row 1 and the formulas in columns O to U refer to data in row 2. I want an ‘X’ to print in the formula columns if the number (1 to 49) corresponds to any data in the respective data rows. It sort of works, but it is picking out single digits from the data instead of the whole numbers, and wrongly marking an X for the single digits. For example, there is an X in cell H3 because cell E1 (23) contains a 3; and the formulas in columns O to U show an X for 2 and 3 because of the 21 and 38 in row 2. I have marked the offending cells in orange.Sample for Reference.ods
Don’t use "*"& nor &"*" next the number you are looking for. You can Find & Replace unchecking Regular expressions.
So H1 formula must be:
=IF(COUNTIF($A$1:$F$1;"1");"X";"")
Instead of:
=IF(COUNTIF($A$1:$F$1;"*"&1&"*");"X";"")
Edit your question if you want to add more information; also can comment an answer (Add Answer is reserved for solutions).
Check the mark (
) to the left of the answer that solves your question.