This project has moved. For the latest updates, please go here.

formula to refrence a column in another sheet

May 27, 2014 at 4:27 AM
I am trying to add a formula to a cell.
I have two sheets 'cover' and 'comment'.

In comment Sheet column D as values as "P" or "C".
I want to reference a column D of sheet comments in cover sheet.

I am doing like this

// Formula ////=COUNTIF(Comment!D:D,"P")
var cellRange = ws.Cells[18, 6];
ExcelColumn column = workbook.Worksheets[1].Column(4);
cellRange.Formula = string.Format("COUNTIF(Comments!{0}:{1}," + @"""P)""", column, column);

But getting error - "Object reference not set to an instance of an object."

could some one help.
May 27, 2014 at 4:31 AM
corrected to

cellRange.Formula = string.Format("COUNTIF(Comments!{0}:{1},\"P\")", column, column);

But still getting same above error.
Jun 1, 2014 at 5:06 AM

when you use the variable column as a parameter string.Format I guess it will call the ToString() method on the instance which will return the typename. Or perhaps column is null when you use it in String.Format?

In the coming 4.0 version of EPPlus we have added the possibility to get the column letter given an index, but in 3.1 you have to handle that yourself.