Lookup Across Workbooks & Tabs

[Mary]

Here's an example of my problem. In cell a1 of Sheet1 in
workbook Summary.xls, I have computed the maximum value
over a range consisting of the same cell (cell a1) across
30 tabs in 5 different workbooks. I need to set cell b1
on Sheet1 of Summary.xls equal to the contents of cell b1
from the specific tab & workbook that produced this
maximum value. Assume all numbers in this range are
unique, and so only one match is possible. Is there a way
to do this using Excel functions, without the use of
macros? (I have never written an Excel macro.)

 

[Frank Kabel]

Hi
is B1 a text or numeric value?

 

[Mary]

Cell B1 contains text.

-----Original Message-----
Hi
is B1 a text or numeric value?

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[Frank Kabel]

Hi
o.k. how are your worksheets named. Do they have a sequence. e.g.
sheet1, sheet2, sheetx?

 

[Mary]

The worksheet names don't have a sequence, such as sheet1,
sheet2, etc. The worksheet names are descriptive & are in
no particular order, but the worksheets are contiguous.
For the max calculation, the range of contiguous
worksheets to be used in each workbook is defined using
blank worksheets with names such as StartSheet & EndSheet.

 

[Frank Kabel]

Hi
not possible without some VBA (At least to my knowldege). Try the
following:
1. download the free add-in Morefunc.xll
(http://longre.free.fr/english)
2. Try the following formula
=VLOOKUP(MAX('sheet_start:sheet_end'!A1),THREED('sheet_start:sheet_end'
!A1:B1),2,0)

 

[Mary]

Thanks for your help!

-----Original Message-----
Hi
not possible without some VBA (At least to my knowldege). Try the
following:
1. download the free add-in Morefunc.xll
(http://longre.free.fr/english)
2. Try the following formula
=VLOOKUP(MAX('sheet_start:sheet_end'!A1),THREED ('sheet_start:sheet_end'
!A1:B1),2,0)

--
Regards
Frank Kabel
Frankfurt, Germany



.