Hyperlinking Using Avlookup Using Multiple Files

[lorenmark]

Hello!

I want to start off by thanking anyone who can help with this excel problem. I greatly appreciate your time and effort and know you could easily do something else. I'm typically pretty good at this stuff, but I can't get this one figured out.

Every morning I download three new .csv files. The names of the .csv files don't change but the data in each file changes daily and may not necessary be in same place either. These .csv files are my 'source' data files.

In a separate excel file, I'm looking to find a certain string of characters and hyperlink to it. So, if I'm looking for "cheeseburger" I need to look in all three .csv files to find the corresponding data with cheeseburger.

The problem I'm having is hyperlinking to where ever the cheeseburger string of characters may be. I get the 'cannot open the specified file' error.

I've also tried entering the exact path for food1.csv along with the sheet but it gives me the same error, ex:
"[C:\file folder\food1.csv]'food1'!$J$13:$BB$60000"

This is my formula:

=CONCATENATE(IF(ISNA(AVLOOKUP($A19,food1.csv!$J$13:$BB$60000,1,0))
,"",HYPERLINK(AVLOOKUP($A19,food1.csv!$J$13:$BB$60000,1,0))),
IF(ISNA(AVLOOKUP($A19,food2.csv!$J$13:$BB$60000,1,0)),"",
HYPERLINK(AVLOOKUP($A19,food2.csv!$J$13:$BB$60000,1,0))),IF(ISNA
(AVLOOKUP($A19,food3.csv!$J$13:$BB$60000,1,0)),"",HYPERLINK(
AVLOOKUP($A19,food3.csv!$J$13:$BB$60000,1,0))))

Assume $A19 is Cheeseburger


Many thanks to anyone who can help, Loren.

[ravishankar]

Hi
try this syntax

"'C:\file folder\[food1.csv]food1'!$J$13:$BB$60000"

Ravi