Shortcut for copying formulas referencing different worksheets

B

bhigdon

I am looking for a shortcut. I am copying the same forumula from Row 1
down the page, but where Row 1 references the next worksheet, Row 2
references the following worksheet, and so on. Is there an easy way to
do this without having to go to each worksheet, find the cell and click
on it? TIA
 
G

Gord Dibben

One method.....

If sheets are named Sheet1, Sheet2 etc. enter this formula in A1 of your
master sheet.

=INDIRECT("'Sheet" & (ROW() & "'!$A$1")

Drag/copy down column B.

If sheets have unique names, enter the sheet names in a column and use this
formula.

=INDIRECT(B1 & "!$A$1")

If spaces in sheetnames use =INDIRECT("'" & B1 & "'!$A$1")

Assume sheet names were in B1:B10 you would copy down 10 cells.

Your ranges may differ, so adjust to suit.


Gord Dibben Excel MVP
 
Top