indirect and offsetsheet

[JVLOLLAR]

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

 

[Domenic]

Try...

=SUM('Sheet1:Sheet3'!B54)

Hope this helps!

 

[Guest]

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]

The formula suggested produced a #Ref error.

The formula(s) I am trying to fix is/are
=(INDIRECT(A4&"!D54"))
=(INDIRECT(A5&"!D54"))
=(INDIRECT(A6&"!D54"))
this continues for about 50 sheets worth of rows

Instead of typing in the specific Tab Name in A4,(and each subsequent
cell below for each following tab name) I want to type in the Worksheet
Position reference, if it is possible.
So instead of having each tab name listed in the cells as:
A4 Term1
A5 Hardship
A6 Loan Withdrawals

I need to see the value of D54 for each of the worksheets
separately-similar to a linked cell reference, but I don't want to use
a link- (I want to use Indirect.)

I want to type in something like
Worksheet1 or IndexSheet3, IndexSheet4

Maybe I need to move my indirect reference in the formula, I'm not
sure...

(I'm too chicken to try VBA- so I'm stuck with weird formulas in
cells...)
Thanks for all your help!