Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Inserting rows and keeping formulas (Excel 2000 SP3)

    I use Excel every day, and thought I was reasonably proficient....

    ....however!

    I need to insert some rows within a large spreadsheet of data, and expected that in my "new" rows, any columns containing formulas would auto-fill with the formula (and all the formulas on the original rows below would adjust to take account of their new position in the table).

    But for some reason, when I insert rows in this particular sheet, the whole of the new row, including the formula colums, is coming out blank.

    What am I missing?

    Neil

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting rows and keeping formulas (Excel 2000 SP3)

    It would be rather presumptuous - maybe you only wanted to insert a row for formatting purposes, or to insert subheadings, or something like that.

    You may also find that Excel didn't adjust the formulas in the row below the inserted one the way you intended.

    You can create a macro that will insert a row and fill down formulas.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Inserting rows and keeping formulas (Excel 2000 SP3)

    Sorry, Hans. I'm not really with you there!

    It is just that Excel doesn't seem to be behaving in the way I would expect (or have always experienced) on this occasion.

    Let me give you an example in simple terms:-

    I have an existing worksheet with 3 columns (A, B, C) and 4 rows. (1-4). Column A and B contain numbers (data). Column C contains a formula (=A*[img]/forums/images/smilies/cool.gif[/img].

    I want to insert an extra row between rows 2 and 3, so I right click on row 3, and select insert.

    I would expect that the formula in Column C would automatically appear on my new row (and those on the re-positioned Rows 3 & 4 be adjusted, to refect their new positions - I'm sure that is what always happens - isn't it?!

    Actually - I've just tested this, and no it isn't what happens. Now I'm really confused!

    I am, however, quite certain that I have inserted numerous rows into numerous spreadsheets and the formulas have auto-filled.

    What is going on?!!

    Neil

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting rows and keeping formulas (Excel 2000 SP3)

    As far as I know, Excel has never autofilled formulas in an inserted row. Perhaps you were thinking of the option to automatically extend formatting and formulas in a list - see XL2000: How Auto Extend List Behavior Works.

  5. #5
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting rows and keeping formulas (Excel 200

    If you select Row 3, copy it, then use the Insert Rows icon (see below), the formula will be copied, but so will any other data in the row...
    Attached Images Attached Images
    • File Type: gif x.gif (1.7 KB, 3 views)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •