Referencing multiple Formulas across multiple worksheets

[andynewby]

After some searching in the forums, I wasn't able to find any info to help me out in my particular situation.

I think I may have crossed the threshold of my own abilities by trying to link cells to formulas in other worksheets within my workbook.

Let me try to explain myself:
I have 20 to 30 worksheets within my workbook. I would have created multiple workbooks, but so much of the data is linked between the sheets and I wanted to have it all in one file location. Anyway, I have a cell with the following formula in cell A6:
=IF(AND(E40>89, E41="Go", E42>89, I40>89, I41="Go", F8=0), "X", " ")

E40 is 80 which does not meet the first criteria to yield the TRUE value of X (all other criteria are met) - yet alas, there is an X in my cell.

It gets more interesting... I intended of having this data pulled from another worksheet.
While E40 shows 80, it is actually referencing a cell from another worksheet within my workbook. E40's fx ='M4 Scores'!F12

So - we go over to Worksheet 'M4 Scores' to cell F12 and this cell is also a formula:
=IF(E12<23,"No-Go",IF(E12<30,"70",IF(E12<36,"80",IF(E12<=40,"100"," "))))

This formula is dependent on E12's value, which is 35.

All the formulas work, except the first formula I mentioned:
=IF(AND(E40>89, E41="Go", E42>89, I40>89, I41="Go", F8=0), "X", " ")

The strange thing is that if I go back to the cell that alerted me to the problem (E40), and I manually type in 80 (which it already had displayed as a result from the formula), it instantly works.

If I evaluate the formula, the evaluation tells me that E40 is True - meaning that it is over 89, when it is clearly not. It is displaying an 80.

I am extremely confused. I have had other issues like this when referencing multiple formulas across multiple cells.

Can anyone help?

Thanks

[Cutter]

Your problem is likely in 'M4 Scores'!F12.

What is in that cell? Is it a formula? What is the formatting of that cell?

It sounds like E40 is returning a text value so you have to check the precedents to make sure they are sending a numeric value and not a text value that looks like it's a numeric value.

[andynewby]

I had tried this before, but I went ahead and did it again just to make sure. I took your advice and changed the root number (E12 on 'M4 Scores') cell format to number. It did not change A6 on my original sheet to " ". Still the same problem of the A6 formula performing incorrectly. Then I went and changed every cell involved that had a function to a number format. Still no change.
I think you are right in that the output value in A6 (80) is being read as text. But so far, the solution has not been to change the cell format to Number.
Any other solutions.

[andynewby]

I also tried doing away with the STEP "='M4 Scores'!F12 by simply replacing this reference with the actual formula found in 'M4 Scores'. Tried to skip a step. Still no change. A6 formula on the original sheet still performs incorrectly by identifying 80 as more than 89, yielding a TRUE value of "X".
Still baffled.

[Cutter]

A quick fix would be to have IF(AND(E40+0>89,....

But you should continue to trace the root of the error.

A quick check for number/text content is to format a range of cells with horizontal alignment as general. Widen the column to make it easier to see if necessary.
Text will align left - numbers align right. So, with that format applied you can identify anything that looks like a number but is aligned to the left as an impostor.

A less easy method is to use the ISNUMBER() function.