This project has moved. For the latest updates, please go here.

Insert Rows & Formulas

Aug 9, 2011 at 11:15 AM

Hi There,

I've been playing around with your API and I've run into a wall.  I'm wondering if you could let me know if I'm doing something wrong?  I'm not exactly sure whats going on but I'm getting some strange behaviour when I insert rows above cells that have a forumla, or within a range used by a formula.

Simple example (all saved in a filed called Test.xlsx):

- I have random numbers  from B4 to B6

- in B9 I have =SUM(B4:B6)

- In D6 I have =B6

If I open the workbook and execute the following:

IXLRows newrows = Sheet.Row(4).InsertRowsBelow(2);
newrows.Style.Fill.BackgroundColor = XLColor.AirForceBlue; //just to highlight the rows
I end up with:
- In B11 the forumla is now =SUM(B6:B10)
- In D8 I end up with =B10

If I open the workbook and execute the following:

IXLRows newrows = Sheet.Row(1).InsertRowsBelow(2);
newrows.Style.Fill.BackgroundColor = XLColor.AirForceBlue; //just to highlight the rows

- The formula references are the same as above
- I end up with values in B4 to B8.  It looks like the values are shifted down by 2 rows, but B4 and B5 retain their original values
It seems like a fairly fundemental issue and the fact that nobody else has reported this makes me wonder if I'm doing something wrong.  Any help would be appreciated.
Thanks for all your hard work making this available to everyone!
Coordinator
Aug 9, 2011 at 2:52 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Aug 10, 2011 at 5:27 AM

Very nice catch! It's fixed on the latest check-in.

Thanks for the feedback.

Aug 10, 2011 at 3:27 PM

Thanks mate