Excel Formulas not always populating when inserting row

[Weezie62]

I have a user on Windows XP Professional running Office 2K Standard. She's having trouble with formulas in spreadsheets of a particular Excel workbook. She enters a value in the cells in the B column. Subsequent columns contain formulas that refer back to previous colums. For example, C2 = B2 + 89, C3 = B3 + 89, etc., and D2 = C2 - 58, D3 = C3 - 58, etc.. (Row 1 is a header row, column 1 is a header column.) The formulas in subsequent rows are populated by dragging the "fill handle" of a cell down the column.
The problem comes when new rows are inserted in the worksheet. The first row I insert takes the formulas with no problem. After that, rows that are inserted below the first row I inserted automatically populate with the appropriate formula. However, rows that I insert above the first row I inserted do not automatically populate with the appropriate formula, and I cannot figure out why.
Any help that you can give in understanding this quirk is GREATLY appreciated!!!!

 

[kjv1611]

Not totally sure on this one, but here are some things to consider:

1.) Are the formulas that are not copying correctly array formulas? I've had problems with array formulas not copying exactly the way I wanted on occasion.

2.) Do you have any $ signs in front of any of the cell/range addresses which would not change to what you want when moving them, or do you possibly need some to prevent some of the cell references from changing?

Those would be my first 2 guesses without delving in any deeper.

 

[Weezie62]

Thanks, kjv1611, for trying!!! (Was kjv written in 1611?)
However, they are not array formulas, nor are there any $ signs anywhere. After doing more research on the web, the only thing I could find was a macro:

http://www.mvps.org/dmcritchie/excel/insrtrow.htm#insrtrow

However, I didn't really want to mess with a macro or take any risks involved. So rather than having my user insert rows, I will have her copy and paste rows.
Thanks again for your help!

 

[kjv1611]

Any time. I'm glad you came to a sollution. In the mean time, that macro idea sounds very interesting. I have created several of my own for use in Excel, as well as in Access, and they can help make short work of many various mundane tasks!

And for your question on kjv - I'm assuming you're asking if the king james version of the Bible was written in 1611? If so, then, no. The Bible itself (as far as textual documents) were written in Hebrew, Coine [spelling] Greek, and some Aromaic. And, there was some work done on translating all of this over to an easily read (at that time) (what is considered now "Old English"), and then there were later "revisions" if you will, bringing it up a little more modern language usages. But, bare in mind, these changes were not different "translations", but rather just refinings of the original kjv 1611 translated texts. I don't recall all of the precise dates and such involved off the top of my head. I hope that is of some help, though not related to computers.

If you wanted more info from me in regards to the non-computer related question, feel free to contact me by going to www.parkwoodbaptistchurch.us, "contact us" page, and email from there. I am the web administrator, and as such, receive all emails. I just posted it this way, since we are not supposed to list email addresses in the Forums, so as to not attract marketing stuff.

 

[xlhelp]

I am curious. Can you give a little more explanation?

Is it like: you have five rows and you insert a row between 4 and 5, then insert row between 5&6 etc and everything is fine, but if you insert another row between 4&5, it's not.

Did I get it?

Member AAA - Abolish Abused Abbreviations

 

[Weezie62]

kjv1611--thanks for the info!

xlhelp--yes, that is exactly correct. If I insert a row between 4 & 5, the formula's there. If I insert a row between 8 & 9, the formula is there. And if I then insert a row between 6 & 7, the formula is still there even though it was above 8 & 9 because it was still below the very first insert. ANYTHING below the very first insert is fine. ANYTHING above the very first insert does not have the formulas. At least this is what I could tell from testing......

 

[xlhelp]

Hi, As long as all the columns have headings and there are NO blank rows or columns within the dataset, I cannot reproduce the behaviour.

If any of the conditions above are not met, then yes, same thing happens to me.

I take it you checked ? "Extend list formats and formulas" under Tools, Options, Edit.

Member AAA - Abolish Abused Abbreviations

 

[Weezie62]

xlhelp,
Yes, "Extend list formats and formulas" is checked. There was a blank column, so following reading your post, I removed the blank column. But I still have the same issue.

 

[xlhelp]

Weird as it may seem (since it shouldn't make a difference), I had to uncheck, click OK, check again to get the "Extend...." to work again.

Member AAA - Abolish Abused Abbreviations