Changing a file name in a formula with data validation

[acellis9]

I'm sorry if this is a duplicate post. I looked around but was unable to find an answer for my problem. I'm attempting to use a drop-down data validation menu to change my source file in a formula. For example: cell B1 contains a formula linking to a cell in another workbook, ='[Sales_16-Washington.xls]Area2'!$O$25 where "16" is the week number.

I want for "16" to link to a drop-down menu where I can select other numbered weeks and have the data repopulate. I already have the week list/menu created in cell A1, I just don't know how to get excel to recognize that "Sales_$A$1-Washington.xls" is a cell reference within a file path.

I would like to be able to change the sheet reference as well, also using another drop-down menu.

So by changing two drop-down's, the formula would change to ='[ABC_23-Washington.xls]Area5'!$O$25

I really appreciate any help you might be able to offer. Thanks for taking a look!

 

[Jerry Sullivan]

Hello and Welcome,

There might be a better way to do this, but one option is to use the =INDIRECT function. Using ampersands to separate Text and Cell References, you build your formula.

=INDIRECT("'[Sales_" & A1 & "-Washington.xls]Area2'!$O$25")

One problem with using INDIRECT to build a reference to another workbook is that links to a workbook include the File Path when the linked workbook is closed (but not when it is open).

If you know your linked workbook will always be open or always closed when you use the workbook with your Validation List choices, that isn't too hard. Otherwise your approach would need to handle both conditions.