You can do exactly what you want so long as you appropriately quote $ characters. For your example you could:

use strict;
use warnings;

my $chart;
my $variable;

$chart->add_series(
    categories => '=Sheet1!$B$1:$E$1',
    values     => "=Sheet1!\$B\$$variable:\$E\$$variable"
    );
[download]

If you are using Spreadsheet::WriteExcel there is a utility function called xl_range_formula()to deal with this.

See the Working with cell ranges section of the chart documentation:

    $chart->add_series(
        categories    => xl_range_formula( 'Sheet1', 1, 9, 0, 0 ),
        values        => xl_range_formula( 'Sheet1', 1, 9, 1, 1 ),
    );
[download]

If you are using Excel::Writer::XLSX you can use the same utility function or pass an arrayref instead:

    $chart->add_series( categories => '=Sheet1!$A$2:$A$7'      ); # Sa
+me as ...
    $chart->add_series( categories => [ 'Sheet1', 1, 6, 0, 0 ] ); # Ze
+ro-index.
[download]

--
John.