Excel Question

[Andrew Warren]

Rick Quatro wrote:

> I am using a formula in Excel to sum the values in a row:
> 
> =SUM(B2:F2)
> 
> Is there a way to use a generic row reference in a formula? For
> example, I don't know for sure at run time if the data and formula
> is going to end up in the second row. What I want is something
> like this
> 
> =SUM(B#:F#)
> 
> where the # sign would indicate the current row containing the formula.

Rick:

If I understand you correctly, you want the column letters to be remain as-is (B through F), but you want the row numbers to change if the formula moves to a different row.

Your formula's already written with relative cell references, so if you copy it to another cell, both the row and column references will change.  For example, if the formula is presently in cell A1, then copying it to cell E10 will change it to:

    =SUM(F11:J11)

To make the "B" and "F" column letters stay constant no matter where you copy the formula, precede them with "$" characters:

    =SUM($B2:$F2)

If you type that formula into cell A1 and then copy it to cell E10, it will change to:

    =SUM($B11:$F11)

I think that's what you want.

If you ever need both the row and column to remain unchanged when you copy the formula, precede both row and column with "$":

    =SUM($B$2:$F$2)

-Andrew