Reference filename based on cell contents

**Scrap74** · 01-06-2010, 05:27 AM

I can hardly think of a way to title this question easily to describe what I'm trying to achieve.

I have a program dumping heaps of .csv files that I am trying to analyse in Excel. The file names are consistent enough for me to use lists in Excel so that I can use something like = CONCATENATE(,,) to quickly and easily build the filename(s) with cells in Excel.

I would like to then use the information within this cell to reference the file name I am wanting to pull data from. In this case use =VLOOKUP().

The VLOOKUP looks like this:

=VLOOKUP($C$3,'(CONCATENATE(B15, " ", "53%","Fe", " Cutoff.csv"))'!$B$6:$J$50,F$1+1)

or

=VLOOKUP($C$3,'(B16)'!$B$6:$J$50,F$1+1)

where B16 would contain the name of the reference file

Hopefully this makes sense and someone has some ideas.

**sweep** · 01-06-2010, 05:33 AM

You need to make use of the INDIRECT function.

=VLOOKUP($C$3,INDIRECT(B16&"'!$B$6:$J$50"),F$1+1)

**Cheeky Charlie** · 01-06-2010, 05:34 AM

You can use indirect(), which takes a string as an argument and uses it as a reference to lookup, this is volatile (significant increase in workbook processsing load) and, more to the point, only works if the specified workbook is open at the time.

You can get around this second issue with indirect.ext:
http://www.*****-blog.com/archives/2...sed-workbooks/

I would recommend you get used to indirect first with an open workbook (you can use it on sheets in the same workbook to test it out) then expand to indirect.ext

hth

PS
the blog belongs to a guy named "richard"... it has been bowdlerised, replace the ***** with 'd i cks' (no spaces)

rofl