Is it possible to define formula1 in dataValidation when the type is list by giving a list of values rather than specifying a sheet range? Like

[JanMarvin]

Is it possible to define formula1 in dataValidation when the type is list by giving a list of values rather than specifying a sheet range? Like

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2")

writeDataTable(wb, sheet = 1, x = iris[1:30, ])
writeData(wb, sheet = 2, x = sample(iris$Sepal.Length, 10))

dataValidation(wb, 1, col = 1, rows = 2:31, type = "list", value = "'option1,option2")

When I try to do this, I get an error in the resulting excel file.

Originally posted by @IRorpa in https://github.com/ycphs/openxlsx/issues/342#issuecomment-1256404432

[JanMarvin]

@IRorpa I've moved this to a new issue. I can reproduce your observation, but I do not know what you are trying to achieve. Are option1 and option2 named regions? Other than that, the data validation is converted to some xml string. This string must be interpretable by spreadsheet software.

Something like this:

<ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{05C60535-1F16-4fd2-B633-F4F36F0B64E0}">
 <x14:dataValidations xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
  <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1">
   <x14:formula1>
    <xm:f>option1, option2</xm:f>
   </x14:formula1>
   <xm:sqref>A2:A31</xm:sqref>
  </x14:dataValidation>
 </x14:dataValidations>
</ext>

The above is created by openxlsx2 and there is no error, but still no clue what's supposed to happen :)

library(openxlsx2)

wb <- wb_workbook()$
  add_worksheet()$add_data_table(x = iris[1:30,])$
  add_worksheet()$add_data(sheet = 2, x = sample(iris$Sepal.Length, 10))$
  add_data_validation(sheet = 1, col = 1, rows = 2:31, type = "list", value = "option1, option2")

wb$open()

[IRorpa]

Thanks for looking into it. I tried installing openxlsx2 and i got a message saying it's not available for my version of R. Do you know where I can get it for R version 4.2.1?

[tentacles-from-outer-space]

It is posible. I done it here: https://github.com/ycphs/openxlsx/issues/16#issuecomment-1095572767

In your case

dataValidation(wb, 1, col = 1, rows = 2:31, type = "list", value = '"option1,option2"')

[IRorpa]

Thank you so much. This is exactly what I needed @tentacles-from-outer-space.

[JanMarvin]

Glad it's solved

[JanMarvin]

Just to please my curiosity, what is supposed to happen? I've re-written a lot of the data validation code, but rarely use it.

[IRorpa]

Just to please my curiosity, what is supposed to happen? I've re-written a lot of the data validation code, but rarely use it.

The cell that the data validation is applied to is supposed to display a dropdown of the options specified in the list.

[JanMarvin]

Thanks! btw openxlsx2 is currently in development and a first CRAN release should happen in the next week.