Formula-Generated Blank Cell is Not Recognized by Formula

Modify_inc

Board Regular
Joined
Feb 26, 2009
Messages
77
Office Version
  1. 2016
Platform
  1. Windows
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)))
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Change formula to this:
Excel Formula:
=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)))
 
Upvote 0
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.
 
Upvote 0
Change formula to this:
Excel Formula:
=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!
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
Excel Formula:
=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
Excel Formula:
=COUNTIF(range,"?*")
which will ignore formula blanks. Note that this will only count text, not numbers.
 
Upvote 0
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
Excel Formula:
=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
Excel Formula:
=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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top