Include type = "custom" for wb_add_data_validation()

[johngodlee]

In Microsoft Excel for Mac (Version 16.93.1 (25011917)) it is possible to write a Data Validation rule with type "Custom" and supplying a formula, such that an error is raised when the formula does not evaluate to TRUE.

Is it possible to specify a similar rule in openxlsx2 using wb_add_data_validation(), for example by setting type = "custom" and supplying the formula through an additional argument?

My immediate use case is:

I have a column of strings in column A:

A
B
C
AB
ABC
G
AG

a Data Validation formula:

=SUMPRODUCT(--ISNUMBER(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1), C$1:C$3, 0))) = LEN(A1)

and a lookup table in column C:

A
B
C

With this rule, rows 1,2,3,4,5 produce no error. Rows 6,7 produce an error, as they contain a forbidden character.

[JanMarvin]

Hi @johngodlee ,

I'm not that big in data validation, but the following should be what you want (we actually support type custom, but only in a few examples hidden in our test files). Be aware that we do not check the formula for valid XML, hence you have to escape & as &.

library(openxlsx2)

vals <- c("A", "B", "C", "AB", "ABC", "G", "AG")

lups <- c("A", "B", "C")

wb <- wb_workbook()$add_worksheet()$
  add_data(x = vals, dims = wb_dims(x = vals))$
  add_data(x = lups, dims = wb_dims(x = lups, from_col = "C"))$
  add_data_validation(type = "custom", dims = "A1:A7", 
                      value = 'SUMPRODUCT(--ISNUMBER(MATCH(MID(A1,ROW(INDIRECT("1:"&amp;LEN(A1))),1), C$1:C$3, 0))) = LEN(A1)')

if (interactive()) wb$open()

You can get the correctly escaped formula with something like this (the node is actually called formula1, it has nothing to do with the race car series):

xml_node_create("formula1", xml_children = 'SUMPRODUCT(--ISNUMBER(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1), C$1:C$3, 0))) = LEN(A1)', escapes = TRUE)

If you are more familiar with the topic, please feel free to extend our documentation. I have no real need for data validation and hence the man pages and the short chapter is the best I came up with.

[johngodlee]

Thanks very much, this works well for my use case.

I find it confusing when to use, for example, A1, vs. sheet1[[#This Row],[col]], when writing these validation functions, but I think that is probably more my basic understanding of Excel at fault.

[JanMarvin]

Perfect! Sometimes spreadsheets require these thinking around a corner ...