Ask Your Question
0

Using VLOOKUP with hyperlinks [closed]

asked 2013-11-10 18:41:18 +0200

mikewallis7 gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-14 12:18:29.844149

Comments

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

JohnSUN gravatar imageJohnSUN ( 2013-11-10 19:47:41 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2013-11-10 23:40:39 +0200

mikewallis7 gravatar image

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 ...
(more)
edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-11-10 18:41:18 +0200

Seen: 1,487 times

Last updated: Nov 10 '13