#VALUE error in Excel

Question

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 get *false...*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 still don't understand why the error occurs nor how can I get around solving it, because extending the range of selected cells is not a solution. Anybody have any ideas?

Moved From: Office / Excel / Microsoft Office Programming / Office 365 Home

Answer 1

We appreciate the clarification. Since this is more of troubleshooting with the formulas being used, as mentioned on the previous post, if using the guide is still the same and still you're getting troubles in getting the correct formula, then it would be best to have your concern to our Tech Community for Excel. This is so other community members that are on that channel, would be able to share their practices, or tips and tricks in getting you the right result and outcome that you would like to get from using the said functions.

If you have further questions, please feel free to reply to this thread.

Answer 2

Hi! Thanks for your prompt reply!

• I already read the article you suggested and tried the different options there offered
• No Macros... only formulas...
• I tried the same formula on a different Excel sheet: same result... I get a functioning formula when used on a cell within the range of selected cells, and it throws back an error when out of it...
• I tried the same formula on a different Excel file: same result...
• Windows 10 Home 1709 OS16229.248
• Office 365

I hope this info gives you a better idea of the situation. I´m really starting to think it might be a glitch, but I´m not sure...

Thanks!

Answer 3

Hi Luis,

There are a few possibilities on what could cause the the # Value error when you place an input with your data using the If function on your Excel. For us to better understand your concern, we would like to know a few things so we would be able to either place your thread on the correct category or provide you with the right steps:

• Are you using any VBA/Macro on your Excel on top of the If function that you used?
• Have you tried replicating if the formula if it is also happening on a new Excel sheet?
• Is this happening to Excel any files?
• What is your current build and version for your Windows and your Office?

In the meantime, you can refer to this article as this would provide you more information on how to address the #VALUE error on Excel depending on the function such as the one you're using.

Please don't hesitate to get back to us.