Concatenate to form a hyperlink

Can someone tell me if this is poosible with file names? That is, I have a main project list that I want to hyperlink to individual Excel project files. So, cell A1 has the project number, I made a hidden cell B1 that was just the path, then did:

=HYPERLINK(CONCATENATE(B1,"\",A1,".xls"))

- which then shows me what I want to see on the page (that is the whole link to the Excel file), but when I click on it it tells me "Cannot Open Specified File". I know I can easily individually set up hyperlinks as the files are created, but this is being created for a less experienced user for whom I want to make it as easy as possible.

Edit - also, just in case (but not expecting anything), I also tried to hyperlink without using the path and only doing the project name with the ".xls" concatenated, and that didn't work either.

Edit Again - the problem seems to be that I have a hyphen in the project name so it's expecting it to act as a minus sign (I only know this because I took the hyphen out and it worked fine, so I tried doing a fully typed link with the hyphen in it and that's when I got the minus sign error) - for some reason this is completely stumping me right now. Do I therefore have to remove the hyphens from my file names?

Any help is greatly appreciated.

Thanks,
Dave

I'm trying to complete this function and am having some issues.

I'm trying to create a link to allow me to click and view the results in a browser window to verify the non-profit status of a school. The school in the below example isn't in their database. Note: I used words from their name and city and put them in the string for this site to get these results - which work fine. My problem is getting the hyperlink to work as I have hundreds of these queries to give my volunteers to do.

Here's my data:

Col AG5 http://www.irs.gov/app/pub-78/search...s=false&names=
Col AE5 AAUW+Nursery
Col AH5 &nameSearchTypeAll=true&city=
Col AF5 Waukegan
Col AI5 &state=All...&country=USA&deductibility=all&dispatchMethod=search&searched.nameSearchTypeStarts=false&searched.names=school&searched.nameSearchTypeAll=false&searched.city=&searched.state=All...&searched.country=USA&searched.deductibility=all&searched.sortColumn=name&searched.indexOfFirstRow=4000&searched.isDescending=false&submitName=Search
________________

Here's my cell where I'm trying to get an active hyperlink I can click and launch my browser.

Col AJ5 =HYPERLINK(AG5&AE5&AH5&AF5&AI5)

This returns a #VALUE! error which is an active hyperlink that doesn't do anything when clicked in Firefox.
_________________

To prove the links should be working here's my results when I concatenate the data with: =CONCATENATE(AG5&AE5&AH5&AF5&AI5). This gives me that statement that there are no non-profits that fit my keywords: "There were no exempt organizations found matching the search values you entered. Please refine your search and try again." I have tried manually copying/pasting the final urls for several rows and get both positive and negative search results which is my intent. Here's the desired end URL:

http://www.irs.gov/app/pub-78/search...mitName=Search
______________

I tried removing the http:// and http://www. portions and including that in the concatenate string but, that isn't working either.

Your help would be greatly appreciated as we've got lots of schoolchildren to help. Thanks much!

Originally Posted by timkatje

While yes, the simplest method is to use concatenate, while spell out the word at all, just use the & symbol...

Example:
Column A = Scheme (aka, http://, ftp://, file://, etc)
Column B = Host (or file location, etc)
Column C = Port (leave blank if none)
Column D = Path (directory structure, leave blank if none)

Column E =HYPERLINK(A1&B1&C1&D1)

As simple as that. No separate but hidden concatenated columns.
And if you'd prefer to have the full URL hidden in the final column, and just have a hyperlink display "LINK", use this:

Column E =HYPERLINK(A1&B1&C1&D1, "LINK")

It doesn't have to be as broken down as this either, if all of your files are http for example, build it into your hyperlink command.

Column E =HYPERLINK("https://"&B1&"/"&D1)

The really cool thing about the first example is if those columns are completely blank (aka, no spaces or any other characters), they won't even show up in the final hyperlink.