Instead of

SUMIF ('[book2.xls]sheet'!$A$1:$A$1000, B1 ,
'[book2.xls]sheet'!$J$1:$J$1000)

Try replacing the j1:j1000 with

Indirect(L1)

Then give names to the various ranges that are alternatives to j1:j100
(insert, name define) and enter the name of the range in L1. The
formula will then work on which ever range is named in L1.


the ranges named must be the same size as A1:a1000.


--
Ruthki
------------------------------------------------------------------------
Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
View this thread: http://www.excelforum.com/showthread...hreadid=386387