Forum Discussion

Michael Brouder's avatar
Michael Brouder
Copper Contributor
Mar 26, 2018

Formula for Blanks in a Table Array

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. 

Resources