B
Ben
Hi
I have the following formula for summing a filtered list based on certain
criteria....
=SUMPRODUCT((Date7)*(SUBTOTAL(3,OFFSET(Resource_Group,
ROW(Resource_Group)-MIN(ROW(Resource_Group)),,1)))*(Resource_Group="On-Site
Engineers")*(Work_Type<>"Previous
Demand")*(Work_Type<>"Booked")*(Work_Type<>"Allocated")*( LEFT(Work_Type, 7)
<>"Actual ")*( LEFT(Work_Type, 9) <>"Capacity "))
My problem is, I have *lots* of similar formulas in my spreadsheet and it is
incredibly slow. Is there a way of speeding this up? I have had a go at
writing an XLL add-in, but the documentation for doing so is poor. For
example, I can't find a way of finding if a particular cell is filtered or
not. I'm not sure doing it this way would speed it up anyway.
Any other good ideas would be a great help.
TIA
Ben
I have the following formula for summing a filtered list based on certain
criteria....
=SUMPRODUCT((Date7)*(SUBTOTAL(3,OFFSET(Resource_Group,
ROW(Resource_Group)-MIN(ROW(Resource_Group)),,1)))*(Resource_Group="On-Site
Engineers")*(Work_Type<>"Previous
Demand")*(Work_Type<>"Booked")*(Work_Type<>"Allocated")*( LEFT(Work_Type, 7)
<>"Actual ")*( LEFT(Work_Type, 9) <>"Capacity "))
My problem is, I have *lots* of similar formulas in my spreadsheet and it is
incredibly slow. Is there a way of speeding this up? I have had a go at
writing an XLL add-in, but the documentation for doing so is poor. For
example, I can't find a way of finding if a particular cell is filtered or
not. I'm not sure doing it this way would speed it up anyway.
Any other good ideas would be a great help.
TIA
Ben