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

Modify_inc

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

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I think You should change SkipBlanks:=False to SkipBlanks:=True
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMN
132
23acedb
3a1
4c
5e
6d
7b
Sheet1
Cell Formulas
RangeFormula
A3:A7A3=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

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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

Board Regular
Joined
Feb 26, 2009
Messages
77
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Feb 26, 2009
Messages
77
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMN
132
23acedb
3a1
4c
5e
6d
7b
Sheet1
Cell Formulas
RangeFormula
A3:A7A3=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:
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,655
Office Version
  1. 365
Platform
  1. Windows
How about use this formula
Excel Formula:
=INDEX($G:$BB,MATCH($A$3,$C:$C,0),ROWS(BF$4:BF4))
and then use
VBA Code:
Range("BG4:BG51").Value = Range("BF4:BF51").Value
instead of
VBA Code:
Range("BF4:BF51").Copy
    Range("BG4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
 

Modify_inc

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

Watch MrExcel Video

Forum statistics

Threads
1,127,809
Messages
5,627,019
Members
416,215
Latest member
Ostie3994

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
Top