listing the same cell from different sheets on one sheet

Hi,

I hope that someone can help.

Basically I have 128 worksheets and on sheet one in column A I want to list the values of A1 in each of the sheet so cell A1 reads sheet1!A1 and then sheet2!A1 etc all the way down, i can do this manually but it takes along time is there a formula that I can put in or a way to copy the cell down, that changes the sheet name and not the cell name.

THank you in advance

Julie

Hello Julie,

This macro will place the formula "=Sheetn!A1" in column A of Sheet1, where n is the index number of the worksheet.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel

To Run the Macro
1. While in Excel, press the keys ALT+F8
2. Click the macro's name in the Macro List dialog
3. Click OK

Sincerely,
Leith Ross

Originally Posted by pjoaquin

Hi Julie,

Try this formula in A1 and fill downward:

=INDIRECT("Sheet"&ROW(A1)&"!A1")

Start this at A2 otherwise Sheet1!A1 will reference itself.

Mark,