Copying and Pasting Formulas with no Result as Values still being counted...

[JazzSP8]

Hey All

I've got a Worksheet which is doing a VLOOKUP and returning nothing ("") if a Zero is found or an Error occours;

=IFERROR(IF(VLOOKUP(A2,'BR01'!A:H,8,FALSE)=0,"",VLOOKUP(A2,'BR01'!A:H,8,FALSE)),"")

In the example I'm looking at I've got 7135 rows of data, 51 of those contain data for this column.

When the sheet has been populated I copy and paste it as values to a new Workbook.

The blanks appear to be blank, but they aren't been recognised as such.

If I do a COUNTA on the range then it gives me the full count of rows, 7135

The cells are displaying as blank as I'd expect.

If I use ISBLANK on a cell, it reports FALSE

If I click on the cell and then into the Formula bar there is nothing in there, no spaces, no nothing, it's like a brand new Formula Bar.

When I click out of the cell or press return / enter - The cell gets it's blank status - The ISBLANK formula immediately changes to TRUE and my COUNTA loses a number.

I tried a find / replace on " " but that didn't do anything.

I need these cells to be actually blank, I can't Clear Contents on the Column as there are values in there that I need.

I want to avoid a loop as some of the sheets I produce go into the tens of thousands of rows.

Has anyone come across this before or can offer a solution?

Thanks

 

[steve the fish]

Copy paste the formulas as values then highlight the column, go to the data tab, press text to columns and then finish. Your counta should be correct now.

 

[JazzSP8]

Copy paste the formulas as values then highlight the column, go to the data tab, press text to columns and then finish. Your counta should be correct now.

You, Mr. The Fish, are my new hero.

Worked like a charm

Any explanation as to what that does or why it happens, or is it just one of those things?

 

[steve the fish]

Its just the way excel behaves. You could always use a different formula eg

=SUMPRODUCT(--(LEN(A1:A5)>0))

 

[JazzSP8]

Fair enough, I've long since learned not to question these things any further than I need to do...

Thanks for your help, I'd never have thought of that