I was given the following macro code (from an earlier posting) which has been
effective for me - I think it will help you. The code creates a sheet in the
front of your document with a list of each tab name vertically. Then you can
refer to these tab names instead of typing them each time.

Sub GetMeAllSheetNames()

Const cnsResultSheetName = "Sheet1"

Const cnsStartColName = "b"

Const cnsStartRow = "2"



Dim bSheetWasHidden As Boolean

Dim intRowNum As Integer



intRowNum = cnsStartRow

For Each oSheet In Application.Sheets

'If InStr(oSheet.Name, cnsResultSheetName) = 0 Then



bSheetWasHidden = False

If Sheets(oSheet.Name).Visible = False Then

bSheetWasHidden = True

Sheets(oSheet.Name).Visible = True

End If



Sheets(oSheet.Name).Select

'MsgBox oSheet.Name



Sheets(cnsResultSheetName).Range(cnsStartColName &
CStr(intRowNum)).Formula = CStr(oSheet.Name)



intRowNum = intRowNum + 1





If bSheetWasHidden = True Then

Sheets(oSheet.Name).Visible = False

End If



'End If



Next



Sheets(cnsResultSheetName).Select

Sheets(cnsResultSheetName).Range("A1").Select



End Sub


hth,
Dave


"JVLOLLAR" wrote:


I want to write an Indirect formula to find a value in a cell (B54)
across multiple worksheets. Previously I would write something like
=Sum(indirect ($a1)&!B54)
to find B54 across several sheets(and I'd copy the formula down to
reference A2, A3, etc for each worksheet name. Then I'd put the sheet
names in A1,A2,A3.
When the sheet names are short & simple and you can use AutoFill it
isn't a big deal.
BUT I'd like to convert the formula to look for the offsetSheet, or
IndexSheet so I could just look at B54 value for IndexSheets, 3,4,5...
instead of typing the name of the worksheets in cells A1, A2, A3.
I've tried several times to alter the formula, but I think I'm missing
a ( or a comma.

Thanks in advance


--
JVLOLLAR
------------------------------------------------------------------------
JVLOLLAR's Profile: http://www.excelforum.com/member.php...o&userid=23721
View this thread: http://www.excelforum.com/showthread...hreadid=374087