Does this mean that the Evaluate function worked?

If you're having trouble, get the array formula working in a cell on the
worksheet. Then post that working formula.

Forgone wrote:

First, I'm not sure why you use =value() around your terms. It may make sense
for cells that may not be numeric, but 191 and 999 are already numbers, so it's
not necessary/useful there.

I've since removed the 191 and 999 and converted it to
ytd.ccd=VALUE(rep.fund)

I have had to use value because the cells that is being used as the
search parameters is formatted as text intentionally.

The worksheet is setup so that the search parameters are in the top of
the worksheet which I've named.
BCostCentre | BFund | BEntity | BProject - this is where the user can
input the search parameters if they want to restrict it to a certain
business area or project.

The named ranges: ytd.cca | ytd.ccb | ytd.ccc | ytd.ccd | ytd.cce is
the main datasource table for and are the relevant costcodes.
YTD represents the datasource for "year to date" thus ytd.act = year
to date actuals

BEntity = CCA
BCostCentre = CCB
BFund = CCC
BAccount (not used) = CCD
BProject = CCE

The reference to $C19 is the specific account code (CCD)

I have a number of data sources.

YTD for Year To Date Actuals which gets updated every month
O9B for 2008/2009 Budget
O8B for 2007/2008 Budget
O8A for 2007/2008 Actuals

I'm trying to keep a consistency with the named ranges.

What I've had to do is if I wanted to look at the entire Department as
a whole, I've had to create another worksheet and manipulate the
formulas to suit but that means on a very old system (which I'm using
at work) takes a very long time to calculate. I would like to work on
the one worksheet.

At the end, I only want to use the one worksheet and either report on
the required values (if not null then ...... report on each criterion
if applicable) or if null then report on the lot.

--

Dave Peterson