I am using excel 2000
I have the following formula,
[HTML]=CONCATENATE("Consultants -"," ",(SUBTOTAL(3,G2:G13))-SUMPRODUCT(SUBTOTAL(3,OFFSET(G2:G13,ROW(G2:G13)-ROW(G2),0,1))*(G2:G13="Vacant"))-SUMPRODUCT(SUBTOTAL(3,OFFSET(G2:G13,ROW(G2:G13)-ROW(G2),0,1))*(G2:G13="Proposed"))," ","Vacancies -"," ",SUMPRODUCT(SUBTOTAL(3,OFFSET(G2:G13,ROW(G2:G13)-ROW(G2),0,1))*(G2:G13="Vacant"))," ","Proposed -"," ",SUMPRODUCT(SUBTOTAL(3,OFFSET(G2:G13,ROW(G2:G13)-ROW(G2),0,1))*(G2:G13="Proposed")))/HTML]
which counts various attributes in the column above, I want to be able to delete rows and the formula still work, however when I delete the first or last row of data in the range I get #REF!, see below
[HTML]=CONCATENATE("Consultants -"," ",(SUBTOTAL(3,G2:G12))-SUMPRODUCT(SUBTOTAL(3,OFFSET(G2:G12,ROW(G2:G12)-ROW(#REF!),0,1))*(G2:G12="Vacant"))-SUMPRODUCT(SUBTOTAL(3,OFFSET(G2:G12,ROW(G2:G12)-ROW(#REF!),0,1))*(G2:G12="Proposed"))," ","Vacancies -"," ",SUMPRODUCT(SUBTOTAL(3,OFFSET(G2:G12,ROW(G2:G12)-ROW(#REF!),0,1))*(G2:G12="Vacant"))," ","Proposed -"," ",SUMPRODUCT(SUBTOTAL(3,OFFSET(G2:G12,ROW(G2:G12)-ROW(#REF!),0,1))*(G2:G12="Proposed")))/HTML]
What do I need to change to stop this happening
Bookmarks