Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup formula
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP FORMULA | Excel Discussion (Misc queries) | |||
lookup formula? | Excel Worksheet Functions | |||
Lookup formula? | Excel Discussion (Misc queries) | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions | |||
Lookup formula | Excel Discussion (Misc queries) |