Lookup formula

[Joe Gieder]

First, sorry for the long post and thank you for your help and looking at
this post.
I have a workbook containing many sheets, one of the sheets contains all of
the sheet names within the workbook and another cell reference
example of sheet containing sheet names:
A2 B2
GE PO 2051280 $B$8
$B$8 refers to an invoice number

On the sheet "GE PO 2051280" cell B8 is the invoice number

I'm trying to create a formula that will use a cell reference for the sheet
name because the list is constantly changing and have a formula something
like this:
=A2!B2, 2 columns to the right for the dollar amount
I tried using offset and match but get errors saying there's an error in my
formula.

It would realy be nice if the following code that Dave Peterson wrote could
be modified to include another column called "Inv Amount". As you can see I
tried but I don't get any results.

Sub ShowCommentsAllSheets()
'modified from code
'posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False

Dim commrange As Range
Dim mycell As Range
Dim ws As Worksheet
Dim newwks As Worksheet
Dim i As Long

Set newwks = Worksheets.Add

newwks.Range("A1:F1").Value = _
Array("Sheet", "Cell Address", "Name", "Invoice Number", "Comment",
"Inv Amount")

For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set commrange = ws.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
'do nothing
Else

i = newwks.Cells(Rows.Count, 1).End(xlUp).Row

For Each mycell In commrange
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = ws.Name
.Cells(i, 2).Value = mycell.Address
.Cells(i, 3).Value = mycell.Name.Name
.Cells(i, 4).Value = mycell.Value
.Cells(i, 5).Value = mycell.Comment.Text
.Cells(i, 6).Value = mycell.Amount
End With
Next mycell
End If
Set commrange = Nothing
Next ws

'format cells for no wrapping, remove line break
newwks.Cells.WrapText = False
newwks.Columns("E:E").Replace What:=Chr(10), _
Replacement:=" ", Lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Application.ScreenUpdating = True

Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

Sheets("Sheet1").Select
Sheets("Sheet1").Move After:=Sheets(5)

Sheets("Sheet2").Select
Sheets("Sheet2").Move After:=Sheets(5)

Sheets("Sheet3").Select
Sheets("Sheet3").Move After:=Sheets(5)

Sheets("Sheet4").Select
Sheets("Sheet4").Move After:=Sheets(5)

Sheets("Sheet5").Select
Sheets("Sheet5").Move After:=Sheets(5)

End Sub