Microsoft 365 and Office | Excel | For home | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Hi
=INDIRECT("Sheet" &ROW(A1) &"!B6")
copying a 3d reference formula
![](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)
Anonymous
Help
I want to copy the 3d reference and have the sheet number change ie: =sheet1$B$6 the next one would be =sheet2$B$6 then =sheet3$B$6
Microsoft 365 and Office | Excel | For home | Windows
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
Anonymous
Answer accepted by question author
Anonymous
Where are you copying it to? Down? Across?
Since you are within the same workbook, you can use INDIRECT to help you out. Assuming you are copying downward, formula is:
=INDIRECT("sheet"&ROW(A1)&"!B6")
Note that I did not need to include absolute reference symbols as the formula uses a static text string for that part. If you want to copy the formula across, use COLUMN(A1) instead of ROW(A1)
1 additional answer
Sort by: Most helpful
-
Anonymous
2015-06-06T23:39:01+00:00 Thank you. Very simple and concise answer.