formula OFFSET with dynamic name ranges

Aspose.com » Public Forums for Customers an... » Aspose.Cells Product Family » Re: formula OFFSET with dynamic name ranges

Last post 02-12-2010, 4:07 AM by nausherwan.aslam. 8 replies.


	Sort Posts: Previous Next

02-08-2010, 5:43 PM 221322

kunlichen
Joined on 05-19-2009
Posts 26
Issues: CELLSNET-14269 (Unknown)

formula OFFSET with dynamic name ranges

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!

02-09-2010, 1:10 AM 221356 in reply to 221322

nausherwan.aslam
Joined on 09-14-2008
Posts 5,083

Re: formula OFFSET with dynamic name ranges

Attachment: Present (inaccessible)

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

02-09-2010, 1:53 PM 221500 in reply to 221356

kunlichen
Joined on 05-19-2009
Posts 26

Re: formula OFFSET with dynamic name ranges

Attachment: Present (inaccessible)

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!

02-10-2010, 12:19 AM 221564 in reply to 221500

Amjad Sahi
Joined on 08-30-2006
Posts 18,411

Re: formula OFFSET with dynamic name ranges

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

02-10-2010, 12:35 AM 221566 in reply to 221564

kunlichen
Joined on 05-19-2009
Posts 26

Re: formula OFFSET with dynamic name ranges

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.

02-10-2010, 12:47 AM 221570 in reply to 221566

Amjad Sahi
Joined on 08-30-2006
Posts 18,411

Re: formula OFFSET with dynamic name ranges

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

02-11-2010, 1:26 AM 221815 in reply to 221566

nausherwan.aslam
Joined on 09-14-2008
Posts 5,083

Re: formula OFFSET with dynamic name ranges

Attachment: Present (inaccessible)

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

02-11-2010, 6:37 PM 222067 in reply to 221815

kunlichen
Joined on 05-19-2009
Posts 26

Re: formula OFFSET with dynamic name ranges

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.

02-12-2010, 4:07 AM 222114 in reply to 222067

nausherwan.aslam
Joined on 09-14-2008
Posts 5,083

Re: formula OFFSET with dynamic name ranges

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