If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
References to worksheets
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 |
#2
|
|||
|
|||
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 wrote: 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 |
#3
|
|||
|
|||
many thanks!
"Dave Peterson" wrote in message ... 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 wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Worksheets are referenced in expressions, how make the worksheets visible? Excel 2000 | L Mehl | General Discussion | 2 | July 27th, 2004 07:20 PM |
Absolute cell references across worksheets | Frank Kabel | Worksheet Functions | 3 | March 4th, 2004 07:06 PM |
Absolute cell references across worksheets | Vasant Nanavati | Worksheet Functions | 0 | March 4th, 2004 06:23 PM |
moving worksheets between files | Markathepub | Worksheet Functions | 1 | December 5th, 2003 06:46 PM |