Formula-Generated Blank Cell is Not Recognized by Formula

[Modify_inc]

Can someone please tell me why the following formula does NOT work correctly when used to process cells that contain a formula-generated value but works fine when the data in the cells are entered "manually"?

So I don't have to enter data manually in all the cells, I decided to use a simple formula in each cell that actually links to the data.

The problem is the way Excel handles the linking cells that it links to when that other cell is empty. It seems the result can either be a zero or a blank cell. I have it so it will show as a blank for cells that are empty using this formula:
IF(ISBLANK(Sheet1!D4),"",Sheet1!D4)

The below formula may look slightly daunting, but it primarily just takes my current data that is horizontal, and list it vertically down a column. Nothing major there. But it also, and most importantly, converts any blank cells to "zz--" as you can see in the formula below. Unfortunately, it for some reason refuses to do that for cells that contain a formula-generated "blank", though works fine if the cell is actually blank (no formula).

Any suggestions how I can get this to work with formula-generated "blank" cells? The only reason for the zz-- is so when I sort the list later, all the blank lines are moved to the bottom. Honestly, I wish I could just remove all the blank lines so none of this would even be necessary, but apparently Excel doesn't make this easy.

IF(INDEX(Sheet2!$G:$BB,MATCH(Sheet2!$A$3,Sheet2!$C:$C,0),ROWS(BF$4:BF4))=0,"zz--",INDEX(Sheet2!$G:$BB,MATCH(Sheet2!$A$3,Sheet2!$C:$C,0),ROWS(BF$4:BF4)))

 

[maabadi]

Change formula to this:

=IF(OR(INDEX(Sheet2!$G:$BB,MATCH(Sheet2!$A$3,Sheet2!$C:$C,0),ROWS(BF$4:BF4))=0,INDEX(Sheet2!$G:$BB,MATCH(Sheet2!$A$3,Sheet2!$C:$C,0),ROWS(BF$4:BF4))=""),"zz--",INDEX(Sheet2!$G:$BB,MATCH(Sheet2!$A$3,Sheet2!$C:$C,0),ROWS(BF$4:BF4)))

 

[jasonb75]

An empty cell has a text value of "" and a numeric value of 0, once you enter something into the cell (formula or other) it only has the value that is entered, in your case it has a text value of "" so the =0 part of your formula is ignored. Changing =0 to ="" will do the trick.

There are ways to ignore the blanks, the correct one would depend on the layout and type of data in the source.

 

[Modify_inc]

Change formula to this:

=IF(OR(INDEX(Sheet2!$G:$BB,MATCH(Sheet2!$A$3,Sheet2!$C:$C,0),ROWS(BF$4:BF4))=0,INDEX(Sheet2!$G:$BB,MATCH(Sheet2!$A$3,Sheet2!$C:$C,0),ROWS(BF$4:BF4))=""),"zz--",INDEX(Sheet2!$G:$BB,MATCH(Sheet2!$A$3,Sheet2!$C:$C,0),ROWS(BF$4:BF4)))

Thank you! That worked!

 

[maabadi]

You're Welcome & thanks for feedback.

 

[Modify_inc]

An empty cell has a text value of "" and a numeric value of 0, once you enter something into the cell (formula or other) it only has the value that is entered, in your case it has a text value of "" so the =0 part of your formula is ignored. Changing =0 to ="" will do the trick.

There are ways to ignore the blanks, the correct one would depend on the layout and type of data in the source.

Both of the suggestions worked, thank you both! Though I'm using yours because I only needed to change the 0 to "".

Just curious, did you have any suggestions for actually removing the blanks/zz-- so they aren't actually listed at all?
It's not a big deal if not, though it would clean it up. Just seems silly to have non relevant data in the list.

 

[Modify_inc]

I don't want to start another post unless it's required, but I'm having another issue that I just noticed when using these formulas in my cells to link to my data.
I had a simple formula that counts the number of non blank cells in a range using CountA(), but now because I have a linking formula in each of the cells, it now counts all of the cells as if they're not blank.

After reading about this, this seems to be by design. So how do I count a range of cells for non-blank cells, without counting the truly blank ones that are now considered NON-blank just because I have a formula in them? Seems like a paradox or something. I mean, how do you get around this? Are there other functions or formulas that will count them correctly?

 

[jasonb75]

Without seeing the source data and without knowing whcih version of excel that you are using, the best that I can suggest would be something like

=IFERROR(INDEX(Sheet2!$A:$BB,MATCH(Sheet2!$A$3,Sheet2!$C:$C,0),AGGREGATE(15,6,COLUMN(Sheet2!$G:$BB)/(INDEX(Sheet2!$G:$BB,MATCH(Sheet2!$A$3,Sheet2!$C:$C,0),0)<>""),ROWS(BF$4:BF4))),"")

which will move the actual data up so that the blanks are at the bottom rather than in the middle of the list. Is that what you were trying to do?

There are functions in some of the latest versions of excel that can make tasks like this much easier.

For your follow up question, try

=COUNTIF(range,"?*")

which will ignore formula blanks. Note that this will only count text, not numbers.

 

[Modify_inc]

Without seeing the source data and without knowing whcih version of excel that you are using, the best that I can suggest would be something like

=IFERROR(INDEX(Sheet2!$A:$BB,MATCH(Sheet2!$A$3,Sheet2!$C:$C,0),AGGREGATE(15,6,COLUMN(Sheet2!$G:$BB)/(INDEX(Sheet2!$G:$BB,MATCH(Sheet2!$A$3,Sheet2!$C:$C,0),0)<>""),ROWS(BF$4:BF4))),"")

which will move the actual data up so that the blanks are at the bottom rather than in the middle of the list. Is that what you were trying to do?

There are functions in some of the latest versions of excel that can make tasks like this much easier.

For your follow up question, try

=COUNTIF(range,"?*")

which will ignore formula blanks. Note that this will only count text, not numbers.

Thanks for the quick reply and suggestions!

Actually, I already have code that will sort the list, which sorts the zz-- to the bottom. I was just hoping there might be an easy way to just remove all the zz-- or blank cells with formulas from the generated list in the first place. I mean it's just useless data that serves no purpose for the end user. I wish Excel could differentiate between these types of blank cells that have formulas in them when it sorts them.

 

[jasonb75]

I was just hoping there might be an easy way to just remove all the zz-- or blank cells with formulas from the generated list in the first place.

That is pretty much what the formula should do. Unless you have 365 with dynamic arrays, you will always have to overfill the range and have some blanks for it to work.