A question asks how to avoid #VALUE errors when a formula refers to a cell with a blank text value. In Episode 767, I will take a look at how these blank cells come to be, how to work around the #VALUE error and even a different approach to prevent the root cause of the problem.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Now, this question came up in a seminar, and I wish I'd gotten the person's name because when I came back and tested it, it was really tough for me to duplicate the problem that he said that he had.
He had one worksheet here with some data on it and he was basically using formulas to copy this data to another worksheet.
So, here, on my secondary worksheet, I have the headings and basically, you know, to set up a link between one sheet and another, it's fairly easy.
Type the = sign, use the mouse, go back and click on the right cell, and when we copy that across, all the values come through -- except for the frustrating issue of blanks.
The blanks come through as 0s all the time, and so this person said that he had rewritten those statements to be an IF statement.
=IF is blank (, and then he had his text.
If it's blank, he was putting in “” to make sure that nothing showed up, otherwise he wants the cell, and copy that down, and this is a common trick, but he said the big problem with that is, now, when he creates formulas that point into those cells -- so maybe we'll have a TOTAL here and this TOTAL needs to be just Q1+Q2 --what is going to happen is because there's blanks, we’re going to get value errors all the way through, okay, except for this case where both numbers happen to be numeric, but you're going to have some value numbers show through, and he said, you know, I don't know what to do about this.
[ =IF(ISBLANK(Sheet1!B4),“” ] Well, you know, a couple of things, when I first tried this, I couldn't replicate the problem because I was using the SUM function out here.
=SUM.
I want to SUM this cell, E4, and this, I4, and the SUM function is going to handle this perfectly, converting the labels to 0, and it'll work.
So, that's one solution.
The other solution is we could go backwards just to the original formula.
So, here, we just have the formulas that have lots of 0s filled in.
Let's go and set up a custom number format.
FORMAT CELLS, and we'll come to CUSTOM, and, basically, what we want to do is say, for positive numbers, we want to show 0, a ;, for negative numbers, we want to show – and a 0, and then, for 0, we don’t want to show anything.
So, that last ;, we're now in the third zone, the 0 zone, and we say we don't show anything for zero, click OK, and, sure enough, that's a very easy way to change all of the 0s to blanks.
Well, there you have it, a great way to make sure those 0s cells show up as blanks and still allow the formulas to work.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Now, this question came up in a seminar, and I wish I'd gotten the person's name because when I came back and tested it, it was really tough for me to duplicate the problem that he said that he had.
He had one worksheet here with some data on it and he was basically using formulas to copy this data to another worksheet.
So, here, on my secondary worksheet, I have the headings and basically, you know, to set up a link between one sheet and another, it's fairly easy.
Type the = sign, use the mouse, go back and click on the right cell, and when we copy that across, all the values come through -- except for the frustrating issue of blanks.
The blanks come through as 0s all the time, and so this person said that he had rewritten those statements to be an IF statement.
=IF is blank (, and then he had his text.
If it's blank, he was putting in “” to make sure that nothing showed up, otherwise he wants the cell, and copy that down, and this is a common trick, but he said the big problem with that is, now, when he creates formulas that point into those cells -- so maybe we'll have a TOTAL here and this TOTAL needs to be just Q1+Q2 --what is going to happen is because there's blanks, we’re going to get value errors all the way through, okay, except for this case where both numbers happen to be numeric, but you're going to have some value numbers show through, and he said, you know, I don't know what to do about this.
[ =IF(ISBLANK(Sheet1!B4),“” ] Well, you know, a couple of things, when I first tried this, I couldn't replicate the problem because I was using the SUM function out here.
=SUM.
I want to SUM this cell, E4, and this, I4, and the SUM function is going to handle this perfectly, converting the labels to 0, and it'll work.
So, that's one solution.
The other solution is we could go backwards just to the original formula.
So, here, we just have the formulas that have lots of 0s filled in.
Let's go and set up a custom number format.
FORMAT CELLS, and we'll come to CUSTOM, and, basically, what we want to do is say, for positive numbers, we want to show 0, a ;, for negative numbers, we want to show – and a 0, and then, for 0, we don’t want to show anything.
So, that last ;, we're now in the third zone, the 0 zone, and we say we don't show anything for zero, click OK, and, sure enough, that's a very easy way to change all of the 0s to blanks.
Well, there you have it, a great way to make sure those 0s cells show up as blanks and still allow the formulas to work.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
