Indirect Reference

[Danny Lewis]

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 Lewis]

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))

 

[Bob Phillips]

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]

=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!

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)

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

 

[Bob Phillips]

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)

=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!

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)

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 Lewis]

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

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)

=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!

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)

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