Is there an easy way to Auto Fill a formula in Excel, where the Sheet reference is the only thing that changes?

0
votes

I've left the default, Sheet1, Sheet2, etc. I have an formula that I want to run through all the sheets in a workbook:

  • In Sheet1,A1 I have: =Sheet2!$A$3.

  • In Sheet1,B1 I have: =VLOOKUP("Total Data Used",Sheet2!$A$1:$G$555,4,FALSE)

How do I copy that down to cover all 100 sheets, where the only change is the sheet number reference?

For Example:

Sheet1, A2 should be:
=Sheet3!$A$3

Sheet1, B2 should be:
=VLOOKUP("Total Data Used",Sheet3!$A$1:$G$555,4,FALSE)

Sheet1, A3 should be:
=Sheet4!$A$3

Sheet1, B3 should be:
=VLOOKUP("Total Data Used",Sheet4!$A$1:$G$555,4,FALSE)

1
excelexcel-formulacopy-pasteautofillexcel-indirect

1 Answers

0
votes

Please try in Sheet1:

In A1: 

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

and in B1 (both formulae copied down to suit): 

=VLOOKUP("Total Data Used",INDIRECT("Sheet"&ROW()+1&"!"&"$A$1:$G$555"),4,FALSE)

With 100 sheets there may be a performance hit (INDIRECT is volatile).