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]

Paste from compile image shows ...

But it doesn't show any cells that contain numbers only. Are you sure there are some such cells in col B in that sheet?
If not, the formula I suggested above can be made simpler.

 

[dave2017]

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))))

Peter

That worked perfect thanks, now it shows an error in column b from b8:b16 after I copied the formula down.

 

[Peter_SSs]

Sorry, I reduced the range size (Changed $100 to $10) in your original formula to make it easier for checking & forgot to reinstate the original value. Here I have changed it back. Does that help?

=IF(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)))="","",
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))))

 

[dave2017]

Sorry, I reduced the range size (Changed $100 to $10) in your original formula to make it easier for checking & forgot to reinstate the original value. Here I have changed it back. Does that help?

=IF(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)))="","",
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))))

Hi Peter,

Still shows the #NUM! Error, possibly because there is no data on the paste from compile sheet from column B row 9 onwards ?

 

[Peter_SSs]

=IFERROR(IF(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)))="","",
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)))),"")

 

[dave2017]

=IFERROR(IF(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)))="","",
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)))),"")

Hi Peter,

That's it ! Perfect, you are a superstar. Thanks for the solution and time you have provided.

David

 

[Peter_SSs]

You're welcome.

For the future, usually faster to the correct answer if we can have the small sample dummy data with XL2BB so I suggest that you investigate that.