DataValidation without additional sheet

[SHAESEN2]

I would be very helpful to add validation to a column of a sheet, without referring to string values in another sheet. The code could look like:

dataValidation(wb, 1, col = 1, rows = 2:3, type = "list", value = c("", val1", "val2"))

[ycphs]

Please provide an xlsx file, which has this behaviour.

If you do it manually in Excel for type "list", you have to choose a range of to have the allowed values.

[SHAESEN2]

It can not be done in excel via 'data validation'. I was hoping it could be forced in via R code.

[ycphs]

If it can't be done in Excel, then it is not possible to create a valid xlsx-file.

[tentacles-from-outer-space]

This can definitely be done in Ecel, and in fact also in openxlsx using the following code:

dataValidation(wb, 1, col = 1, rows = 2:3, type = "list", value = "\",val1,val2\""))
# or
dataValidation(wb, 1, col = 1, rows = 2:3, type = "list", value = '",val1,val2"'))

Double quotes are essential for this to work.

So if one had vector of values:

v <- c("a", "b", "c")
dataValidation(......., value = paste0('"', paste0(v, collapse=","), '"'))

This could be added as an example on dataValidation help page.