Convert cells to links

**vbhal** · 02-16-2011, 07:06 PM

I have a bunch of plain text cells pointing to different spreadsheets i.e. a cell could have 'D:\Me\Jan-11\[myfile1.xlsx]Sheet1'!$G$64. I actually created this string through the concatenate function, but then got stuck. I can copy and paste the value of cell (concatenate(a1,a2,a3)) into a new cell. But is there a way that I can actually after the paste add = to it and hit enter so that it retrieves the value?

So for example, I can take cell with this 'D:\Me\Jan-11\[myfile1.xlsx]Sheet1'!$G$64 make this ='D:\Me\Jan-11\[myfile1.xlsx]Sheet1'!$G$64 and click enter for each cell?

I have a bunch of these cells and the strings are dynamic and subject to change, so I'm looking for a way to automate this.

Any help is greatly appreciated.

**davesexcel** · 02-16-2011, 09:21 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!
I assume a Hyperlink formula can be used

**vbhal** · 02-17-2011, 10:02 AM

Thanks Dave I can see that my explanation was confusing. As you can see in the uploaded spreadsheet, I just would like Cell D5 to pull the value that it the path name is pointing too. I couldn't think of a way to do it either in Excel or VBA. Any help is appreciated.

**JBeaucaire** · 02-17-2011, 11:43 AM

Try this macro:

Please Login or Register  to view this content.

**vbhal** · 02-19-2011, 08:23 PM

Fantastic, thanks taking the time to help! Really appreciated

**JBeaucaire** · 02-20-2011, 09:09 AM

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

**vbhal** · 02-24-2011, 12:59 PM

Thanks so much, I was going to mark this but then tried to put this in a function

For example I used:

Function MakeFormulas(RNG As Range) as Range
MakeFormulas.Formula = "=" & RNG.Text
End Function

And I keep getting #Values, apologies as I know this is probably a basic question as I haven't written functions before and am a complete beginner to VBA syntax. Is it that I can't use the .formula here in the function?

Thanks again