Inserting Rows with Formulas

**Christian411** · 11-23-2007, 09:39 AM

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

**NBVC** · 11-23-2007, 09:46 AM

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 *)

**Christian411** · 01-16-2008, 06:17 AM

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

Inserting Rows with Formulas

LinkBack

Thread Tools

Rate This Thread

Display

Inserting Rows with Formulas

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions