Formula for Blanks in a Table Array

Highlighted
Occasional Visitor

Hello, I need some help on what formula to use to try so that I can sort the table based on blanks. 

 

I am trying to automate an audit process by creating a macro. I will be looking at about a 500-1000 row excel sheet with only 5-6 columns of information I am trying to pull in to see if we have blanks.

 

I have first set up an IF statement to bring this information to a second sheet, formula is as follows (=IF('Sheet1'!E2="",'Sheet1'!$C2,0) -> so basically IF E2 is blank bring me back C2, if false 0.

 

Most of the information will come back to me as 0s as most of the information will be populated, but there will always be a few without fields I need. What I am trying to accomplish is another column that would allow filter on the information so that I can put those 0s at the bottom, so for example IF a column is blank = X, if false 0 so you cna filter this. I have tried IF ISBLANK statements with a "" in there instead of 0 (this does not work because the cell is not really blank) and as well as IF various if statements and I only seem to get #VALUE? and #NAME? errors. 

1 Reply
Highlighted

These two articles may prove helpful for your situation:

 

https://support.microsoft.com/en-us/help/214244/xl-how-to-create-a-formula-to-correctly-evaluate-bla...

 

https://colinlegg.wordpress.com/2014/10/31/is-your-cell-blank-or-empty/

 

If these two articles don't help with your solution I would be happy to review your scenario further if you could attach an example file with more detail provided.  Perhaps a before and after example.  (i.e. current state vs. future state).