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

Use Tab Name in Lookup Field
 

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

Use Tab Name in Lookup Field
 

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

Use Tab Name in Lookup Field
 

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