Using VLOOKUP with hyperlinks

[mikewallis7]

I have been using VLOOKUP to access information from .csv files for years using the format:

=VLOOKUP(B$3,‘file:ffffdddd.csv’#$Sheet1.$B$6:$F$350,2,0)
where ffff is a path/filename that never changes, and dddd is a date that is different for every file.

My problem is that I use this for thousands of .csv files. To use the formula for multiple dates I have to copy it to multiple cells and then manually change the dddd part of the formula in each cell. Then I copy and paste special to convert the result to a permanent number value.

What I would like to do is replace ffffdddd with a hyperlink that I can create with CONCATENATE() so it reads a date from column 1 and creates the necessary filename to replace ffffdddd.csv, something similar to

=VLOOKUP(B$3, (D$7)#$Sheet1.$B$6:$F$350,2,0)

where (D$7) contains the concatenated filename created from a date in D$1:
=HYPERLINK(CONCATENATE(path\filename_and_date_strings_created_from_D$1))

I have no problem creating a functioning hyperlink (i.e. the contents of (D$7)) that opens a .csv file, but I cannot figure out a way to implement the VLOOKUP() using it. I’ve tried concatenating $SHEET1.arrayrange directly to the filename but the hyperlink won’t work. Also played around with INDIRECT().

Any ideas?

[JohnSUN]

It seems to me that you are looking for a function INDIRECT(CONCATENATE(…))

[mikewallis7]

Found it! Everything explained below in great detail.

Each .csv file contains data corresponding to a particular date.
Each row in the .csv file contains the datum that I want to look up.
From each file, I want to import these data into a single row of my sheet.

My generic cell formula is:
=VLOOKUP(ccc$3,INDIRECT($AHrrr),2,0)
where

• ccc$3 references a column header in
  row 3 of my sheet that I use to
  search the rows of the .csv file -
  there are headers in columns B to AC
  of row 3.
• rrr is a row (below row 3) that I
  want to import data into from a
  unique .csv file.
• The .csv filename is constructed
  using a CONCATENATE() formula located
  in column AH of row rrr.

---

For each row rrr:

• Column A has a formatted date that
  displays like “Nov 08, 13”.

• Columns B to AC contain the INDIRECT
  formula listed above.

• Column AH contain the concatenation
  formula, which is shown below:

  =CONCATENATE(
  “'ffff”,
  TEXT(YEAR(Arrr),"####"),
  “-”,
  TEXT(MONTH(Arrr),“0#”),
  “-”,
  TEXT(DAY(Arrr),“0#”),
  “.csv’#$‘Sheet1’.$B$6:$F$350”
  )

where

• ffff is the generic path/filename for
  all the .csv files.

• The TEXT() formulas are used to
  create the end portion of each unique
  .csv filename, which is in the format
  yyyy-mm-dd, like 2013-11-08.

• The important data in each .csv file
  is contained in the range
  $B$6:$F$350.

---

In a row somewhere well below the last dated row, I keep the VLOOKUP() formula in column B and the CONCATENATE() formula in row AH, both cells highlighted in light blue so they are recognizable. Since there is no date in column A of that row:

• The VLOOKUP() cell displays as
  Err:502
• The date portion of the CONCATENATE()
  cell displays as 1899-12-30.

---

To use the formulas with new .csv files, I:

1. Create new dates in column A
   corresponding to the new .csv files.
2. If necessary, move the row
   containing the VLOOKUP() -
   CONCATENATE() formulas so it is well
   below the last added row. This row
   has to remain intact when the file
   is saved.
3. Highlight and copy the row
   containing VLOOKUP() - CONCATENATE()
   formulas from column B to AH.
4. Paste that row into all the new
   rows, starting in column B.
5. Acknowledge a Text Import dialog for
   each file - 10 new rows require 10
   OKs.
6. Copy the cells in the B column to
   all the other columns that have
   headers, in my case column C to AC.
7. Copy all the cells that were
   imported and use Paste Special to
   save them as numbers.
8. Erase the cells in the AH column
   since they are no longer needed.
9. Change all the light blue
   highlighting to clear.

---

Hopefully this should answer any and all questions!