Using =SUM below a column filled with Smart Marker references

Aspose.Cells Product Family
1

Hello -

I have an Excel worksheet with Smart Markers that’s working fine, however I would like to use the =SUM function directly below a Smart Marker. Up to now, I’ve always included a blank row between the Smart Marker and the Sum, which works fine.

For example, let’s say in column D1 I have:
&=Detail.Amount

I would like to use the formula “=SUM(D1:D1)” in cell D2. However, after performing a merge (and inserting, say 5 rows of data), the SUM formula moves to the bottom of the column, but only shows the “sum” of the first cell.

Everything works fine if I move the =SUM formula to D3 and use the formula “=SUM(D1:D2)” where D2 is a blank cell.

I’m sure this has to do with Smart Markers inserting rows and that the SUM formula isn’t aware of the newly inserted data, right?

Any suggestions on how to avoid the blank row?

2

Hi,

Well, I think you may try to delete the blank row after filling the data to the marker(s) from the source into the worksheet.

E.g.,

workbookdesigner.Process(false);

int mdatarow = workbookdesigner.Workbook.Worksheets[0].Cells.MaxDataRow;

workbookdesigner.Workbook.Worksheets[0].Cells.DeleteRow(mdatarow-1);

Thank you.

3

Thanks, Amjad. That really isn’t an option because we have dozens of different templates, most of which were designed by other end-users, and I’ll never know ahead of time exactly where the totaling cell will exist.

Mike

4

Hi,

Ok, we will analyze the feasibility and lest effort involved (currently we are engaged in some other important tasks on hand) to enhance the smart markers according to your requirements.

Thanks for being patient!

5

Hi,

We have the same issue - is there a fix coming out for this soon :)?

Thanks!

6

Hi,

Thanks for considering Aspose.

We will soon enhance the smart markers to accept formula like "=SUM(A1:A1)" in the template excel files

Thank you.

7

Hi,

If you want use "=SUM(A1:A1)" to sum the value in the column ,please use dynamic formula

&=&=sum(A1:A{-1}) to replace the normal formula.