Transferring data from multiple worksheets to a Summary sheet

[Bette]

I want to copy data from the same cell in multiple worksheets and I also want
the ability to update.

To speed up the process of creating the Summary sheet, I want to drag down a
column in the Summary sheet and change the worksheet # but not change the
cell reference , e.g. =('5'!$A$2) allows me to drag and the cell reference
will not change but neither does the worksheet.

Is there something I can attach to the '5' that allows it to change by one
as I drag down?

 

[Gord Dibben]

=INDIRECT(ROW(A1)&"!$B$6")


Gord Dibben MS Excel MVP

 

[Bette]

Thank you, thank you, thank you. Just what I wanted.