update precedents of excel formulas

Is there any way to ask excel to update the precedents of a formula if we provide all details like....

suppose in cell D1 = "=SUM(B1:B20)"

now after data got exported then we know that data is B5:B50

then how 开发者_如何转开发can we ask excel to update the formula in cell D1 like "=Sum(B5:B50)

or we will have to string replacements.

You could assign a name to the B1:B20 range: highlight B1:B20, the click on the name box (to the left of the formula bar) and type a name. You can then use that name in your SUM formula. When the data moves, update the location of the range on the Excel 2003 Insert menu, at Range > Define.

Alternatively, if the top-left cell stayed in the same place after the export (i.e. in cell B1), you could create an Excel list to represent the data: highlight B1:B20 and right-click on it, then click Create List. If you add rows to the bottom of a list, Excel automatically updates any formulae that refer to it.