choose which file to link to?

eye_in_the_sky

New Member
Joined
Jan 7, 2014
Messages
10
It seems like this should be possible, just not sure how to pull it off...

I have a workbook that is linked to multiple other workbooks but only one at a time. I have formulas all over the place pulling data from a file called bravo01.xlsx. I want to be able to change all of the formulas at once to pull info from the from the same cells but in bravo02.xlsx, or bravo03, or bravo04, etc...

Is there a way to do this with a data validation list where the user selects a file from the drop down which in turn changes all of the formulas to link to the new file chosen? I'm open to anything.

Thanks!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
Hi,
You can write a VBA routine for that. Sample code here (I'll let you take care of the validation list part, unless you need help with that do):

Code:
[COLOR="Navy"]Sub[/COLOR] TestIt()
[COLOR="Navy"]Dim[/COLOR] newLink [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    newLink = "C:\myTemp\Book22.xlsx"
    [COLOR="Navy"]Call[/COLOR] Change_Links(ThisWorkbook, newLink)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Sub[/COLOR] Change_Links(ByRef wb [COLOR="Navy"]As[/COLOR] Workbook, newLink [COLOR="Navy"]As[/COLOR] String)
    [COLOR="Navy"]Dim[/COLOR] arLinks [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] intIndex [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    arLinks = wb.LinkSources(xlExcelLinks)
        
        Application.DisplayAlerts = False
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
        [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] IsEmpty(arLinks) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]For[/COLOR] intIndex = LBound(arLinks) [COLOR="Navy"]To[/COLOR] UBound(arLinks)
                wb.ChangeLink Name:=arLinks(intIndex), newName:=newLink, Type:=xlExcelLinks
            [COLOR="Navy"]Next[/COLOR] intIndex
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] 0
        Application.DisplayAlerts = True

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

For what it's worth you can change the link without VBA by using the Edit Links dialog.


**Note that this code assumes that there is only ONE linked workbook and all links are changed to the new link you provide. It will go haywire if you add a second linked workbook - in that case, you'd have to resolve which links to change and how to change them to the correct new source.
 
Last edited:

eye_in_the_sky

New Member
Joined
Jan 7, 2014
Messages
10
Thanks for the reply. I'm never used VBA so that might as well have been in Portuguese!! I was hoping there was a way to do this with formulas but the more reading plus trial and error I do, the bleaker things are looking. I guess I need to figure out VBA!

Is there a way to list a formula as text in a cell and then refer to that cell from another cell with an if,then or conditional formatting?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
Okay, well you can use the Edit Links dialog as your "listbox" and do change the sources for links right there by clicking Change on the link you want to change. But you would need to browse to the file you want to change it to as the way to select the new source.

As for listing a formula as text in a cell, no no no. You can put the formula in the same cell as your IF/Then:

=IF([condition],A1+B1,C1+D1)

In the above formula, A1+B1 is one "formula" and C1+D1 is another.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

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
Top