Problem with ISBLANK when there is a formula cell

[m_in_spain]

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

 

[shift-del]

With some helper columns.

	BZ	CA	CB	CC

<colgroup><col style="width: 28ptpx"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody>
[TD="colspan: 5"]Arbeitsblatt mit dem Namen 'Tabelle1'[/TD]

[TD="bgcolor: #cacaca"]2[/TD]
[TD="align: right"]3,0074[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]74[/TD]
[TD="align: left"]Max[/TD]

[TD="bgcolor: #cacaca"]3[/TD]
[TD="align: right"]5,0076[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]76[/TD]
[TD="align: left"]York[/TD]

[TD="bgcolor: #cacaca"]4[/TD]
[TD="align: right"]7,0075[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]555[/TD]

[TD="bgcolor: #cacaca"]5[/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: left"] [/TD]

[TD="bgcolor: #cacaca"]6[/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: left"] [/TD]

[TD="bgcolor: #cacaca"]7[/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: left"] [/TD]

[TD="bgcolor: #cacaca"]8[/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: left"] [/TD]

[TD="bgcolor: #cacaca"]9[/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: right"]#ZAHL![/TD]
[TD="align: left"] [/TD]

</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]

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?