What answer do you need? I mentioned that INDIRECT will not work with closed
workbooks. Will yours never be closed, in which case you can use

=SUMPRODUCT((INDIRECT("'[Incidents
period"&H1&".xls]DATA'!$G$8:$G$2000")=$C4)*
(INDIRECT("'[Incidents period"&H1&".xls]DATA'!$W$8:$W$2000")="Mainline"))

If it will be closed, then you need another solution. Laurent Longre has a
an INDIRECT.EXT function within his MOREFUNC addin at
http://xcell05.free.fr/english/, I haven't used it but I assume it will work
with ranges.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Danny Lewis" wrote in message
...
Good morning all

I did in fact post a question about this yesterday afternoon but I didn't
really get the answers I needed and I wondered if anyone could help me
out,
because it's drive me insane.

Along the top of a set of data run period numbers, eg. P0701, P0702, P0703
and so on.

Below that I have a formula which CURRENTLY looks like this under P0703:

=SUMPRODUCT(('[Incidents
period0703.xls]DATA'!$G$8:$G$2000=$C4)*('[Incidents
period0703.xls]DATA'!$W$8:$W$2000="Mainline"))

It looks up data in a period's file, in this case, Incidents period0703.

How can I alter this formula so it reads from the column header the file
it's supposed to be reading from, rather than me changing it every period?

Many thanks in advance for you help,

Danny