Changed Excel data link defaults to first sheet of external reference and not given sheet.

Air_Cooled_Nut

New Member
Joined
Oct 8, 2004
Messages
36
I have an Excel workbook that is used as a template. It contains references to other Excel workbooks (data links). One example:

Code:
=VLOOKUP($A18,'C:\Work\program worksheets\[AD Mktg-Com 2012 R2.xlsb][B][COLOR="#FF0000"]2012[/COLOR][/B]'!costcenter_PGE,AS$5+1,FALSE)

Here it is referencing the named range "costcenter_PGE" in the sheet "2012". If I open this template the references don't change (specifically, the sheet name), which is good as they aren't supposed to anyway.

Now, I have a master workbook that builds other end user workbooks based upon the template. The master opens the template, makes some simple changes (delete columns, for example), then saves the template as a workbook for the end user in a different directory with a different file name. The file PATH for the external references are updated to point to the same directory that the end user workbook is saved to using VBA (Workbook method .ChangeLink)

That all works well and good (I step through the code and watch the changes happen). However, when I open the newly created end user workbook the external references point to the first worksheet in the external reference by default, for example:

Code:
=VLOOKUP($A18,'C:\TESTING\program worksheets\[AD Mktg-Com 2012 R2.xlsb][B][COLOR="#FF0000"]Instruct[/COLOR][/B]'!costcenter_PGE,AS$5+1,FALSE)

Instead of it looking at the "2012" sheet it defaults to the first sheet in the workbook, "Instruct". The workbooks that are being referenced have multiple sheets because they are filled in by other people and may change. So it's not feasible to have a workbook with a single sheet in it (though that would be very nice, I admit).

What's happening? Why does Excel change the original, specified sheet of "2012" and replace it with the first sheet of the workbook, "Instruct"? How can I make sure it doesn't default to the first sheet of the referenced workbook so it keeps the sheet name originally given?
Thanks!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I have a doc where I use this formula in the spreadsheet:
Rich (BB code):
=VLOOKUP($J$6,INDIRECT(SS_Ref),2,FALSE)
where SS_Ref is defined as:
="[Customer_Data.xlsx]User_Data!A:D"
where I have the Customer_Data auto open when Excel is launched and is a hidden workbook - so once installed the user never sees it

I tried a variety of Indirect etc with the Customer_Data in a cell on the page and couldn't quote seem to get that to work.

Your example might look something like this:
Rich (BB code):
=VLOOKUP($A18,INDIRECT(External_ref),AS$5+1,FALSE)

where you created a named range "External_Ref" and define that range as:
"
'C:\Work\program worksheets\[AD Mktg-Com 2012 R2.xlsb]2012'!costcenter_PGE"

although if multiple users need to either make sure that source is always in the same place.

You could write a macro to check that the doc is in the correct place or update the named range given a user supplied file name and location.

I went with the hidden workbook as it was simpler for multiple users across Mac and Windows versions of Excel - and allows me to control who as the customer data file installed on their system.
 
