0

Let's say I have "Sheet A" and "Sheet B" within the same workbook.

Sheet B has a cell that references Sheet "A" i.e. ='Sheet A '!Q20

I want to copy Sheet B and Create "Sheet C" in the same workbook and I want the cell reference (='Sheet A '!Q20) in Sheet C to now reference Sheet B. i.e. ='Sheet B '!Q20.

When I copy sheet B and create Sheet C, the cell reference is still ='Sheet A '!Q20

I have seen solutions to similar issues but not when copying sheets within the same workbook.

TIA

Improve this question
1
  • I don't think you could do that in a single step as by definition, creating a copy means it copies what's in the original sheet. But if your formulas are all consistent, as in your question, then you could simply do a replace, searching for Sheet A and replacing with Sheet B. Just make sure that you're searching within the sheet and looking in formulas. You could likely make a simple vba macro that would combine the two actions.
    – Mockman
    Commented Jul 14, 2023 at 2:54

2 Answers 2

Reset to default
0

Try this formula in Sheet B:

=INDIRECT("'Sheet "&CHAR(CODE(RIGHT(CELL("filename"),1))-1)&"'!A1")

enter image description here

Improve this answer
0

You can also define a simple UDF, which returns a name of a worksheet whose position is moved by ofs from the current worksheet.

Function sh(ofs As Long) As String
    Dim cw As Long
    cw = Application.Caller.Parent.Index
    sh = Worksheets(cw + ofs).Name
End Function

The name of the previous worksheet will be sh(-1), the next will be sh(1).
Now you can refer to the cell Q20 in the previous worksheet by a formula:

=INDIRECT("'"&sh(-1)&"'!Q20")

or

=INDIRECT(ADDRESS(20,17,,,sh(-1)))
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .