I have a 400 page spreadsheet and I just want the info from box A2 and F40
from each page to appear in two columns on a seperate page. A2 on each page
is a Part # and F40 is our current inventory. Does anyone know the equation
and how to set up this 'all' inventory page?

Thanks,
--
Kelly

Assuming a "page" is a worksheet and you have 400 worksheets in a single
workbook.........

How are the sheets named?

If named Sheet1, Sheet2 etc.

On a summary sheet enter this formula in A1

=INDIRECT("Sheet" & (ROW()) & "!A2") and copy down to A400

In B1 enter =INDIRECT("Sheet" & (ROW()) & "!F40")

Copy down.

If sheets are uniquely named VBA will be rquired.

Sub ListCells()
Dim Cell As Range
Dim sh As Worksheet
Dim csh As Worksheet
Set csh = ActiveWorkbook.Worksheets.Add
csh.Name = "Summary"
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < csh.Name Then
With csh.Range("a65536").End(xlUp).Offset(1, 0)
.Value = sh.Range("A2").Value
.Offset(0, 1).Value = sh.Range("F2").Value
End With
End If
Next sh
End Sub



Gord Dibben MS Excel MVP



On Wed, 21 Oct 2009 08:08:01 -0700, Kelly
wrote:

I have a 400 page spreadsheet and I just want the info from box A2 and F40
from each page to appear in two columns on a seperate page. A2 on each page
is a Part # and F40 is our current inventory. Does anyone know the equation
and how to set up this 'all' inventory page?

Thanks,