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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,648
Office Version
  1. 365
Platform
  1. Windows
When using PasteSpecial, if the copied cell contained anything (even "") then something has to be pasted (in this case a null string), but when using .Value=.Value if the cell contains "" it's ignored so you get an empty cell.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Modify_inc

Board Regular
Joined
Feb 26, 2009
Messages
77
Office Version
  1. 2016
Platform
  1. Windows
When using PasteSpecial, if the copied cell contained anything (even "") then something has to be pasted (in this case a null string), but when using .Value=.Value if the cell contains "" it's ignored so you get an empty cell.

Interesting...thanks for the quick explanation!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,648
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,801
Messages
5,626,965
Members
416,212
Latest member
Ifemiide

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