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



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

[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" <DannyLewis@discussions.microsoft.com> wrote in message
news:8856D135-4DEF-47C9-8902-5611509F7B67@microsoft.com...
> 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]

=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" <DannyLewis@discussions.microsoft.com> wrote in message
> news:8856D135-4DEF-47C9-8902-5611509F7B67@microsoft.com...
> > 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)

"Danny Lewis" <DannyLewis@discussions.microsoft.com> wrote in message
news:DB64DA47-6A31-4F43-8024-7264FCFD618A@microsoft.com...
> =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" <DannyLewis@discussions.microsoft.com> wrote in message
> > news:8856D135-4DEF-47C9-8902-5611509F7B67@microsoft.com...
> > > 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

"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" <DannyLewis@discussions.microsoft.com> wrote in message
> news:DB64DA47-6A31-4F43-8024-7264FCFD618A@microsoft.com...
> > =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" <DannyLewis@discussions.microsoft.com> wrote in message
> > > news:8856D135-4DEF-47C9-8902-5611509F7B67@microsoft.com...
> > > > 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
> > > >
> > >
> > >
> > >
>
>
>