Hyperlinks to same workbook using vlookup 1

[kjschmitz]

I have a Workbook that will constantly be growing as the years progress. One one sheet I have all of the project information collected in 25 columns and an ever expanding number of rows (currently over 1500). To assist the users I have created another worksheet within the notebook that they can use to look up individual 'records' (rows) in the table. I am using the vlookup function in an effort arrange the data and to make it more readable to the users.

This is all working great but I was hoping to add one final 'simple' tool: I want the users to be supplied with a 'hyperlink' to the original row (on a separate worksheet within the workbook) that contains all of the information. I thought I could use something like the following to get them there:

=HYPERLINK(CONCATENATE("file:///\\360_PPM\DATA\Shared\Staff Users\John Doe\Project Status Updates 2008.xls - 'Project List'!","A",(VLOOKUP($B$2,'Project List'!$C$3:$P$351,1,FALSE)-6998)),"More Information")

Note that the purpose of the vlookup in this case is to return the row reference (project numbers start on 7001 and on row 3 -- hence the '-6998' part) so I can add that using concatenate to the hyperlink.

Unfortunately it returns the following error:

"Cannot Open the Specified File"

I am probably making this much harder than it needs to be, and am hoping you have a much simpler solution. Thanks in advance, I appreciate it!

 

[xlbo]

VLOOKUP returns a value in a cell, not the row the information is on

Use MATCH rather than vlookup to get a row reference

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kjschmitz]

Thanks Geoff, that simplifies the first part, now how can I construct the hyperlink to go to that row reference? I must not be writing my hyperlink formula correctly...

 

[xlbo]

=hyperlink("[Full Path & File Name.xls]SheetName!CellRef", "More Information")

For CellRef, you may substitute:

ColumnLetter & MATCH formula result

eg

=hyperlink("[Full Path & File Name.xls]SheetName!A" & MATCH(x,y,0), "More Information")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kjschmitz]

Thank you so much, worked like a charm and my architects love it!