Inserting rows pushing Named Range back when using Smart Markers in .NET

Aspose.Cells Product Family
1

It seems like smart markers are causing named ranges in excel to be redefined or "pushed back" since rows/columns are inserted during runtime. We worked around this for inserted column by using "noadd" in the smart marker, but there isn't a "noadd" for rows.

We have an excel file comprised of a blank chart. The chart's data is linked the a named range "Chart1DataRange", defined by =OFFSET(Data!$B$2,0,0,COUNTA(Data!$B:$B)-1,1). This essentially is saying range Chart1DataRange is all of the cells in column B that have data starting at B2.

Currently "Chart1DataRange" range is blank. We will populate the data during runtime via smart marker. We will need the above formula because we don't know how many rows of data will be returned.

So at $B$2 we have a smart marker &=Chart1Data(horizontal,noadd) which we will use to add data 2 columns by 3 rows, in this example.

If you remember, the chart data is defined by range "Chart1DataRange" defined as starting at $B$2 for all cells of column B that have data. In this case, we have 3 rows of data inserted at $B$2. So, B2, B3, B4 are populated. However, what i found is that since the smart marker essentially inserts rows as it populates data, Excel actually changed the range of "Chart1DataRange" from starting at B2 to B5. So, Chart1DataRange is now OFFSET(Data!$B$6,0,0,COUNTA(Data!$B:$B)-1,1) instead of OFFSET(Data!$B$2,0,0,COUNTA(Data!$B:$B)-1,1). The Range definition got pushed down!

The end result is that my chart still doesn't show data because the range has been dynamically changed. I thought by using the "$" sign in excel cell ranges would be absolute. But when smart markers are used, it doesn't seem to be the case.

Any idea?

2

Hi,


It looks like “noadd” works for simple smart markers (vertically specified markers). For smart markers set with horizontal parameter might not work as expected as new columns are inserted accordingly.

By the way, if your data is filled vertically as you mentioned, i.e…,
“range “Chart1DataRange” defined as starting at $B$2 for all cells of column B that have data. In this case, we have 3 rows of data inserted at $B$2. So, B2, B3, B4 are populated. However, what i found is that since the smart marker essentially inserts rows as it populates data, Excel actually changed the range of “Chart1DataRange” from starting at B2 to B5. So, Chart1DataRange is now OFFSET(Data!$B$6,0,0,COUNTA(Data!$B:$B)-1,1) instead of OFFSET(Data!$B$2,0,0,COUNTA(Data!$B:$B)-1,1). The Range definition got pushed down!”
then why you are using your marker having “horizontal” parameter. Also, your specified marker i.e. “&=Chart1Data(horizontal,noadd)” is not valid as you may have to specify variables or tablename.fieldname formatting starting with “&=” chars. You should have your markers without “horizontal” parameter and with “noadd” attribute only.
We recommend you to kindly see the document on Smart Markers for your reference:
http://www.aspose.com/docs/display/cellsnet/Smart+Markers

If you still find the issue, kindly do create a sample (runnable) console application, zip it and post it here to show the issue on our end. Also please use hard coded values and create dynamic datatables/datasets so, we could run your project without any problem. Also, attach your input and output files. Moreover, attach your your expected output file. We will check your issue soon.


Thank you.