Changing file names within worksheets

**saturngirl** · 03-25-2009, 03:07 PM

Hello, I am hoping someone may be able to help me.

I am going to go my best to explain what I am looking to do....

my formula is as follows:
=(VLOOKUP(A7,'[Monthly AR Analysis - Feb09.xls]AR - Current'!$A$1:$I$153,8,FALSE))

I will be using this for formula on about 15 different worksheets ... my issue is the file name changes every month and currently I have to go in and do a find and replace, I would like to have one main page where I could change the file name month and it will change all the rest.

ie: Monthly AR Analysis - Feb09.xls next month changes to Monthly AR Analysis - Mar09.xls .... So basically each month I want it to pick up the new file that has been saved with the new name.

So what I would want is to be able to have a cell that I could change the Feb09 to Mar09 and then it would do the same to all the worksheets

Is this possible?

I attempted to use CONCATENATE to change file name and it worked but will not work within the Vlookup....

I would appreicate any advise .... thanks in advance.

**NBVC** · 03-25-2009, 03:11 PM

Perhaps

=VLOOKUP(A7,INDIRECT("'[Monthly AR Analysis - "&X1&".xls]AR - Current'!$A$1:$I$153"),8,FALSE)

where X1 would hold the variable data

**saturngirl** · 03-25-2009, 03:27 PM

hmmm that doesn't seem to have worked as I got an error message

**saturngirl** · 03-25-2009, 03:30 PM

I tried again and it worked thank you so much

**NBVC** · 03-25-2009, 03:36 PM

If you are satisfied with the solution provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**guochingsu** · 08-18-2012, 02:54 AM

Hello,

I encountered simillar problem in which I need to get "total daily sales" record from daily sales file.
The daily sales file is saved as this format: 101mmdd.xls.

And I wish to get the "total daily sales" record from the revenues.xls file by typing in 101mmdd on cell "C1" like entering 1010818 on cell "C1" to get the August 18 "total daily sales" record. I tried to type the formula as follows:

='C:\Users\user\Data\[&c1&.xls]Daily_WorkSheet'!B4

or

='C:\Users\user\Data\["&c1&".xls]Daily_WorkSheet'!B4

But both won't work. ...

Please kindly help...

**arlu1201** · 08-18-2012, 04:46 AM

guochingsu,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.