Variable File Name in Formula

[glenn18d]

Is there a way to have a filename in a formula changed dependant upon the contents of another cell?? The formula is as follows:
=VLOOKUP(C5,'2004.csv'!$A$1:$B$3472,2)
i need to be able to change the 2004.csv, i can go through and do this manually, but i want to have a cell where the user can enter the year and it will select the correct filename.i.e. 2003 would lookup 2003.csv.
Regards

Glenn

 

[starl]

Not easy to explain, but I'll try. You CAN build the workbook name - Look at this:
=VLOOKUP(B2,INDIRECT(CONCATENATE("[",B2,".xls]Sheet1!$A$1")),1,FALSE)

it's a very simplified example, but look at INDIRECT and CONCATENATE.
CONCATENATE is part 1 of the method - it's used to put together your workbook name.
B2 is the cell containing the year.
INDIRECT is what makes it work.

 

[starl]

oh - it's the SECOND B2 that's the cell that contains the workbook name...
the first B2, the lookup value.. I was just cheating - like I said, it's a simplified example

 

[glenn18d]

Starl, thanks for the quick response!
I changed the procedure slightly so it picked up the sheet name (i thought it would be best to store all the years data in the one workbook).
Anyway im getting a #ref! error... i thought this may have been because u need the external workbook open? i don't really want this to happen. however i opened the workbook and still got the same result...
maybe there is something wrong with my formula... ive posted it incase it is something obvious.
=VLOOKUP(C15,INDIRECT(CONCATENATE("G:\data\APA - Workpapers\[ASXP.csv]","Home!I10","!$A$1:$B$3472"),FALSE),2)

 

[starl]

=VLOOKUP(C15,INDIRECT(CONCATENATE("G:\data\APA - Workpapers\[ASXP.csv]","Home!I10","!$A$1:$B$3472"),FALSE),2)

little difficult for me to tell.. but it loooks like your sheet name is based on a cell reference - Home!I10.. in that case, get rid of the quotes - you don't want the string, you want the cell value.

Here's a hint on troubleshooting functions within formulas - place your cursor in the function (in this case CONCATENATE) and click the + sign by the formula bar. It'll help you breakdown just that formula.

Well - I'm going to bed now - if you still have probs, send me the workbooks and I can look at them in the morning.

 

[glenn18d]

Thanks for the tip on the formula lookup, after i took out the " " it was displaying the right value (2004) from the home worksheet however i was still getting the ref value from the formula result.... i tried a few different things but am stumped...
which is the best method to send the files to you?

 

[glenn18d]

Got it working.... Thanks for the help starl...much appreciated... i would have never worked it out...

 

[glenn18d]

thought i had it working Well it does but i need to have the workbook its looking up to be open in order for the value to be returned otherwise i just get #ref!
this is the formula im using:
=VLOOKUP(C15,INDIRECT(CONCATENATE("[ASXP.XLS]",Home!$I$10,"!$A$1:$B$3472"),TRUE),2)
the home value is a year, and there are tabs in the asxp workbook for the relevant years.

 

[Rangeraziz]

Hi Glenn, i am going through same problem trying to look for cell value in the file that is not open and file name is in cell and changes row to row can you help

 

[gurkan]

thanks for the tip starl