data_validation not working , and get a #VALUE!

[Aggie]

I am attaching my excel sheet.
see worksheet Sheet1.

Column B and C are data validation lists
and D uses a lookup function to determine its value.

For data validation lists if I simply open the dialog and press enter
it starts working.
Similarly if I double click the cell in col D all starts to work.

for colD I am using store and repeat formula subroutines, have tried
suggestions using single quotes, qq and changing ref2D to ref2dV
etc...
nothing seems to work for me.

Please help.

Oh !! there is no option to attach files..I can send the sheet if it
helps.

The validation formula for col B:
=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($K3,"-","_"),"
","_"),"&","_"))

for col C:
=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($L3,"-","_"),"
","_"),"&","_"))

for Col D the formula is
=VLOOKUP(C3,INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($L3,"-","_"),"
","_"),"&","_")),2,FALSE)

[jmcnamara]

> The validation formula for col B:
> =INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($K3,"-","_"),"
> ","_"),"&","_"))
>
> for col C:
> =INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($L3,"-","_"),"
> ","_"),"&","_"))
>
> for Col D the formula is
> =VLOOKUP(C3,INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($L3,"-","_"),"
> ","_"),"&","_")),2,FALSE)

Hi,

If you send me a small but complete example then I will have a look at
the problem.

See the following for the suggested format of the bug report:


http://groups.google.com/group/spreadsheet-writeexcel/browse_frm/thread/23c26520737d753b

John.
--

[Aggie]

Perl version : 5.008008
OS name : linux
Module versions: (not all are required)
Spreadsheet::WriteExcel 2.25.06
Parse::RecDescent 1.94
File::Temp 0.21
OLE::Storage_Lite 0.14
IO::Stringy 2.110

following is the minimal script that closely mimics the required list
and formula being used in the original sheet.
#-------------------------------------------
use strict;
use warnings;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Utility;


my $workbook = Spreadsheet::WriteExcel->new("/tmp/
datavalidation.xls");
my $worksheet = $workbook->add_worksheet("List") ;

my @depts = ("Information Technology", "Support", "Professional
Services");
my @itemployees = ("employee 1", "employee 2");
my @suppemployees = ("employee 3", "employee 4");
my @psemployees = ("employee 5", "employee 6");
my @nums = qw/1234 2345 3456 4567 5678 6789/;


$worksheet->write_blank(0,0);
$workbook->define_name("BLANK",'=List!$A$1:$A$1');
$worksheet->write_col(1,0,\@depts);
$worksheet->write_col(1,2,\@nums);
$worksheet->write_col(1,1,\@itemployees);
$worksheet->write_col(3,1,\@suppemployees);
$worksheet->write_col(5,1,\@psemployees);

$workbook->define_name('Dept','=List!$A$2:$A$4');

my %invalidchars;
my $start = 2;
my $end;
foreach my $dept (@depts) {
$end = $start + 1;
my $invalid = $dept;
$invalid =~ s/[\w\.]+//g;
@invalidchars{split (//,$invalid)} = 1;
$dept =~ s/[^\w\.]+/_/g;
$workbook->define_name("$dept",'=List!$B$'. $start. ':B$'. $end );
$start = $end + 1;
}
$workbook->define_name("Employee",'=List!$B2:C$'. $end );

$worksheet = $workbook->add_worksheet("Information") ;
$worksheet->write(1,25,"EMPLOYEE");
$worksheet->data_validation( '$A1:$A10',
{ validate => 'list',
value => qq/=Dept/,
dropdown => 1,
ignore_blank => 0
});
my $substitute = "CELL";
my $formula = $substitute;
foreach my $invalidchar (keys %invalidchars) {
$formula = "SUBSTITUTE($substitute,\"$invalidchar\",\"_\")";
$substitute = $formula;
}
$formula =~ s/CELL/\$A1/g;
$worksheet->data_validation( '$B1:B10',
{ validate => 'list',
value => qq/=IF(ISBLANK(\$A1),BLANK,INDIRECT
($formula))/,
dropdown => 1,
ignore_blank => 0
});
my $partformula = $worksheet->store_formula(qq/=VLOOKUP(A1,INDIRECT
(Z2),2,FALSE)/);

foreach my $row (0..9) {
$worksheet->repeat_formula($row,2,$partformula,undef,('A1'=>
xl_rowcol_to_cell($row,1)));
}
$workbook->close();

#---------------------------------------------------


The workbook generated will contain 2 sheets.
The first is the List sheet that has definitions of various lists name
(defined names)
which are then used in Information sheet.

In the Information sheet you will see
3 cols (rows 1 to 10)
In col A: a dropdown list of depts can be seen (no problems)
in Col B: a dropdown list of employees in each dept (to populate this
list you need to go to the validation dialog and hit enter)
in col C: a formula to get the employee number (this has !#VALUE but
if you f2 and press enter in column a valid number is popluated)

Your help is much appreciated.
On 29 July, 16:50, jmcnamara <jmcnam...@cpan.org> wrote:
> > Thevalidationformula for col B:

> http://groups.google.com/group/spreadsheet-writeexcel/browse_frm/thre...
>
> John.
> --

[jmcnamara]

On Aug 3, 4:40 pm, Aggie <vaawas...@googlemail.com> wrote:
> Perl version   : 5.008008
> OS name        : linux
> Module versions: (not all are required)
>                      Spreadsheet::WriteExcel    2.25.06
>                      Parse::RecDescent          1.94
>                      File::Temp                 0.21
>                      OLE::Storage_Lite          0.14
>                      IO::Stringy                2.110
>
> following is the minimal script that closely mimics the required list
> and formula being used in the original sheet.

Hi,

The problem with the data_validation is that the INDIRECT() function
isn't parsed correctly. You will have to avoid using that in a data
validation for now. In fact anything other that simple ranges ins't
recommended.

There is also a similar problem with the INDIRECT() formula in the
main body of the program. That is probably fixable with a workaround.

John.
--