Cell returns 0 if blank.

dave2017

New Member
Joined
Jan 4, 2017
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,145
Office Version
  1. 365
Platform
  1. Windows
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’)

1621504802346.png
 

dave2017

New Member
Joined
Jan 4, 2017
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
51,145
Office Version
  1. 365
Platform
  1. Windows
That didn't answer my question

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
Joined
Jan 4, 2017
Messages
13
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

That didn't answer my question



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
Joined
Apr 10, 2021
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
51,145
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jan 4, 2017
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
51,145
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 4, 2017
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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
    Paste From Compile.JPG
    49.4 KB · Views: 1
  • Site Sheet.JPG
    Site Sheet.JPG
    46.8 KB · Views: 2

Forum statistics

Threads
1,144,278
Messages
5,723,467
Members
422,498
Latest member
KAT112014

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