In Excel can you easily change the File name that a formula is pointed to

Copper Contributor

I've created an Excel file that has a bunch of tabs for different things.  One is my Bill of Materials.  I was trying to create a 2nd Excel file that would be my Purchase Request form without all the unnecessary tabs to make it stream lined for my purchaser.  I know how to point to another file and make it work.  What I was wondering is if there is a way to create a blank form and be able to type in just the reference file name in a single block and that would repoint all the formulas to the proper location.

 

Example:

 

='[xxx Internal Cost REV0.xlsx]Tab1'!$A$31    Is doing it the normal way

 

I want to use an unused cell that I would type the actual new file name into to replace "xxx Internal Cost REV0.xlsx"

 

So I was picturing something like:

 

='[Z3]'Tab1!$A$31

 

and cell Z3 I would type in "xxx Internal Cost REV0.xlsx"

 

Is this even possible?

1 Reply

Hi! I would prefer to create a Userform in Excel VBA with a combo box (Control) and content will be the certain cells or xlsx-Links that you will point to.

I found something on stackoverflow, check this pls.: https://stackoverflow.com/questions/23110442/excel-vba-listbox-links https://stackoverflow.com/questions/23110442/excel-vba-listbox-links

Hope that helps. Greets, Eva.