Upvote 0
Huh...that's a clever way to do it. The option I was thinking of would be a global find/replace using Excel's built-in function (it'd be fast) in the workbook open event. I think I like your way much better :cool: It's going to require me to do some rework in a test workbook (in a day or three). Yes, the referenced workbooks would all reside in the same directory -- no need for me to have the referenced workbooks opened, even if hidden.

I'll report back my results but I think this should work (I hope!).
 
Last edited:
Upvote 0
I have a doc where I use this formula in the spreadsheet:
Rich (BB code):
=VLOOKUP($J$6,INDIRECT(SS_Ref),2,FALSE)
where SS_Ref is defined as:
="[Customer_Data.xlsx]User_Data!A:D"
where I have the Customer_Data auto open when Excel is launched and is a hidden workbook - so once installed the user never sees it
...


For INDIRECT to work, the referenced external workbook must also be opened according to the Help file:
If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
I confirmed this to be true. But what you suggested gave me an idea...

As a test, I entered this into my cell's formula
Code:
=VLOOKUP($A19, TESTING, BJ$5 + 1, FALSE)
and created a named variable, TESTING, to be
Code:
='C:\ExcelWork\Round 2 - final\program worksheets\[P&E 2012 R2.xlsb]2012'!costcenter_PGE
(exactly as displayed, no surrounding quotes!)
That worked quite nicely!

Thanks mcwsky09 for the help!
 
Upvote 0
Due to time constraints and additional coding involved during the user workbook creation process I've defaulted to using the below code to 'fix' the issue:
Code:
Function FixFirstSheetNameInFormulas(sWrongName As String, sCorrectName As String)
'For some reason the external links will default to pointing to the first sheet of the external workbook.
'This very specific function will find a linked sheet name and replace it with the proper name using Excel's built-in Find/Replace function.

On Error Resume Next  'Not worried about errors for this function
ThisWorkbook.ActiveSheet.Cells.Replace What:="]" & sWrongName & "'", Replacement:="]" & sCorrectName & "'", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

On Error GoTo 0  'Resume normal error checking
End Function

The function is called by the offending sheet's Worksheet_Activate() event. Luckily, once the text has been corrected, subsequent calls are not noticeable as there is no sWrongName value on the sheet. The replacement seems to 'stick' because if I save the workbook after clicking the offending tabs, quit, then re-launch the workbooks the required values remain.

Keeping subscribed to this thread in hopes someone can explain what's going on and propose a proper fix since my solution is more of a [effective] hack.
 
Upvote 0
So...changes had to be made so my above Find-n-Replace was scrapped. Long story made really short, the data links were made into named ranges (named references?) and that name was used in place of the actual filepath/name -- I followed mcwsky09's suggestion. This allowed my code to build the name and its definition for each end user workbook.

For example:
This formula
Code:
=VLOOKUP($A18,[i]'C:\Work\program worksheets\[AD Mktg-Com 2012 R2.xlsb]2012'!costcenter_PGE[/i],AS$5+1,FALSE)
now looks like this
Code:
=VLOOKUP($A18,[i]mktg_com1costcenter_PGE[/i],AS$5+1,FALSE)
and the named range is built like so:
Code:
Dim lYear1 as Long, lYear2 as Long, wkbkNew as Workbook, sWkbkName as String, sDefinedName as String, sRangeName as String, sSaveToPath as String
With wkbkNew
     ...
     .Names.Add Name:=sDefinedName & "1" & sRangeName, [I]RefersToR1C1[/I]:="='" & sSaveToPath & "[" & sWkbkName & " " & lYear1 & "-1.xlsb]" & lYear1 & "'!" & sRangeName  'Year 1 named reference
     .Names.Add Name:=sDefinedName & "2" & sRangeName, [I]RefersToR1C1[/I]:="='" & sSaveToPath & "[" & sWkbkName & " " & lYear1 & "-1.xlsb]" & lYear2 & "'!" & sRangeName  'Year 2 named reference
     ...
End With
Notice how the name is added, by using RefersToR1C1, and how it's built. When looking at the Name in the Name Manager (from the Formulas group in the Ribbon) the Referes to: looks like this:
Code:
='C:\ExcelWork\Round TEST 2\[AD Mktg-Com 2012-1.xlsb]2012'!costcenter_PGE
There are NO surrounding quotes, this is important. Done!
 
Upvote 0
(Users are only allowed 10 minutes after a posting to edit it -- fail! :rolleyes: Thus this post.)
This allowed me to build the references as needed, with greater flexibility, and also insured naming consistency. The actual sheet that is being referenced no longer changes -- it can't -- since it's hard-coded in the named reference. Because there are multiple years and "rounds" throughout the year this method also insures that the workbook will reference the correct external workbooks since, again, the path in the named reference cannot easily change at Excel's whimsy.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top