Forum Discussion
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?
=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.
4 Replies
- here's a link to the google excel sheet for reference:
https://docs.google.com/spreadsheets/d/16n_G_j4X6nz1ORwvpm1eDwNApeMj6aKi3R8wOP0m1a0/edit?usp=sharing=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.
- OliverScheurich
the function works like magic, thanks alot
