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

 

[Modify_inc]

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.

I have Excel 2016 and it isn't working like that, at least with mine. Remember my original post/issue, when the formula I was using wasn't converting the cells with formulas to zz--, so instead I had a bunch of blank spaces. And when I sorted them, I still had blank spaces, which were always sorted first before the actual data. But you saying the blank spaces should have been removed automatically during a sort, correct?

Maybe it's my sort code, so here it is if that helps:

Sub SortManual()
Application.ScreenUpdating = False
Application.EnableEvents = False

' SortResults Macro - Sorts the Word Results Manually
  
    Range("BF4:BF51").Copy
    Range("BG4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Game").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Game").Sort.SortFields.Add Key:=Range("BG4"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Game").Sort
        .SetRange Range("BG4:BG51")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Range("A4").Select
        
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  
End Sub

 

[maabadi]

I think You should change SkipBlanks:=False to SkipBlanks:=True

 

[jasonb75]

It could be that I'm misinterpreting the data layout, the formula doesn't sort the data it should just remove the blanks as in this simple example. For sorting the data as well it will need a different approach, but first I need confirmation that my interpretation of the layout is correct.

In the example, 3 in A1 is matched to column C to find the correct row, the data in that row is then returned from A3 downwards without the blanks (but in the same order as the original data).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	3		2
2			3		a		c			e		d		b
3	a		1
4	c
5	e
6	d
7	b
Sheet1

Cell Formulas
Range	Formula
A3:A7	A3	=IFERROR(INDEX(A:O,MATCH($A$1,C:C,0),AGGREGATE(15,6,COLUMN(D:O)/(INDEX(D:O,MATCH($A$1,C:C,0),0)<>""),ROWS(A$3:A3))),"")

 

[jasonb75]

Not much use to you as you don't have the functions but just to show how much easier things are with newer versions, this formula for 365 does the same as the one above, but with the blanks eliminated and the results sorted in order as well.

=LET(rng,INDEX(D:O,XMATCH(A1,C:C),),TRANSPOSE(SORT(FILTER(rng,rng<>""),,,1)))

With this version the formula only needs to be entered into a single cell, everything else is done automatically.

 

[Modify_inc]

I think You should change SkipBlanks:=False to SkipBlanks:=True

I was certain that would be it, but after making the change, and saving, and trying it again...I still had the spaces after sorting. Go figure! Should have known that would be too easy.

 

[Modify_inc]

It could be that I'm misinterpreting the data layout, the formula doesn't sort the data it should just remove the blanks as in this simple example. For sorting the data as well it will need a different approach, but first I need confirmation that my interpretation of the layout is correct.

In the example, 3 in A1 is matched to column C to find the correct row, the data in that row is then returned from A3 downwards without the blanks (but in the same order as the original data).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	3		2
2			3		a		c			e		d		b
3	a		1
4	c
5	e
6	d
7	b
Sheet1

Cell Formulas
Range	Formula
A3:A7	A3	=IFERROR(INDEX(A:O,MATCH($A$1,C:C,0),AGGREGATE(15,6,COLUMN(D:O)/(INDEX(D:O,MATCH($A$1,C:C,0),0)<>""),ROWS(A$3:A3))),"")

I'll just upload a sample so there is no confusion. Wait..is there no option to upload an Excel file anymore?
Here is a link via my Dropbox:

Dropbox

www.dropbox.com

 

[maabadi]

You can Use XL2BB addin. at above of reply section before undo key , you can see it.

 

[jasonb75]

Wait..is there no option to upload an Excel file anymore?

I don't think that there ever has been an option to upload a workbook, only to post sample using XL2BB (or similar tools with the old forum).
I'm not going to be able to do anything with it today but I'll have a look at your linked file as soon as I get chance, probably tomorrow evening (uk time).

 

[Fluff]

How about use this formula

=INDEX($G:$BB,MATCH($A$3,$C:$C,0),ROWS(BF$4:BF4))

and then use

Range("BG4:BG51").Value = Range("BF4:BF51").Value

instead of

Range("BF4:BF51").Copy
    Range("BG4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

 

[Modify_inc]

Range("BG4:BG51").Value = Range("BF4:BF51").Value

Perfect! That work better than expected and the formula is much easier to work with than that lengthy one I was using. So what is it exactly in this formula or vba code that makes this work, if you care to explain?