update for vlookup and #value bug?

[mark.s...@gmail.com]

Hello,

I'm experiencing the VLOOKUP and #VALUE bug described here:
http://freshmeat.net/projects/writeexcel/

Editting the cell with the issue corrects it.

John McNamara provides a workaround there. I'm writing to inquire if
they any better solutions to use to address this, as of 2.14.

Ironically, OpenOffice 2.0 and Gnumeric are smarter about this and
don't need the workaround.

Thanks,

Mark

[jmcnamara]

> I'm experiencing the VLOOKUP and #VALUE bug

>...

> I'm writing to inquire if they any better solutions to use
> to address this, as of 2.14.

Hi,

Currently, the best way to address this is issue is with the workaround
that you refer to and which I'll repeat here so that there is a record
of it.

#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;


my $workbook = Spreadsheet::WriteExcel->new('vlookup.xls');

my $worksheet = $workbook->add_worksheet();

$worksheet->write('A1', 'Data' );
$worksheet->write('A2', ['North', 1]);
$worksheet->write('A3', ['South', 2]);

$worksheet->write('A5', 'Lookup' );
$worksheet->write('A6', 'South' );


# Workaround for bad parsing of VLOOKUP.
my $formula = q{=VLOOKUP(A6,A2:B3,2,FALSE)};
my $vlookup = $worksheet->store_formula($formula);
@$vlookup = map {s/_ref2d/_ref2dV/;$_} @$vlookup;

$worksheet->repeat_formula('B6', $vlookup );


__END__

I need to address this issue at the parser level as soon as I get some
time.

John.
--

[Mark Stosberg]

On Tue, Sep 06, 2005 at 06:02:38AM -0700, jmcnamara wrote:

> # Workaround for bad parsing of VLOOKUP.
> my $formula = q{=VLOOKUP(A6,A2:B3,2,FALSE)};
> my $vlookup = $worksheet->store_formula($formula);
> @$vlookup = map {s/_ref2d/_ref2dV/;$_} @$vlookup;
>
> $worksheet->repeat_formula('B6', $vlookup );

If you have to do this several times, here's a subroutine to encapsulate
it:

# Workaround for bad parsing of VLOOKUP.

# It affected Excel 2002 and 2003, but not OOo and Gnumeric.
#
# Use it in place of a VLOOKUP formula:
#
# _fix_vlookup($ws,$formula);
#

sub _fix_vlookup {
my $ws = shift || die;
my $formula = shift || die;
my $vlookup = $ws->store_formula($formula);

@$vlookup = map {s/_ref2d/_ref2dV/;$_} @$vlookup;

return $vlookup;
}

Later, when this is fixed in ::WriteExcel, this could be redefined to
simply return the input formula again, I suppose.

Mark