You can build the address as a text string, then use INDIRECT() to make a reference from that string. This approach will easily break portability, and may also require caution when you insert/remove rows/columns in your spreadsheet, as references are not automatically updated inside string constants.
This may be a starting point for what you try to do:
Code: Select all
=SUM(INDIRECT("B"&$D$1&":B"&$D$2))
For many situations it is better to use the ADDRESS() function to create the starting address/range, then SUBSTITUTE() to insert the desired row numbers before conversion by INDIRECT(). This helps with portability when you have multiple sheets (tabs) in your file. (The syntax for sheet references is slightly different between different spreadsheet applications.) With this approach, consider having the generated address string in a separate cell (which can be hidden). Breaking your formula down to smaller steps makes debugging easier.
Note that using OFFSET(), perhaps also with named ranges, will normally be much easier to work with than INDIRECT constructs. (More compact, and also easier to understand in a year when you try to edit your spreadsheet formulas, having forgotten the details about what you were doing.) Trust me. I've gone there both ways.
