ZDEMO_EXCEL9 Custom Data Validation in cell A8 doesn't seem to work

[sandraros]

ZDEMO_EXCEL9, all values entered in A8 are rejected, whatever B8 contains a "-" or not:

Details of the data validation in cell A8 (Custom, formula IF(B8<>"";INDIRECT(LEFT(B8;SEARCH("-";B8;1)));EMPTY)):

[sandraros]

Concerned ABAP code:

demos/src/zdemo_excel9.prog.abap

Lines 130 to 138 in 2f89e63

	" Evaluation by Formula from issue #161
	lo_worksheet->set_cell( ip_row = 7 ip_column = 'A' ip_value = 'Validate if B8 contains a "-":' ).
	lo_worksheet->set_cell( ip_row = 8 ip_column = 'A' ip_value = 'Text' ).
	lo_worksheet->set_cell( ip_row = 8 ip_column = 'B' ip_value = '-' ).
	lo_data_validation = lo_worksheet->add_new_data_validation( ).
	lo_data_validation->type = zcl_excel_data_validation=>c_type_custom.
	lo_data_validation->formula1 = '"IF(B8<>"""";INDIRECT(LEFT(B8;SEARCH(""-"";B8;1)));EMPTY)"'.
	lo_data_validation->cell_row = 8.
	lo_data_validation->cell_column = 'A'.

Indeed, there are 3 issues:

1. First of all, a formula should start with =, which is currently not the case (starts with IF).
2. In the ABAP code, the formula should always contains commas instead of semicolons, which is currently not the case. For information, the formulas in ABAP have to contain commas, but Excel will display them with semicolons for European people (Windows/Office settings set to European number formats) and commas are kept for US people.
3. If I understand the intention (based on #159), it was to have the cell B8 contain a cell reference followed by - (the space was important) and the value in A8 should be equal to the value in that cell, e.g. if B8 contains A2 -, A8 value could be only abcdefghij. I propose to simplify that.

My proposal is to simplify the custom data validation formula, to validate that A8 starts with "T" if B8 contains a "-" and to not validate the input otherwise. Fix proposal:

• Change the text:

  lo_worksheet->set_cell(  ip_row = 7 ip_column = 'A' ip_value = 'Validate that A8 starts with "T" if B8 contains a "-":' ).

• Change the formula:

  lo_data_validation->formula1    = '=IF(ISERROR(SEARCH("-",B8)),TRUE,LEFT(A8,1)="T")'.