# Problem with ISBLANK when there is a formula cell

#### m_in_spain

##### Board Regular
Hi again
Today's problem involves lists and ISBLANK when i am trying to remove the blank cells.

1) I have a list over 3 columns, BV, BW and BX the lists go from Row3 to Row300. I have named BV3:BX300 as tab. Some have formulae in that produce text, others have a formula that produces a blank cell. for example =IF(fuel_type=oxy,"deleteAirHybrid","")

So if the fuel type is not oxy, the cell is blank... except it isnt!

Next i am trying to make one list of all the cells in tab that have visible data in. I do not want the ones that have a formula to make them blank.

The formula i have got to in cell BY2 is:

{=INDEX(tbl, SMALL(IF(SMALL(IF(COUNTIF(\$BZ2:BZ\$2, tbl)+ISBLANK(tbl)=0, COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), ROW(tbl)-MIN(ROW(tbl))+1), 1), MATCH(MIN(IF(COUNTIF(\$BZ2:BZ\$2, tbl)+ISBLANK(tbl)>0, "", COUNTIF(tbl, "<"&tbl)+1)), INDEX(IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), SMALL(IF(SMALL(IF(COUNTIF(\$BZ2:BZ\$2, tbl)+ISBLANK(tbl)=0, COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF(ISBLANK(tbl), "", COUNTIF(tbl, "<"&tbl)+1), ROW(tbl)-MIN(ROW(tbl))+1), 1), , 1), 0), 1)}

which i drag down to fill lower cells in column BY

this works fine when there is a visible string in tab and also when a cell is actually blank. However when there is a formula which makes a cell "appear" blank, in my new list in BY i receive the error #NUM ! in the list

So far I only have 6 "apparently" blank cells, and i get 6 of these errors under the remainder of the new list.

Sorry it is long winded, as ever, any help will be appreciated.

Thanks

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### shift-del

##### Well-known Member
With some helper columns.
 Arbeitsblatt mit dem Namen 'Tabelle1' BZ CA CB CC 2 3,0074 3 74 Max 3 5,0076 5 76 York 4 7,0075 7 75 555 5 #ZAHL! #ZAHL! #ZAHL! 6 #ZAHL! #ZAHL! #ZAHL! 7 #ZAHL! #ZAHL! #ZAHL! 8 #ZAHL! #ZAHL! #ZAHL! 9 #ZAHL! #ZAHL! #ZAHL!

<colgroup><col style="width: 28ptpx"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody>
</tbody>

 Zelle Formel BZ2 =AGGREGATE(15,6,(ROW(tbl)+COLUMN(tbl)%%)/(tbl<>""),ROWS(BZ\$2:BZ2)) CA2 =QUOTIENT(BZ2,1) CB2 =MOD(BZ2,1)*10^4 CC2 =IFERROR(INDEX(\$1:\$1048576,CA2,CB2),"")

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>
 Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016 Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>

#### m_in_spain

##### Board Regular
I sorted this problem with this formulahaving reduced it to 100 lines)

=IFERROR(IFERROR(IFERROR(INDEX(\$BV\$4:\$BV\$100, MATCH(0, COUNTIF(\$BY\$2:BY2, \$BV\$4:\$BV\$100)+(\$BV\$4:\$BV\$100=""), 0)), INDEX(\$BW\$4:\$BW\$100, MATCH(0, COUNTIF(\$BY\$2:BY2, \$BW\$4:\$BW\$100)+(\$BW\$4:\$BW\$100=""), 0))), INDEX(\$BX\$4:\$BX\$100, MATCH(0, COUNTIF(\$BY\$2:BY2, \$BX\$4:\$BX\$100)+(\$BX\$4:\$BX\$100=""), 0))), "")

Now, My list in column BY is filling up with only names, it brings me to the next problem!

I now need the individual results in column BY cells to be assigned to a name for each cell. I need to use the names a bookmarks in a Word document.

Is this possible?.. maybe i should start a new thread?

Replies
3
Views
102
Replies
4
Views
322
Replies
6
Views
97
Replies
2
Views
157
Replies
0
Views
82

1,126,944
Messages
5,621,751
Members
415,854
Latest member
Tutu123

### 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.

### Which adblocker are you using?

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

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