SOLVED

# Skip empty cells when preforming a function

Copper Contributor

# Skip empty cells when preforming a function

I only want to display in the table on the right the values which have values adjacent to their SKUs.

meaning that I don't want SKU 3, 5, 7, 8 and 10 (any SKU that doesn't have a quantity value beside it).

I want the table on the right to only include the SKUs which have quantity values and omit any row having a zero in "batch size" column.

any help?

4 Replies

# Re: Skip empty cells when preforming a function

best response confirmed by AhmedMedhat66 (Copper Contributor)
Solution

# Re: Skip empty cells when preforming a function

``=IFERROR(INDEX(A\$2:A\$11,SMALL(IF(\$B\$2:\$B\$11<>"",ROW(\$B\$2:\$B\$11)-1),ROW(\$B1))),"")``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell E2 and filled across range E2:F10 in the example.

If you work with Office 365 you can apply the FILTER function.

# Re: Skip empty cells when preforming a function

@OliverScheurich
the function works like magic, thanks alot

# Re: Skip empty cells when preforming a function

Practically the same

``=IFERROR( INDEX( \$A\$2:\$A\$11, AGGREGATE(15,6, 1/(\$B\$2:\$B\$11<>"")*(ROW(\$A\$2:\$A\$11)-ROW(\$A\$1)), ROW()-ROW(\$E\$1)) ), "" )``
1 best response

Accepted Solutions
best response confirmed by AhmedMedhat66 (Copper Contributor)
Solution

# Re: Skip empty cells when preforming a function

``=IFERROR(INDEX(A\$2:A\$11,SMALL(IF(\$B\$2:\$B\$11<>"",ROW(\$B\$2:\$B\$11)-1),ROW(\$B1))),"")``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell E2 and filled across range E2:F10 in the example.

If you work with Office 365 you can apply the FILTER function.