Excel 2003 - Copy Down Function to Reference Next Worksheet

[Lmarsh1693]

Hi All,

Formula in Worksheet 1, cell A1 = 'Worksheet 2'!B2

How can I copy the above formula down to Worksheet 1, cell A2 such that it
references: 'Worksheet 3'!B2

Thank you,

Larry

 

[T. Valko]

Enter this formula in A1 and copy down as needed:

=INDIRECT("'Worksheet"&ROWS(A$1:A2)&"'!B2")

 

[Lmarsh1693]

Thanks. This will work if I have a number of sheets that I'm working with?

I need to copy this formula down in sheet 1 ( each row in sheet 1 would
reflect the value in a specific cell from each worksheet ) to pull the
corresponding values from the other worksheets. Will this still work so that
each copy down references the next worksheet? Thx.


Larry

 

[RagDyer]

If you sheets do have a <space> between the name and the number,
just add a space to Biff's suggestion after the name:

=INDIRECT("'Worksheet "&ROWS(A$1:A2)&"'!B2")

 

[Michael Montgomery]

To complicate this scenario further, at the end of ech week I develop a sheet named for the year, month, and last day of the week (eg. 20120618). An annual total sheet (AT) already exists, and at weeks end I need to create a new column in the AT sheet and reference the same cell from the week before, but in the new week on the AT page. It would be nice to drag to the new column in the AT sheet and have the cells referenc the new week. Any thoughts?