External reference to spreadsheet names (tabs) help needed.

[cuyuni]

Hi all,

I have a question for the forum.

If I have two workbooks, let’s say workbook1 (database) & workbook2
(calc’s)
Workbook1 has two spreadsheets named “tab1” & “tab2”.
Workbook2 has one spreadsheet named “calcs”.
Workbook2 has formulas linked to workbook1 which contains all the data
needed for the calculation.

My question: is there any way I can reference the formulas in
worksheet2 to the “internal” name of the tabs in worksheet1, so it
doesn’t matter if the names of the two spreadsheets change, the links
in spreadsheet2 still work ?

I don’t know if excel keep an internal name or index for each
spreadsheet instead of the name we put in the tabs.

I appreciate your help,

Thanks

 

[Leith Ross]

Hello cuyuni,

Here is the code for a UDF (User Defined Function) that will
automatically update the link when the Worksheet name is changed. Paste
this code into a VBA module in Workbook1. If you need help with
installing a Module in the Workbook, let me know in your next post.


Code:
--------------------
Function LinkCells(Control_Cell As Range)

Application.Volatile
LinkCells = Control_Cell.Value

End Function
--------------------


For example let's say A1 on Worksheet1 is to be linked to D1 on
Worksheet2...

A1 would intially contain =LinkCells(Sheet2!D1)

Now, let's change Worksheet2's name to "clac's"...

A1 will now update and contain =LinkCells('calc"s'!A1)

Sincerely,
Leith Ross

 

[Dave Peterson]

There's nothing built into excel that exposes the codename of a worksheet to a
formula.

But if the other workbook were open, you could use a UDF like Leith suggested.

But you'd have to pass it the workbook name, codename and address.

Option Explicit
Function GetValueFromCodeName(WkbkName As String, WksCodeName As String, _
Addr As String) As Variant

Application.Volatile

Dim testWkbk As Workbook
Dim testWks As Worksheet
Dim testRng As Range

Set testWkbk = Nothing
On Error Resume Next
Set testWkbk = Workbooks(WkbkName)
On Error GoTo 0

If testWkbk Is Nothing Then
GetValueFromCodeName = "Invalid WorkBook Name"
Exit Function
End If

Set testWks = Nothing
For Each testWks In testWkbk.Worksheets
If LCase(testWks.CodeName) = LCase(WksCodeName) Then
Exit For
End If
Next testWks

If testWks Is Nothing Then
GetValueFromCodeName = "Invalid WorkSheet Name"
Exit Function
End If

Set testRng = Nothing
On Error Resume Next
Set testRng = testWks.Range(Addr)
On Error GoTo 0

If testRng Is Nothing Then
GetValueFromCodeName = "Invalid Address"
Exit Function
End If

If testRng.Cells.Count > 1 Then
GetValueFromCodeName = "Too many cells"
Exit Function
End If

GetValueFromCodeName = testRng.Value

End Function

And you'd use it in a cell in a worksheet like:
=getvaluefromcodename("book2.xls","sheet1","A1")

But it breaks as soon as the "sending" workbook is closed (and excel
recalculates).

The application.volatile is there to update the function if the other "sending"
cell changes.

Because we're passing strings to the UDF, excel doesn't know what to check to
know when to recalculate--so don't trust the value until you force a
recalculation.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=getvaluefromcodename("book2.xls","sheet1","A1")

=====
Just my opinion--with all the limitations that this has, I wouldn't use it.