Formula OFFSET with dynamic name ranges

Aspose.Cells Product Family
1

Hi, I found a bug using the OFFSET function as the following:

<span style=“font-size: 11pt; font-family: “Calibri”,“sans-serif”;”>=OFFSET(Admin!$B$110,0,0,2+Time,3)


<span style=“font-size: 11pt; font-family: “Calibri”,“sans-serif”;”>We would like to be
able to take advantage of Excel’s ability to define dynamic named ranges using
the OFFSET formula. Thank you!

2

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. If you still face any problem, please share your sample code and template file. We will check it soon.

Thank You & Best Regards,

3

Hi, thanks for the quick reply. unfortunately, version 4.8.2.5 doesn’t work on our case. I have attached a sample in excel.

in the sample, name range “Annual_Cash_Flows” is being referred to formula “=OFFSET(Sheet1!$A$1,0,0,Time+2,3)” instead of a static range. You can see the range change by change the Time (Variable) dropdown to a different item.

what we want to achieve:
on the web, if we map the Time value to 2, we need aspose to calculate the range of “Annual_Cash_Flows” correctly and returns the correct range

Thank you!

4

Hi,

Thanks for providing us the template file with some details.

Do you mean you want to get the range by Name.GetRange()
method when you change the Time (Variable) drop down to a different item?


Thank you.


5

Yes. After we changed the Time (variable) drop down, we will call calculateFormula and expect Annual_Cash_Flows to change its range when we call the GetRange() function.

6

Hi,
OK, we will support to get the range if the Name’s RefersTo is like "=Offset(…)"
We will figure out the issue soon.

We have added it into our issue tracking system with an issue id: CELLSNET-14269.

Thank you.

7

Hi, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We have supported your desired feature.

<?xml:namespace prefix = u1 />

Workbook workbook = new Workbook();

workbook.Open(@"F:\FileTemp\AsposeRequestSample.xls");

Cells cells = workbook.Worksheets[0].Cells;

for (int i = 1; i <= 5; i++)

{

cells["B10"].PutValue(i);

Range range = workbook.Worksheets.Names[0].GetRange();

Console.WriteLine(range.RowCount);

}

Thank You & Best Regards,

8

Thanks for the revision. I’m hitting an error when I tried to convert the range to DataTable

code snippet:
_workbook.Worksheets.Names[“AnnualCashFlow”].GetRange().ExportDataTable()

end code

I received the “Invalid end column index.” error message. I evaluate the ColumnsCount, it’s 65430 which my table should only have 5 columns.

9

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Well, I checked your issue with the template file you attached in the previous post and it works fine. Please share the sample code and template file (if different from previous file) to reproduce the issue. We will check it soon.

Thank You & Best Regards,