Using GridDataBoundGrid for cross sheet formula references

[Najmeh Joze-khajavi]

I have studied the two examples shipped with syncfusion regarding cross sheet formula references ( "NameRangesForFormulas" and "RetirementSimulation"). I am wonder if I can replace GridControls in these examples with GridDataBoundGrid Control and register them the same way in these examples?

If yes, how about combining both? To explain myself, consider to have one GridDataBoundGrid and 5 GridControls and I want to register all of the grids as sheets using CreateSheetFamilyID and RegisterGridAsSheet.

In my application I followed NameRangesForFormulas example very closely but I can''t reference from one sheet to other. I believe because my sheets are combination of GridControl and GridDataBoundGrid controls. When I use the formula "=Worksheet1!A1" I got zero in cell or when I use the formula "=Worksheet1!A1 + Worksheet1!B1" I got "Invalid characters following an operator" error. Of course A1 and B1 in Worksheet1 has some values.

[Administrator]

I think you probably should be able to get this working. Here is a little sample.
http://www.syncfusion.com/Support/user/uploads/GC_GDBG_Formulas_d6eb23e5.zip

In your code, make sure you save the value of the sheetFamilyId and do not repeatedly create a new one by repeatedly calling the create method.

int sheetFamilyID = GridFormulaEngine.CreateSheetFamilyID();
GridFormulaEngine.RegisterGridAsSheet("sheet1", this.gridControl1.Model, sheetFamilyID);
GridFormulaEngine.RegisterGridAsSheet("sheet2", this.gridControl2.Model, sheetFamilyID);
GridFormulaEngine.RegisterGridAsSheet("sheet3", this.gridControl3.Model, sheetFamilyID);
GridFormulaEngine.RegisterGridAsSheet("sheet4", this.gridControl4.Model, sheetFamilyID);
GridFormulaEngine.RegisterGridAsSheet("sheet5", this.gridDataBoundGrid1.Model, sheetFamilyID);

[Najmeh Joze-khajavi]

Thanks for sample but I am not able to run it. I got the following error:
C:\GC_GDBG_Formulas\licenses.licx Could not transform licenses file ''licenses.licx'' into a binary resource. (1) : error LC0003 : Unabled to resolve type ''Syncfusion.Core.Licensing.LicensedComponent, Syncfusion.Core''

Can you tell me how to fix it? We are using Syncfusion version 3.0.1.6. Is this a problem?

I tried to follow your sample. I think you want to set some data in GridDataBoundGrid control. I do the same thing but I don''t see any data in my GridDataBoundGrid control.

I tried to set some data in one of Grid controls and then use the cross sheet reference it still doesn''t work. It returns zero in the proper cell. It seems the registration is not correct.

[Administrator]

Remove the license.licx file (Show All Files in the solution explorer window. Then right-click and delete teh licx file.).

Then I think you should be able to run the project using 3.0.1.0.

[Najmeh Joze-khajavi]

Removing licenses.licx helps me to build the project. Thanks.

I had a couple problem that I fixed those. Now I am able to do cross sheet references between sheets that they are GridControl but not between GridDataBoundGrid and GridControl. In the sheet that is GridDataBoundGrid I even can''t say "=A1". It returns "=A1 is not a valid value for int32". I believe this is because I didn''t correctly set the cell type to "FormulaCell" in my GridDataBoundGrid. I did it the same way I did for GridControl but searching forums I found out I need to use GridQueryCellInfo and formulaTags. I still not sure the GridDataBoundGrid do the job for us. Our application are heavily dependent to formula. I want all my cells in GridDataBoundGrid to be FormulaCell type. We set up some formula but our client may also add their formula to GridDataBoundGrid. Therefore they need to be able to enter their formula in run time. Am I wrong to choice GridDataBoundGrid over GridControl? For example in your sample that you sent me I want to say in sheet5 (GBDG) "=sheet1!A1+sheet1!A2 when
sheet1!A1 has value 2 and
sheet1!A2 has value 3.
I can''t do this. Your advise would be helpful.

[Administrator]

>>I want all my cells in GridDataBoundGrid to be FormulaCell type

You can set:

grid.TableStyle.CellType = "FormulaCell";

and every cell in the grid will be able to hold a formula.

But if some of you datacolumns in the Datatable are of some type other than string (like double or float), then that column cannot normally hold a formula as the formula string is the cellvalue, and that must be a what is stored in the DataTable, something other than a string.

But a bigger requirement is that you must maintain a datastore to cache teh formulatag objects needed for formula cells. In a GridDataBoundGrid, the only datstore the grid knows about is teh datasource. And that datasource only has a single value (what is mapped to the CellValue). To maintain formulas, the grid needs more than just the cell value. Here is a KB link that discusses this. http://www.syncfusion.com/support/kb/grid/Default.aspx?ToDo=view&questId=104

In addition,