Problem with formulas that reference another sheet


#1

Hi Laurence,

If I define 2 formulas, for example,

On Sheet1 =COUNT(A2:A5)

On Sheet2 =COUNT(Sheet1!A2:A5)

when I add/remove columns and rows on sheet 1 the formula on sheet 1 is being updated but the formula on sheet 2 is being left as it is.

Any ideas?

Thanks

David


#2

Hi David,

For performance consideration, now adding/deleting row/column only update reference for the first case but not for the second case.


#3

Laurence,

Is there any change you could add a flag so that, if it was set, it would update all the worksheets instead of just the one being modified?

Thanks,

David


#4

Hi David,

Thanks for your suggestion.

I am also thinking of such an option to allow you to update all the worksheets. You will get it within one week.


#5

Laurence,

Thanks.

David


#6
Laurence,
Have you managed to get any further with this?
Thanks,
David

#7

Please try this attached fix.

You can use the following sample code:

cells.InsertRows(2, 3, true); // insert 3 rows in the third row


#8

Hi Laurence,

That works for the inserted rows. Could you add the same to DeleteRow, DeleteColumn so that formulas are updated for these as well?

Thanks,

David


#9

Hi David,

I will add them in a few days.


#10

Hi Laurence,

Have you got any further with this?

Thanks,

David


#11

Hi David,

You will get them in one or two days. Thanks for your patience.


#12

Hello Laurence,

would you do the same for InsertRange for that matter? Smile [:)]

However, the InsertRange "NullReferenceException" bug has higher priority for me.

Greetings - Richie


#13

Hi Richie,

Sure I will add it for InsertRange method.

But I am confused with "InsertRange NullRefreenceException" bug. Have you reported to me before? If yes, I think it's solved.

If you still find the problem, please post you sample code and file here. Thank you.


#14

Hi Laurence,

yes, the NullReferenceException still exists.

Check out the Test Application sent in this thread http://www.aspose.com/Community/forums/thread/54241.aspx together with the Aspose.Cells-Version that you recently implemented for me that denies BIFF7 records in the thread http://www.aspose.com/Community/forums/thread/54244.aspx.

I converted the Before.xls from the test application into a BIFF8/BIFF8X-only-format.

Greetings - Richie


#15

Laurence,

Have you managed to get any further with this?

Thanks,

David


#16

Hi David,

Thanks for considering Aspose,

Well, Mr. Laurence is sick, hopefully he will get well soon and will be back in a couple of days.

Regards,

Amjad Sahi

Aspose Nanjing Team


#17

Amjad,

Thanks for the response.

David


#18

Hi David,

Please try this attached fix. Now DeleteRows and InsertRange also support to update this reference.


#19

Hi Laurence,

Thanks for the fix - it works great. Are you going to be able to add the same functionality to the DeleteColumn function as well?

Thanks,

David


#20

Hi David,

Please try this attached fix. DeleteColumn method also supports this feature now.