Dragging a Named Cell

Question

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.

Answer 1

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:

= tester * 2

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.

=INDEX(tester,1,COLUMNS($D:D))*2

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.