Cell returns 0 if blank.

[dave2017]

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

 

[Peter_SSs]

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]

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]

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]

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]

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
	A	B	C	D	E	F	G
2	500	NO					5000
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(ISNUMBER(G2),IF(ISNUMBER(A2),IF(G2>A2,"NO","YES"),""),"")

 

[Peter_SSs]

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]

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]

Then try this

=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]

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