Shortcut for copying formulas referencing different worksheets

  • Thread starter Thread starter bhigdon
  • Start date Start date
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
 
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
 
Back
Top