References to worksheets

[Lee Harris]

If I have a file with 50 worksheets in them, numbered 1 to 50, but each are
in the same format, and I have a master sheet that wants to check, say, the
average of a column on each sheet

such that I have this on Sheet 1

A1=average('2'A:A) (average of 1st col in sheet 2)
A2=average('3'A:A) (average of 1st col in sheet 3)

etc

instead of manually setting those up, is there a way to use a
number/reference so that the bit in brackets that says which sheet to look
at can be referred to a cell value so I can autofill the formula on sheet 1
like


A1=2, B1 = average(REFA1 A:A)
A2=3, B2 = average(REFA2 A:A)

that way I can just auto fill a column of sheet indeces in one column, do a
formula in cell B1 and autofill it down without manually doing each formula

cheers

 

[Dave Peterson]

You could use a formula like:

=AVERAGE(INDIRECT("'"&ROW()&"'!a:a"))

If you put it in row 7 to start, adjust it to:

=AVERAGE(INDIRECT("'"&ROW()-6&"'!a:a"))

(row()-x should be 1 for the first cell)

And if you want to use that extra cell:

=AVERAGE(INDIRECT("'"&a2&"'!a:a"))

Where a2 held the worksheet name/number.

 

[Lee Harris]

many thanks!

You could use a formula like:

=AVERAGE(INDIRECT("'"&ROW()&"'!a:a"))

If you put it in row 7 to start, adjust it to:

=AVERAGE(INDIRECT("'"&ROW()-6&"'!a:a"))

(row()-x should be 1 for the first cell)

And if you want to use that extra cell:

=AVERAGE(INDIRECT("'"&a2&"'!a:a"))

Where a2 held the worksheet name/number.