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

=OFFSET(Admin!$B$110,0,0,2+Time,3)

We would like to be able to take advantage of Excel’s ability to define dynamic named ranges using the OFFSET formula.  Thank you!

Hi,

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,

---

Nausherwan Aslam
Support Developer,
Aspose Sialkot Team
Contact Us

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!

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.

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

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.

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.

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

Hi, 

 

Thank you for considering Aspose.

 

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

 

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,

---

Nausherwan Aslam
Support Developer,
Aspose Sialkot Team
Contact Us

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.

Hi,

 

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,

---

Nausherwan Aslam
Support Developer,
Aspose Sialkot Team
Contact Us