This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hello,
I am trying to autofill a column referencing different sheets as opposed to different cells.
I need:
='sheet 1'!!$B$11
='sheet 2'!!$B$11
='sheet 3'!!$B$11
... and so on
As opposed to what I continue to get:
='sheet 1'!!$B$11
='sheet 1'!!$B$12
='sheet 1'!!$B$13
I have tried using the formula =INDIRECT("sheet 1"&ROW(B11)&"!$B$11") that I found on another forum, but I keep on getting #REF.
Please Help!
Jason
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
In cell A5, enter the formula
=INDIRECT("'sheet " & (ROW()-ROW($A$5)+1) & "'!B11")
then fill down to A7 (or further down if needed). Please note that there is a single quote (apostrophe) before the word sheet and and also before the exclamation mark ! in the formula.
That worked...Thank you!
I have one problem, when I try to duplicate the sheet they become: sheet (1), sheet (2), Sheet (3) etc.
That code won't recognixe the numbers in the bracket. Thoughts? IS there a way to duplicate the sheets (I need aprox 500 of them) without the brackets or can we make the code recognize the number in the bracket?
Also, how would I make
=INDIRECT("'sheet " & (ROW()-ROW($A$5)+1) & "'!B11") a hyperlink to the cell B11 on the corresponding sheet?
Thank you so much for your help!!
The formulas:
='sheet 1'!!$B$11
='sheet 2'!!$B$11
='sheet 3'!!$B$11
go into the cells a5, a6, and a7 on the master file.
The reference information is coming from cell b11 on sheet 1, 2, 3 etc.
Does this help?
The data required is in the 11th row of column b.
Assuming that the first formula is in row 13, try
=INDIRECT("'sheet" & (ROW()-ROW($A$13)+1) & "'!B11")
If the first formula is in another row, adjust the row number in $A$13 accordingly.