Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 stackoverflow.com 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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.