Validation, wich depends on other cell ist not working

Aspose.Cells Product Family
1

Hello Aspose-Team,

we have in Excel a column “F” which list depends on the choice of column “D”.

The following give the following result:

0: 5
1: 5.1
2: 5.2
3: 5.3
4: 5.4
5: 5.5
6: 5.6
7: 5.7
8: 5.8
9: 5.9
10: 5.10

but I expected:

0: 1.
1: 1.1
2: 1.1.1
…

Code-Example:
public void TestAsposeValidation()
{
var book = new Workbook(“C:\Projekte\BK8_MW.xlsx”);

Worksheet sheetBla = book.Worksheets["Erläuterungen der VNB"];
Cell cell1 = sheetBla.Cells["D8"];
Cell cell2 = sheetBla.Cells["F8"];
Cell cell3 = sheetBla.Cells["D9"];
Cell cell4 = sheetBla.Cells["F9"];

TestContext.WriteLine("____________________________________________________________________________________________");

cell1.Value = "Amtlicher Gemeindeschlüssel";
cell2.Value = 5;
cell3.Value = "Unternehmensdaten";
cell4.Value = "1.1.1";
Validation validation = cell4.GetValidation();

TestContext.WriteLine("Valide: " + cell4.GetValidationValue());
TestContext.WriteLine("Formula1: " + validation.Formula1);
TestContext.WriteLine("Type: " + validation.Type);

var listenEintraege = (object[])validation.Value1;
for (int k = 0; k <= listenEintraege.Length - 1; k++)
{
    TestContext.WriteLine(k + ": " + listenEintraege[k].ToString().Trim());
    if (k == 10) break;
}

TestContext.WriteLine("____________________________________________________________________________________________");
    }<a class="attachment" href="/uploads/default/65056">BK8_MW.7z</a> (328.4 KB)

Our Aspose.Cells: Version: 22.6.1.0

What can we do?

Thank’s for your help,
Matthias

2

@Matthias_Winzer,

Please zip and attach your sample Excel file, we will check your issue soon.

3

BK8_MW.7z (328.4 KB)

4

Thanks for the file.

I have tested your scenario/case using your template file and sample. I guess this is expected behavior.

Please note, this will give you the source values set for the validation (from the “Amtlicher Gemeindeschlüssel” worksheet) which starts from 5.x.

5

Hello Amjad,

thanks for your answere.

I think that is currently not the expected behavior.
You can see in the added Screenshot what I expected.
In the Excel-file is it working but in my testcase not.

In my code I took the validation from cell “F9” who depends on the cell “D9”.
I put in the cell “D9” the content “Unternehmensdaten” so I expect as a list for ell “F9” the values 1., 1.1 etc.

Thank’s for your help,
Matthias

Excel_Auswahl.png (27.8 KB)

6

@Matthias_Winzer,

Your desired values are sorted out dynamically in MS Excel application, I am not sure if it is documented in the source XML data/structures of the XLSX file. Could you provide details if there are some tags or other elements for the selected (ordered) values (of the data validation list) is saved in the source XML of the Excel file, so we could look into it further.

7

Hello Amjad,

I send you the same Excel but all the hidden pages are now visible.

The Formular in Cell “F7”, “F8” … is: =CHOOSE(C7,Liste!$E$2:$E$399,‘Amtlicher Gemeindeschlüssel’!B$6:B$1048576)
If I choose ‘Amtlicher Gemeindeschlüssel’ in cell “D7”, the list to choose from for Cell “F7” is ‘Amtlicher Gemeindeschlüssel’!B$6:B$1048576
If I choose ‘Unternehmensdaten’ in cell “D7”, the list to choose from for Cell “F7” is Liste!$E$2:$E$399

I hope that helps you to evaluate the problem.

Thank’s for your help,
Matthias

BK8_MW_enttarnt.7z (329.2 KB)

8

@Matthias_Winzer,
Because the list of the validation is calculated result of formula and is variable for different cell in the applied range, so please use validation.GetListValue(cellRowIndex, cellColumnIndex) instead of validation.Value1, and before getting the result of validation you need to re-calculate formulas of the workbook because you have changed some data:
…
cell4.Value = “1.1.1”;
book.CalculateFormula();
Validation validation = cell4.GetValidation();
object res = validation.GetListValue(8, 5); //for cell F9
…

9

Hi Mr. Johnson,

that seems to work.

Thank you very much
Matthias

10

@Matthias_Winzer,

Good to know that the suggested method works for your needs. Feel free to write us back if you have further queries or comments.