Reference different sheet for hyperlink

I'm wondering if someone could help me. I'm working on a formula to get the the path of a picture on a different sheet in a workbook. I am trying to get use the hyperlink function to reference that cell on the the other sheet. It looks right but I get an error saying that it cannot open the specified file. This is what my formula looks like:

=HYPERLINK(ADDRESS(4,12,2,,"Sheet 1"),2)

I need it to actually get the actual text value of the cell I am trying to reference. How would I do that?

Thanks.

I want the file path in the cell I'm trying to reference. I want the actual text value. I have a sheet that needs to have a bunch of hyperlink I'm trying to make it fast so i do not have to click right each cell and click hyperlink 400 times.

Thanks.

Originally Posted by Andy Pope

=HYPERLINK(INDIRECT(ADDRESS(4,12,2,,"Sheet 1")),2)

I tried that and I get an #REF! error.

That worked. What I really need it to do is able to drag the forumula from left to right (across the row) and let excel automatically update the cell references. I can attach the sheet if needed.

Originally Posted by Andy Pope

=HYPERLINK(INDIRECT(ADDRESS(4,COLUMN(L:L),2,,"Sheet1")),2)

That one didn't correctly. It hyperlinks but not to the right location. I have attached the document. Column AM has the hyperlinks. Sheet1 contains the paths to the picture. I want to be able to drag across the hyperlink section and it update the link by going down a row in the sheet with the file path's

Thanks

Originally Posted by Andy Pope

so you want the single column list to fill 4 cells across then move down and fill next 4?

Yes, when I drag the formula across to the next cells to hyperlink, It needs to get the file path from the other sheet by moving down the list. So I as I drag across the list, it needs to get the file path by moving down the list with the file paths.

Thanks.