+ Reply to Thread
Results 1 to 3 of 3

Inserting Rows with Formulas

  1. #1
    Registered User
    Join Date
    11-23-2007
    Posts
    65

    Inserting Rows with Formulas

    Hi,

    I've got a spreadsheet with a lot of columns, each with VERY LONG formulas in them and a lot of conditional formatting. Once the spreadsheet is full of data I may need to insert rows in the middle of it from time to time. When I do this however, Excel only inserts blank cells - it doesn't insert the formulas. Is there an way of getting Excel to insert the formulas into the new cells (other than just highlighting the row above and copying the formulas down - I want to avoid this if possible to make using the sheet more user friendly)?

    Cheers,

    Chris

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    The only way I think you can do that without resorting to VBA is to convert your sheet to a list.

    The List feature is available in 2003 (and I assume 2007)...

    In 2003, you would select the entire occupied range and go to Data|List|Create List... the selected range should appear and if you have headers select the "my list has headers" checkbox. Click Ok.

    This will encapsulate your range within a blue bordered box.

    Now when you insert rows, the formulas should also copy to inserted lines and reset.

    Also, a feature of the List feature is that you can add new items to bottom of the list and the formulas will automatically copy down. (just insert over the last row marked by an asterisk *)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-23-2007
    Posts
    65
    I've still not managed to sort this one out. I did try to do this but I can't actually see the data/list/create list option (I'm working with Excel 2000).

    I'm not against using VBA if that's what it takes - I have some VBA doing other things in the spreadsheet.

    Again - can anyone offer any advice?

    Thanks in advance,

    Chris

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1