I find myself having to reference a single cell (financial analysis: a certain expense as a % of revenue, for instance) so I'd like to know if there's a way I name a cell Revenue for the first year, then drag it across so it'll take care of all the cells.
If the cells happened to occupy the same columns in different sheets you could try this:
In Sheet1 create a named range in cells A1:E1 called "tester," and enter the values 1 to 5 in these cells. Now in Sheet2 in range A2:E2 enter the formula:
You'll see the results 2,4,6,8,10. This works because if a range is specified like this in a formula Excel assumes that it intersects the column of the cell with the formula, even if the range is in a different sheet.
If they're not in the same columns, in sheet2 you could enter something like this, assuming that your formulas start in Column D.
The Columns part of the formula for the cell in column D evaluates to 1, for the cell in column E to 2, etc. The Index part then counts over this many columns in your named range.