Hi!

One way:

Create a list of the sheet names:

H1 = Sheet2
H2 = Sheet3
H3 = Sheet4
H4 = Sheet5

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDIRECT("'"&INDEX(H$1:H$4,MATCH(TRUE,COUNTIF(IND IRECT("'"&H$1:H$4&"'!E4"),A1)0,0))&"'!AC1")

Biff

"Jaisenm" wrote in message
...
I have a workbook with a summary tab and one tab for ever week.

I need to write a formula that looks for a value from column a, find the
sheet with a matching value in cell e4 and returns the value of cell ac1
of
the worksheet.

This formula will live in column b of the summary sheet.

Example:

Summary Tab

A B
1 19-Jul 19,000(formula result)
2 16-Jul
3 23-Jul
4 30-Jul

Data Tab 1

E AC
1 9-Jul 19,000

It will need to look in cell E1 of every tab in the work book (52 tabs)
for
the value found in $a1.