Create a Dropdown with a Table

[lporras]

Hey guys,

I'm trying to set data validation on cells using a table it does not work, only works when using range like A1:A4

Here is my code:

require 'write_xlsx'

workbook = WriteXLSX.new('write_xlsx_example.xlsx')

dropdownSheet = workbook.add_worksheet('DropDown Values')
dropdownSheet.add_table('A1:A4', {
  data: [
    ['Fruit'],
    ['Vegetable'],
    ['Meat']
  ],
  name: 'Produce',
  columns: [
    { header: 'Produce List' }
  ]
})

worksheet = workbook.add_worksheet

worksheet.data_validation('A1:A100',
{
    :validate        => 'list',
    :value           => "'DropDown Values'!A$2:A$4",
    #:value           => "'DropDown Values'!Produce", -> this does not work
    #:value           => "Produce", -> this does not work
    #:source          => "Produce", -> this does not work
   #:source          => "=Produce", -> this does not work
    #:input_title     => 'Input an integer:',
    :input_message   => 'Choose the value from the dropdown',
    :error_message   => 'Please use the dropdown selector to choose the value'
})

workbook.close

Also would be nice to support named range.

Thanks