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)))

 

[Fluff]

When using PasteSpecial, if the copied cell contained anything (even "") then something has to be pasted (in this case a null string), but when using .Value=.Value if the cell contains "" it's ignored so you get an empty cell.

 

[Modify_inc]

When using PasteSpecial, if the copied cell contained anything (even "") then something has to be pasted (in this case a null string), but when using .Value=.Value if the cell contains "" it's ignored so you get an empty cell.

Interesting...thanks for the quick explanation!

 

[Fluff]

You're welcome & thanks for the feedback.