Indirect worksheet function
 

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

Indirect worksheet function
 

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.

Indirect worksheet function
 

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" wrote:

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.

Indirect worksheet function
 

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.