Reference a Sheet and Cell for use in Formula

Question

I have a workbook where I want to reference a range in the first Sheet (B1) for the input of a formula in a cell in the second Sheet (B3). My problem is the first Sheet's name will always change so I thought to get around this I would create a variable based on the cell reference that the formula could use. This is what I have so far:

Sub GetCellAddress()

Dim Cell As Range
Dim CellAddress As String
Set Cell = ActiveWorkbook.Worksheets(1).Cells(1, 2)
CellAddress = Cell.Parent.Name & "!" & Cell.Address(External:=False)

With ActiveSheet
    Range("B3").Select
    ActiveCell.Formula = _
    "=CONCATENATE(""Balance Sheet"","" - "",MID(CellAddress,FIND("" "",CellAddress,FIND("","",CellAddress)+2)+1,256))"
End With

'MsgBox CellAddress

End Sub

Although the MsgBox will confirm the CellAddress I have is Sheet1!$B1$1 I cannot seem to pass this reference in the formula by just using CellAddress as I get the #NAME? error when I use it. If I use Sheet1!R[-2]C as the ActiveCell.FormulaR1C1 syntax, it works fine but as I explained before I want the code to resolve the Sheet name. Either I have the wrong syntax or maybe my method is the incorrect way of referencing a range in another sheet. Can anyone show me where I am going wrong.

Answer 1

After trying out the solutions above I used the following syntax:

 Sheets(2).Range("B3").Formula = "=CONCATENATE(""Balance Sheet"","" - "",
 MID(" & Sheets(1).Name & "!B1,FIND("" "", " & Sheets(1).Name & "!B1,
 FIND("","", " & Sheets(1).Name & "!B1)+2)+1,256))"

which seemed to address the issue I had with referencing the sheet. So, it was just a matter of putting quotes in the right place when using @asleedawg 's Index method.

Care must be taken if the Sheet has spaces in the name. If it does, then add a single quote around the double quote referencing the sheet name.

Answer 2

I suspect the problem may be due to the syntax of cell address. If you ref cell A1 from 'sheet space' in 'Sheet1', you should get:

='sheet space'!A1

Notice the sheet name is wrapped in " ' " when the sheet name has a white space in it. When the sheet name is a single-word, the " ' " gets auto-formatted away, but is still a valid ref (i.e. ='Sheet1'!A1 is valid)

Try:

CellAddress = "'" & Cell.Parent.Name & "'!" & Cell.Address(External:=False)

Answer 3

Using worksheet index numbers instead of names:

As I understand it, at least part of your issue is that you need to be able to refer to a worksheet even though the name of the worksheet will be changing at times.

You could refer to the worksheet's index number instead of it's name.

This temporary sub will list the names and index numbers of your existing worksheets in the Immediate Window: Ctrl + G

Sub CheckIdx()
   Dim ws as Worksheet
   For Each ws In Sheets
     Debug.Print ".Index=" & ws.Index,".Name=" & ws.Name,".CodeName=" & ws.CodeName
   Next ws
End Sub

The index numbers won't change when the name does.

If you have a sheet named "data" with an index number of 3, you could refer to it either by name:

Sheets("data")

...or, by number:

Sheets(3)

---

Edit:

As @Chris pointed out, Worksheet Index numbers do change, for example if the order of the worksheet tabs is changed.

This can be prevented by protecting the workbook (with or without a password), by clicking:

• Review → Protect Workbook → (Only "Structure" should be checked) → OK

• Password not necessary but is optional. Protecting the workbook structure prevents users from adding, moving, deleting, or hiding/unhiding worksheets, and is not the same as protecting the worksheet, so cells can still be edited, etc.

  

---

Another method would be to use the worksheet CodeName property instead of Index. Technically this can also be changed but is less likely.

I've updated the CheckIdx function (above) to inlcude CodeName, so if you wanted to go this route.