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!

[Cutter]

Hi and welcome to the forum.

What you need is the INDIRECT() function (if the target file will be open)
or
the INDIRECT.EXT() function which can access closed files. This function is part of the free add-in called MoreFunc.
Available here:
http://xcell05.free.fr/morefunc/english/

Here are links for the function explanations:
http://www.excelfunctions.net/Excel-...-Function.html
http://xcell05.free.fr/morefunc/engl...direct.ext.htm

[acellis9]

Thanks, Cutter. I've downloaded and installed the INDIRECT.EXT function, but am unsure how to write the formula to incorporate the data validation within the file path. Could you please give me an example?

I'm assuming I will have to break the file path into sections and connect them with &'s. Is this correct? Something like =indirect.ext('["Sales_"&"$A$1"&"-Washington.xls"]$B$2'!$O$25) where A1 is the week drop-down, B2 is the drop-down to select which sheet, and O25 is the cell data I'm wishing to pull from Sales_16-Washington.xls

[Cutter]

Have a look at this very similar thread from earlier today.

http://www.excelforum.com/excel-prog...to-a-path.html

[acellis9]

I am attempting to copy your instructions from the other tread, but am getting a #REF! error.

[Cutter]

Show us your formula AND the correct full path to the file.

[acellis9]

Thank you so much for your help.

The correct full file path would be: ='D:\Projects\East\[Sales_16-Washington.xls]Area2'!$O$25

I got the formula to work using =INDIRECT.EXT("'D:\Projects\East\[Sales_"&Criteria!$A$1&"-Washington.xls]Area2'!$O$25")

I'm not using a drop-down for the sheet selection at this point.

I really appreciate your help!

How do I mark this "Solved?"

[acellis9]

I'm having another issue...
I'm trying to compare 2011 and 2010 data on the same sheet, but when I copy the formula to an adjacent cell, changing only the year of the file path from:

=INDIRECT.EXT("'D:\Projects\2011\East\[Sales_"&Criteria!$A$1&"-Washington.xls]Area2'!$O$25")
to
=INDIRECT.EXT("'D:\Projects\2010\East\[Sales_"&Criteria!$A$1&"-Washington.xls]Area2'!$O$25")

and I close the original 2011 file and open the 2010 file, the 2011 numbers revert to 0's while the 2010 numbers calculate to the correct values.

[acellis9]

The file has also become extremely slow. Is this a product of using the INDIRECT.EXT function? And is there a solution?

[Cutter]

Sorry for late response - busy with other things yesterday.

I'm afraid I don't have a definitive answer for you with regard to the formula producing 0's when the source file is closed. But I would suggest you have a look at the link for the INDIRECT.EXT() again (http://xcell05.free.fr/morefunc/engl...direct.ext.htm) and specifically the section "MODE" Argument as well as the Remarks that immediately follow.

As for slowing things down - INDIRECT() is one of the volatile functions (see here for others: http://www.decisionmodels.com/calcsecretsi.htm) so it will slow things down if there are a lot of volatile functions in play but I wouldn't expect a couple to have much of an impact. Do you have any others?

Try putting a trigger on the formulas to test:

Please Login or Register  to view this content.

Change the AA1 cell to any other cell not currently in use and place a 1 in the cell to have the calculations take place and remove the 1 when you don't need the calculations to happen.
I've used that method with a bunch of SUMPRODUCT()'s in order to control when they calculate.

[brewcitybiz]

Hey Cutter,

After reading this thread, I have a similar problem, I want to do the same thing as acellis9; however, I want to add a VLOOKUP to find an account value in the file I am looking for. Is there a way to do this?

Thus the formula I have now (without your advice above about INDIRECT) =

=VLOOKUP(E15,'[TB-SMID 7.11.xls]Sheet3'!$A$1:$N$27,12,FALSE)

E15 is a Account Number from the Trial Balance that the formula searches for in the TB it is looking up. I would like to make the "7.11" part of the formula a cell that can easily be changed. Any help would be great, thanks so much.

[anup_keshri]

Hi Cutter,
I want to do exactly what acellis9 was doing. I used the INDIRECT.EXT function inside VLOOKUP function as argument. But it did not work.
I had written INDIRECT.EXT("'C:\Users\AnupKeshri\Desktop\TEST["&$H$4&"IM.xlsx]Sheet1'!$B$7")

Please help me.

[arlu1201]

anup_keshri,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

[anup_keshri]

ok thanks arlu