Dynamic ranges for cells

[ub92]

I think I have a problem that either has an easy solution or no solution (at least without going to VBA).

I have a bunch of formulas that I need repeated on multiple worksheets.

The formulas are different and they are variable in range. However, several formulas use the same range. The range changes depending on the worksheet.

What I would like to do is put a number in a cell of a worksheet such that the formula will use that number as the cell range.

For example, say I wanted to sum the entries in cells A2:A44. However, depending on the worksheet, the range may go to A54 or A61. Is there a way to write the formula such that it can draw range from cell entries (static) on that worksheet?

If you have any questions, please let me know. I hope I have explained this properly.

Thank you in advance,

Don

[davesexcel]

You can use a dynamic named range
Check this out
http://support.microsoft.com/kb/830287

[Ace_XL]

These are two options based on your example

Cell B1 holds the number (44 in your example) of Rows you want to sum in Column A starting from Cell A2

=SUMPRODUCT(A2:A1000,--(ROW(A2:A1000)<=B1))

or

=SUM(INDIRECT("A2:A"&B1))