I renamed cells, now formulas wont copy correctly

**ExcelRook** · 10-09-2008, 06:24 PM

Forgive me if this has been posted already.

Example: I renamed cell A1 to Apple, A2 to Banana, A3 to Chair.

Down in cell a50 I was using simple formulas a1+a2*a3. then copying the formula into column b50, c50,... Now when I use the formula SUM(Apple+Banana*Chair) and try to copy it into cell b50, It doesnt change the formula to SUM(B1+B2*B3).

Is there an easy way I solve this issue?

**Paul** · 10-09-2008, 10:17 PM

Cell names (named ranges) do not update as you move across columns/rows. In A50 you can use

=Apple+Banana*Chair

There is no need for the SUM function. Then in B50 use:

=OFFSET(Apple,0,COLUMN()-1)+OFFSET(Banana,0,COLUMN()-1)*OFFSET(Chair,0,COLUMN()-1)

Then fill that to the right for C50, D50, E50, etc..

**ExcelRook** · 10-09-2008, 10:33 PM

Thank you for the help, unfortunately that wont make it any easier for me.

I need to make it a one step process if possible. Going through to each B cell and entering a new formula would be too time consuming. I would be better off unnaming cells a1-a3. Is there any other way to correct it?

**sglife** · 10-10-2008, 03:50 AM

Unname, or rename the range to entire row
Apple = 1:1
Banan = 2:2

**Cheeky Charlie** · 10-10-2008, 04:27 AM

Small tip - naming cells is useful for fixing constants into formulae.

Not such a good idea if you don't want to fix the constants...

**ExcelRook** · 10-10-2008, 11:45 AM

Im going to mark this solved, as I went ahead and unnamed the cells. Im going to try and attack the situation in a different way to make entering the formulas as easy as possible.