I have a list that I need to remove the zero value items from via formula to create a new list (imagine an order form).
Because people will be using this second form without much excel knowledge, I need it 100% formula driven.
<colgroup><col><col><col></colgroup><tbody>
</tbody>
I need it to look like this (with a dollar symbol aligned to the left):
<tbody>
</tbody>
I have found this formula to return the non-zero values (when dragged down the column): {=IF(ROWS(F$2:F2)>COUNTIF(A$2:A$1201,">0"),"",INDEX(A$2:A$1201,SMALL(IF(A$2:A$1201>0,ROW(A$2:A$1201)-ROW(A$2)+1),ROWS(F$2:F2))))}
But I'm not sure how to concatenate the estimate or $ symbol into that formula.
Also, not sure how to extract the label in the cell beside the non-zero value.
Any ideas please?
Because people will be using this second form without much excel knowledge, I need it 100% formula driven.
Qty | Item | Estimate or Actual? |
- | Apples | Actual |
16 | Bananas | Estimate |
15 | Oranges | Actual |
Strawberries | Actual | |
10 | Blueberries | Estimate |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
I need it to look like this (with a dollar symbol aligned to the left):
Bananas | $ (E) 16 |
Oranges | $ (A) 15 |
Blueberries | $ (E) 10 |
<tbody>
</tbody>
I have found this formula to return the non-zero values (when dragged down the column): {=IF(ROWS(F$2:F2)>COUNTIF(A$2:A$1201,">0"),"",INDEX(A$2:A$1201,SMALL(IF(A$2:A$1201>0,ROW(A$2:A$1201)-ROW(A$2)+1),ROWS(F$2:F2))))}
But I'm not sure how to concatenate the estimate or $ symbol into that formula.
Also, not sure how to extract the label in the cell beside the non-zero value.
Any ideas please?