OFFSET and INDIRECT
I forgot to say the exception is in :
oValidDDLMetric.Add(Excel.XlDVType.xlValidateList,
Excel.XlDVAlertStyle.xlValidAlertStop, 1, source, Type.Missing);
regards
"daniel #" wrote in message
...
Hello Guys
i have a problem about dynamic ranges assigned to one cell.
On the attachment (sorry about that) we have two dropdowns dependents, I
am trying to do the same in C# code.
the source for the second dropdown (in the 8th row) in the example is :
=OFFSET(INDIRECT($B8),0,0,COUNTA(INDIRECT(B8&'Col' )),1)
I am trying the following code:
Excel.Range rngDDLMetric =
(Excel.Range)_xlsJudgmentData.Cells[currentRow, 2];
Excel.Validation oValidDDLMetric =
rngDDLMetric.Validation;
string source = "=OFFSET(INDIRECT($A" +
currentRow.ToString() + "),0,0,COUNTA(INDIRECT(A" + currentRow.ToString()
+ @"&""Col"")),1)";
oValidDDLMetric.Add(Excel.XlDVType.xlValidateList,
Excel.XlDVAlertStyle.xlValidAlertStop, 1, source, Type.Missing);
oValidDDLMetric.InCellDropdown = true;
oValidDDLMetric.ErrorTitle = "Error";
oValidDDLMetric.InputMessage = "Select an Metric";
oValidDDLMetric.ErrorMessage = "Invalid Option";
oValidDDLMetric.ShowError = true;
oValidDDLMetric.ShowInput = true;
But i get an exception.
Thanks in advance
Daniel
|