Problem with ISBLANK when there is a formula cell

m_in_spain

Board Regular
Joined
Sep 28, 2018
Messages
55
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
516
Office Version
  1. 365
Platform
  1. Windows
With some helper columns.
Arbeitsblatt mit dem Namen 'Tabelle1'
BZCACBCC
23,0074374Max
35,0076576York
47,0075775555
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>

ZelleFormel
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
Joined
Sep 28, 2018
Messages
55
I sorted this problem with this formula:(having 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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,101
Members
416,161
Latest member
David1966Lewis

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