dynamic cell references
 

Hello everyone,

I'm a relative novice in terms of Excel and I have a problem that I
hope someone can help with.

I'm basically using a SUMIF formula (in book1) to calculate a total
using data from book2.

e.g.

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

i.e. the formula will sum the values in column 'J' where the search
criteria is satisfied.

The problem is that I want to vary the value range to use for the
totalling so that in the formula the reference to column 'J' is
replaced by a value that is stored in (e.g.) cell 'L1'. In that way I'm
hoping to allow the value of cell 'L1' to control which data is used
for totalling.

Any offers?

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

Your solution worked just fine.

Thanks very much.