Jenna_B
New Member
- Joined
- Sep 22, 2016
- Messages
- 28
- Office Version
- 365
- Platform
- Windows
I am using the following formula but I am getting messed up as I am trying to create two charts. In one chart I want it to show values with a High Quantity and in the other I want it to show values with a high cost.
=INDEX(Inv[Description],SMALL(IF((Inv[Days Since Last]>$J$2)*(Inv[Qty. on hand]>'Report - High Cost'!$K$2)*(Inv[Avg. Cost of Inventory On Hand]>'Report - High Cost'!$L$2),MATCH(ROW(Inv[Days Since Last]),ROW(Inv[Days Since Last])),""),ROW($A$1:$A$1)+3))
The issue I am having is when I filter the main table the value in my charts with the formula change to accommodate the filter. How do I get around that?
=INDEX(Inv[Description],SMALL(IF((Inv[Days Since Last]>$J$2)*(Inv[Qty. on hand]>'Report - High Cost'!$K$2)*(Inv[Avg. Cost of Inventory On Hand]>'Report - High Cost'!$L$2),MATCH(ROW(Inv[Days Since Last]),ROW(Inv[Days Since Last])),""),ROW($A$1:$A$1)+3))
The issue I am having is when I filter the main table the value in my charts with the formula change to accommodate the filter. How do I get around that?