Trueblue862
Board Regular
- Joined
- May 24, 2020
- Messages
- 160
- Office Version
- 365
- Platform
- Windows
I have this sheet below which pulls data from another sheet to produce a profit/loss report. Is there any way to adjust the formulas to condense the report down and remove the blank cells?
Stock List 1.6.xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Profit Items | Profit per Item | Loss Items | Loss Per Item | ||
2 | Apples (pink lady) | $0.00 | ||||
3 | Avocado | -$100.00 | ||||
4 | bananas (cavendish) | $0.00 | ||||
5 | bananas (sugar) | -$75.00 | ||||
6 | beans | $0.00 | ||||
7 | beetroot | -$32.00 | ||||
Profit or Loss |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A2:A7 | A2 | =IF(INDEX('Order Sheet'!$N:$N,ROW())>=0,INDEX('Order Sheet'!$A:$A,ROW()),"") |
B2:B7 | B2 | =IF(INDEX('Order Sheet'!$N:$N,ROW())>=0,INDEX('Order Sheet'!$N:$N,ROW()),"") |
C2:C7 | C2 | =IF(INDEX('Order Sheet'!$N:$N,ROW())<0,INDEX('Order Sheet'!$A:$A,ROW()),"") |
D2:D7 | D2 | =IF(INDEX('Order Sheet'!$N:$N,ROW())<0,INDEX('Order Sheet'!$N:$N,ROW()),"") |