Excel question

[TillaMurphs]

We have all of our Benchmark finds (and DNFs) in an Excel spreadsheet. One column has the PIDs.

 

Is there a way to generate an URL from the PID by concatenating it onto the end of text string like “http://www.ngs.noaa.gov/cgi-bin/ds_mark.prl?PidBox=” and then ultimately end up with a clickable hyperlink in the spreadsheet?

 

I would like to be able to click from within the spreadsheet to go to the online datasheet.

 

Thanks,

 

The TillaMurphs

Edited January 25, 2010 by TillaMurphs

[+Yossarian]

Sure...copy this formula to the top cell in the column you want the hyperlinks to be in:

=HYPERLINK("http://www.ngs.noaa.gov/cgi-bin/ds_mark.prl?PidBox="&A1)

Then use F2 to change the A1 to reference the cell that has the PID for that row. If you wanted the text for the link to show the PID instead of the url you could use:

=HYPERLINK("http://www.ngs.noaa.gov/cgi-bin/ds_mark.prl?PidBox="&A1,A1)

(again, changing the A1's to the correct cell reference) Then select all the cells you want the hyperlinks to be in (including the top one you put the above formula in) and do ctrl-d to copy the formula down to the rest of the cells.

Edited January 25, 2010 by Yossarian

[+Black Dog Trackers]

I have done this same thing in excel with 2 columns; one for linking to NGS and the other for linking to GCBenchmarking.

[TillaMurphs]

Yossarian,

That worked great. Excellent instructions. Thank you so much!

 

BDT,

Good idea - I will also add a column for a GC link.

 

Thanks guys.