#VALUE error when using formula on specific cells

[Luis Eduardo Rodríguez Alger LC]

Hi! I think I have found a way around my problem, but I'm still curious... I'd like to know why it's happening... I'm cross-referencing two different sheets in the same document in an "IF" formula. Sheet 1 (where I'm working),cell E7 has the following formula: =IF(AND('- A -'!C6:C11<>"",'- A -'!E6:E11<>""),'- A -'!E17,"No"). This formula works perfectly: if the selected cells in sheet "- A -" contain data, I get a true condition; if one of the selected cells doesn't have any data, I getfalse... I later write the same formula a couple of cells down, say E15 (still on sheet 1), with one difference... I reference a different sheet (- D -): =IF(AND('- D -'!C6:C11<>"",'- D -'!E6:E11<>""),'- D -'!E17,"No"). At this point, I get th following error: "#VALUE". I, then, try the exact same formula, to the last coma... and I still got the error. After playing with it for a bit, I noticed that in the first case, the cell with the formula was inside the rage of selected cells (E7); in the second case, it wasn't (E15). This was the only difference... So I changed the range of selected cells and the error disappeared. I tried the same formula on different sheets and workbooks, and got the same result... Anybody have any ideas? If you're interested, I could share my file so you can inspect it directly...

[#2 · Accepted solution]

Hello Luis,

Yes, try to move up the cell E15 to the cell E11, and will notice that the error disappeared because the array formula is now parallel to one of the cells that are in the evaluated array!

Anyway, the result of the formula isn't accurate because you do not evaluate the whole ranges C6:C11 and E6:E11!

Actually, you evaluate only this parallel cell!

To check that, put the formula in cell E7 on Sheet1, then go to Sheet - A -, clear the cell C7 or cell E7, and then you will get the value_if_false.

This is an array formula, so you have to press Ctrl+Shift+Enter at the same time to force the formula to handle the whole array and to get rid of that error.

[#3]

Thanks! You're a genius! Now I understand the full inner workings of that formula... and you helped me find a better solution than mine and a more precise execution than the original formula (the one I erased and was trying to reconstruct... and now I found in a past version of another file...: yeah! I think we all know what it feels like to erase something important and not realizing what you've done till it's too late!). Well, thanks again!