Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there any way to automatically update formulas referring to my data when I delete or add a row to my data?

share|improve this question

migrated from Apr 19 '11 at 11:13

This question came from our site for professional and enthusiast programmers.

Can you please be more specific. If I enter a number in cell A2, and put 2*A2 in cell A1 and insert a row in between then cell A1 will read 2*A3, i.e. formulas are updated automatically. What else do you want? – Howard Apr 19 '11 at 5:45
@Howard : suppose you have 7 rows and you want the sum of column A, now first when you make the formula the range will go from A1 to A7 i.e. Sum(A1:A7) , now you add a new row , but the formula still is SUM(A1:A7), we want it to be Sum(A1:A8), that is the formula gets updated not the value of the formula. Regards – Arjang Apr 19 '11 at 12:16

Take a look at lists (XL 2003) or tables (XL 2007 & 2010). These add the type of functionality you're talking about and may work for you.

Also, you could add a named range to your worksheet with a relative reference that refers to the cell directly above. You could modify the example on this page under the heading "Using relative references."

share|improve this answer
named ranges are likely what should be used+1 – datatoo Apr 25 '11 at 16:56

You must log in to answer this question.