Formula across multiple sheets

Hi,
I'm hoping there's a formula for this...

I have a file with multiple sheets that all have the same
format (system generated output from an ERP system). In
cell AL2 on all sheets is a unique value (2110, 3110,
4110, etc).

What I'm trying to do is create a summary page that would
be set up like a vlookup, with the list of unique values
in column A. Then I'd like to use some kind of formula
to look across all the sheets, find the value in column
A, and return a value from a specified cell on each of
the sheets (F123 in column B, F124 in column C, etc).

Is this possible? I'd be willing to move the value from
AL2 on each sheet to F122 if I can do a vlookup across
multiple sheets.

Thanks,
Dave

Hi Dave
Excel does not support this directly - VLOOKUP is not be able to use 3D
(multiple sheet) references

But you may try the following:
1. Download the free add-in Morefunc.xll
(http://longre.free.fr/english). It includes the function THREED to
convert 3D references to a 2D reference

2. Assumptions:
- your data is in sheet2 - sheet5 (all with the same layout)
- unique identifier in column A of all sheets
- in your master sheet column a contains the lookup values.

3. Use the following function in B1
=VLOOKUP(A1,THREED('sheet2:sheet6'!$A$1:$B$100);2; 0)
to return the corresponding value from column B in one of your sheets


--
Regards
Frank Kabel
Frankfurt, Germany


Dave wrote:
Hi,
I'm hoping there's a formula for this...

I have a file with multiple sheets that all have the same
format (system generated output from an ERP system). In
cell AL2 on all sheets is a unique value (2110, 3110,
4110, etc).

What I'm trying to do is create a summary page that would
be set up like a vlookup, with the list of unique values
in column A. Then I'd like to use some kind of formula
to look across all the sheets, find the value in column
A, and return a value from a specified cell on each of
the sheets (F123 in column B, F124 in column C, etc).

Is this possible? I'd be willing to move the value from
AL2 on each sheet to F122 if I can do a vlookup across
multiple sheets.

Thanks,
Dave