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

No worries, solved it with

=SUMPRODUCT((INDIRECT("'[Incidents
period"&RIGHT(AF$3,4)&".xls]DATA'!$G$8:$G$2000")=$C4)*(INDIRECT("'[Incidents
period"&RIGHT(AF$3,4)&".xls]DATA'!$W$8:$W$2000")=$AT$1))



"Danny Lewis" wrote:

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

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

=SUMPRODUCT((INDIRECT.EXT("'[Incidents
period"&RIGHT(AF$3,4)&".xls]DATA'!$G$8:$G$2000")=$C4)*(INDIRECT.EXT("'[Incidents period"&RIGHT(AF$3,4)&".xls]DATA'!$W$8:$W$2000")=$AT$1))

Thanks Bob - I did actually try INDIRECT.EXT, as above, having installed the
morefunc, however it returns a #VALUE! error! :(



"Bob Phillips" wrote:

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

Danny,

Does your solution mean that it will never need to work on closed workbooks
then? If not, I will check out another solution for you, Harlan Grove's Pull
function, which I know does work with ranges. As I said, I haven't used
Morefunc, I don't like installing XLLs, so I can't help with that.

--
HTH

Bob Phillips

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

"Danny Lewis" wrote in message
...
=SUMPRODUCT((INDIRECT.EXT("'[Incidents

period"&RIGHT(AF$3,4)&".xls]DATA'!$G$8:$G$2000")=$C4)*(INDIRECT.EXT("'[Incid
ents period"&RIGHT(AF$3,4)&".xls]DATA'!$W$8:$W$2000")=$AT$1))

Thanks Bob - I did actually try INDIRECT.EXT, as above, having installed
the
morefunc, however it returns a #VALUE! error! :(



"Bob Phillips" wrote:

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

Well, it would be more than helpful if I could get this closed book thing
sorted because otherwise the files need to be opened every time...

I downloaded morefunc. The original formula worked, where the referenced
workbook was open...but when I added .EXT onto both INDIRECTs it brought up
an error value.

I tried to look at this pull function but I couldn't get into the FTP
server...

Regards
Danny

"Bob Phillips" wrote:

Danny,

Does your solution mean that it will never need to work on closed workbooks
then? If not, I will check out another solution for you, Harlan Grove's Pull
function, which I know does work with ranges. As I said, I haven't used
Morefunc, I don't like installing XLLs, so I can't help with that.

--
HTH

Bob Phillips

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

"Danny Lewis" wrote in message
...
=SUMPRODUCT((INDIRECT.EXT("'[Incidents

period"&RIGHT(AF$3,4)&".xls]DATA'!$G$8:$G$2000")=$C4)*(INDIRECT.EXT("'[Incid
ents period"&RIGHT(AF$3,4)&".xls]DATA'!$W$8:$W$2000")=$AT$1))

Thanks Bob - I did actually try INDIRECT.EXT, as above, having installed
the
morefunc, however it returns a #VALUE! error! :(



"Bob Phillips" wrote:

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