Indirect worksheet function

[rk0909]

All,

I am using the =SUM('Start>>:<<End'!B7) formula to sum data from 50 sheets.
I want to make this formula little dynamic. I want the '7" in the formula to
be a cell link e.g. A1 so that it sums up the field in the cells whose row is
defined by the number input in A!.

I tried using =SUM(INDIRECT("('Start>>:<<End'!B"&A1) but this gives errors.

Any solutions or alternatives to this method.

Thanks much,

RK

 

[Herbert Seidenberg]

Make a list of all your sheets
and name the list FL.
=SUMPRODUCT(SUMIF(A1,A1,INDIRECT(FL&"!B"&A1)))
If the list can be generated by some
algorithm, further automation is possible.

 

[rk0909]

Thanks Herbert.

I am new to lists, could you please explain little bit more on how to create
the list. I looked at excel help and couldn't find a way to create a list
across diff. sheets.

thanks much,

RK

 

[Herbert Seidenberg]

The most straight forward way is to type
the sheet names into 50 adjacent cells
and name those 50 cells FL.
The automated way is to use a macro:

Sub listsheets()
For i = 1 To Worksheets.Count
Cells(i, "A") = Sheets(i).Name
'List starts at A1.
'If you want to start the list at B22 use
'Cells(i + 21, "B") = Sheets(i).Name
Next i
End Sub

If you sheet names have some order to them,
or you are willing to change the names,
then I can give you an easy shortcut.