Use Tab Name in Lookup Field

I have a large File that I need to summarize.

On the Summarize tab I need to pull data from other tabs "2007-01", 2007-02,
etc...

There are several fields on each tab that I need to lookup and enter into
"Summarize".

Example, "2007-01!E5" and "2007-01!-G5", etc...

How can I use the tab name to repeat and pull data from each tab?

See Below: Invoice # is the source tab name. Inv Date, Fee, Per Diem isn the
data I need to pull from each tab.


BILLING SUMMARY - 2008

Invoice # Inv Date Fee Per Diem Room Other Total

2008-01 1/07/2008 $600.00 $50.00 $126.14 $31.11 $807.25
2008-02 1/14/2008 $600.00 $50.00 $105.57 $- $755.57
2008-03 1/14/2008 $600.00 $50.00 $105.57 $- $755.57

Thanks
--
A MACKENZIE, CMA, MBA

demechanik
Sat Mar 15 19:14:01 CDT 2008

In your summary sheet,
if you have the tabnames in A2 down,
you could place this in B2:
=OFFSET(INDIRECT("'"&$A2&"'!A5"),,COLUMNS($A:A)-1)
Copy B2 across & fill down as far as required.
Format the returns as appropriate (date, currency etc)

Above will return what's in A5 across in each of the individual tabs (ie,
A5, B5, C5,...). Adapt the anchor: "A5" in the expression to suit. Eg if you
want to extract from E5 across instead, just replace "A5" with "E5"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"aMack" wrote:
> I have a large File that I need to summarize.
>
> On the Summarize tab I need to pull data from other tabs "2007-01", 2007-02,
> etc...
>
> There are several fields on each tab that I need to lookup and enter into
> "Summarize".
>
> Example, "2007-01!E5" and "2007-01!-G5", etc...
>
> How can I use the tab name to repeat and pull data from each tab?
>
> See Below: Invoice # is the source tab name. Inv Date, Fee, Per Diem isn the
> data I need to pull from each tab.
>
>
> BILLING SUMMARY - 2008
>
> Invoice # Inv Date Fee Per Diem Room Other Total
>
> 2008-01 1/07/2008 $600.00 $50.00 $126.14 $31.11 $807.25
> 2008-02 1/14/2008 $600.00 $50.00 $105.57 $- $755.57
> 2008-03 1/14/2008 $600.00 $50.00 $105.57 $- $755.57
>
> Thanks
> --
> A MACKENZIE, CMA, MBA

aMack
Tue Mar 18 14:10:00 CDT 2008

Max: Thanks.

Getting close - The 1st column works - "B"

The second column "C" gives me the same resuilts as column "B".

=OFFSET(INDIRECT("'"&$A37&"'!ar76"),,COLUMN($A:C)-1)


"AR76" does not change to "AS76"




--
A MACKENZIE, CMA, MBA


"Max" wrote:

> In your summary sheet,
> if you have the tabnames in A2 down,
> you could place this in B2:
> =OFFSET(INDIRECT("'"&$A2&"'!A5"),,COLUMNS($A:A)-1)
> Copy B2 across & fill down as far as required.
> Format the returns as appropriate (date, currency etc)
>
> Above will return what's in A5 across in each of the individual tabs (ie,
> A5, B5, C5,...). Adapt the anchor: "A5" in the expression to suit. Eg if you
> want to extract from E5 across instead, just replace "A5" with "E5"
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "aMack" wrote:
> > I have a large File that I need to summarize.
> >
> > On the Summarize tab I need to pull data from other tabs "2007-01", 2007-02,
> > etc...
> >
> > There are several fields on each tab that I need to lookup and enter into
> > "Summarize".
> >
> > Example, "2007-01!E5" and "2007-01!-G5", etc...
> >
> > How can I use the tab name to repeat and pull data from each tab?
> >
> > See Below: Invoice # is the source tab name. Inv Date, Fee, Per Diem isn the
> > data I need to pull from each tab.
> >
> >
> > BILLING SUMMARY - 2008
> >
> > Invoice # Inv Date Fee Per Diem Room Other Total
> >
> > 2008-01 1/07/2008 $600.00 $50.00 $126.14 $31.11 $807.25
> > 2008-02 1/14/2008 $600.00 $50.00 $105.57 $- $755.57
> > 2008-03 1/14/2008 $600.00 $50.00 $105.57 $- $755.57
> >
> > Thanks
> > --
> > A MACKENZIE, CMA, MBA

demechanik
Tue Mar 18 17:03:01 CDT 2008

> =OFFSET(INDIRECT("'"&$A37&"'!ar76"),,COLUMN($A:C)-1)
> "AR76" does not change to "AS76"

In the start cell, ie the first formula cell that you propagate the formula
from, the incrementer part: COLUMNS($A:A) in the expression must remain
unchanged. So it should be this:
> =OFFSET(INDIRECT("'"&$A37&"'!ar76"),,COLUMN($A:A)-1)

But I'm not sure if your set-up/requirements have changed since,
based on your line:
> "AR76" does not change to "AS76"

AR76 is fixed, yes, but it's just an anchor cell ref in each tab name that
you have running in A2 down. The "col" incrementation in the earlier
expression is done by the part: COLUMN($A:A)-1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"aMack" wrote:
> Max: Thanks.
>
> Getting close - The 1st column works - "B"
>
> The second column "C" gives me the same resuilts as column "B".
>
> =OFFSET(INDIRECT("'"&$A37&"'!ar76"),,COLUMN($A:C)-1)
>
>
> "AR76" does not change to "AS76"