# Cell returns 0 if blank.

#### dave2017

##### New Member
Hi,

I have the below formula that works however if a cell is blank it returns a 0, so is possible to have it return a blank if the cell is blank ?

=IFERROR(INDEX('Paste From Compile'!B:B,SMALL(IF('Paste From Compile'!\$M\$4:\$M\$100>0,ROW('Paste From Compile'!\$M\$4:\$M\$100),""),ROW('Site Sheet'!B3))),"")

Iam using Ms office 2019 home and business on Windows pro 10.

Thanks David

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Peter_SSs

##### MrExcel MVP, Moderator
Are the non-blank values in column B of 'Paste From Compile' Text values, Numerical Values or a mixture of both?

Iam using Ms office 2019 home and business on Windows pro 10.
Please add that information to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

#### dave2017

##### New Member
Are the non-blank values in column B of 'Paste From Compile' Text values, Numerical Values or a mixture of both?
Hi Peter,

Thanks for the reply, Yes just column B.

Thanks David

#### Peter_SSs

##### MrExcel MVP, Moderator

Are the non-blank values in column B of 'Paste From Compile' Text values, Numerical Values or a mixture of both?

Also please review my previous post as I edited it.

#### dave2017

##### New Member

Also please review my previous post as I edited it.
Hi Peter,

Apologies, I have updated my details.

Column B on the paste from compile sheet may be blank some times and will when data is entered it may contain a mixture of both.

Thanks David

#### Rahul1987

##### Board Regular
ps
Hi,

I have the below formula that works however if a cell is blank it returns a 0, so is possible to have it return a blank if the cell is blank ?

=IFERROR(INDEX('Paste From Compile'!B:B,SMALL(IF('Paste From Compile'!\$M\$4:\$M\$100>0,ROW('Paste From Compile'!\$M\$4:\$M\$100),""),ROW('Site Sheet'!B3))),"")

Iam using Ms office 2019 home and business on Windows pro 10.

Thanks David

pls paste your sample data and expected output for the better understanding

meanwhile you can go through different example... not related to you , however you can steal something from this.

Return blank cell.xlsx
ABCDEFG
2500NO5000
Sheet1
Cell Formulas
RangeFormula
B2B2=IF(ISNUMBER(G2),IF(ISNUMBER(A2),IF(G2>A2,"NO","YES"),""),"")

#### Peter_SSs

##### MrExcel MVP, Moderator

Just to be clear, is this True?
In column B of 'Paste From Compile' some cells contain numbers only and some cells contain text and some cells are blank

#### dave2017

##### New Member
Just to be clear, is this True?
In column B of 'Paste From Compile' some cells contain numbers only and some cells contain text and some cells are blank
Peter,

Yes that is correct.

Thanks David

#### Peter_SSs

##### MrExcel MVP, Moderator
Then try this

Excel Formula:
``````=IF(INDEX('Paste From Compile'!B:B,SMALL(IF('Paste From Compile'!\$M\$4:\$M\$10>0,ROW('Paste From Compile'!\$M\$4:\$M\$10),""),ROW('Site Sheet'!B3)))="","",
INDEX('Paste From Compile'!B:B,SMALL(IF('Paste From Compile'!\$M\$4:\$M\$10>0,ROW('Paste From Compile'!\$M\$4:\$M\$10),""),ROW('Site Sheet'!B3))))``````

#### dave2017

##### New Member
Just to be clear, is this True?
In column B of 'Paste From Compile' some cells contain numbers only and some cells contain text and some cells are blank
Paste from compile image shows the completed data and if you look at B6 it was left blank.

Site Sheet image shows the results and formulas, however B6 is showing a 0 but should be blank.

Hope this helps

David

#### Attachments

• Paste From Compile.JPG
49.4 KB · Views: 1
• Site Sheet.JPG
46.8 KB · Views: 2

Replies
4
Views
347
Replies
5
Views
235
Replies
3
Views
108
Replies
3
Views
190
Replies
7
Views
262

1,148,397
Messages
5,746,457
Members
424,020
Latest member
LongDoo

### 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.

### Which adblocker are you using?

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

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