Thread: Problem with formula returning #REF! if I delete the first or last row of data

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

Presumably by first row you mean row 2 ?

You can replace each instance of:

ROW(G2)

with

ROW(INDEX(G2:G13,1))

Thanks that solved it

In future pa attention to the Forum Rules

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save