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

OFFSET and INDIRECT
 

I answer my own question. The problem was the Cell A didn't have data.

Warm Regards
Daniel


"daniel #" wrote in message
...
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