Extend Formula not working

B

BK

Using Excel 2003 and have put a check mark next to the box to "extend data
range formats and formulas." I then began entering data and created a
formula in Column E that calculates a running balance based on whatever is
entered in the row. When I first built the worksheet, I copied the formula
that I entered in Row 3 down four more rows and filled in those four
additional rows with current data.

Now that I have gone back to that worksheet to enter new data in the next
row, but the formula does not seem to automatically extend to the new row.
I entered data in the row and whether I tab to Column E or arrow over to
Column E, my new balance does not appear. I thought the formula was
supposed to automatically extend to the new row of data.

What step did I miss?
 
G

Gord Dibben

This works.

In E1 enter =SUM(A1:D1)

Copy down at least 3 rows so you have 4 rows in total with the formula.

Enter data in A5:D5 and the formula in E1 will "extend"

Data must be entered in all 4 columns A:D in order for the formula to extend.


Gord Dibben MS Excel MVP
 
S

Shane Devenshire

Hi BK,

FYI - from the help system:

Extend list formats and formulas Select to automatically format new items
added to the end of a list to match the format of the rest of the list.
Formulas that are repeated in every row are also copied. To be extended,
formats and formulas must appear in at least three of the five last rows
preceding the new row.

Cheers,
Shane Devenshire
Microsoft Excel MVP
 
B

BK

I read that same information in the help, so I copied my original formula
down four rows when I first entered it. Now today when I added a new row
of data, that formula in Column E did not calculate anything at the end of
the new row of information.
 
B

BK

This is a simple check register for my children. Column A is a date.
Column B is the payee name. Column C is the description of what the
payment is for. Column D is the amount. Column E is the running
total of the balance. So in Row 3 I entered =E2-D2. I copied that
formula down four rows to be able to enter current information. When
we went back in today to enter a new expenditure, the running balance
cell in Column E did not update.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Second Posting for Extending Formula 7
is Retrieving a Cell address from a cell containing a SUMPRODUCTformula a problem ? 5
Figuring out a lengthy formula. Probably quite simple... 3
Filter by typed value 5
Excel VBA 1
Table no longer copies formulas to new rows 9
Return value in an table with variable columns and rows 2
Extend formula hotkeys? 4

Top