Link cell data, hyperlinks, and formatting from Workbook 1 to Workbook 2 in Excel 2013

[EricJToll]

I need to create a workbook, called “Roundup.xslm,” that links back to cells in the primary workbook, “Articles.xlsm.” Each record (row) has six columns (A:F). One of the columns is hyperlinked to a file or Web URL.

I need to link the rows in Roundup to the records in Articles so that when the field (column:row) with the hyperlink displays in Roundup, it includes the hyperlink from the source field in Articles.

I successfully created the Roundup workbook so that the unformatted cell content from each record in Articles appears in the same field in Roundup. I am using Excel 2013, 32 bit.

The formula ='[Articles.xlsm]06-10-14'!$A$7 works in column A, Row 7 of Roundup, and the formula adjusted for each column and row for field (columns) B:F displays the correct information like this:

[TABLE="width: 639"]
<TBODY>[TR]
[TD]A
ET

[/TD]
[TD]B
P

[/TD]
[TD]C
4 Casino Developments in Process in AZ

[/TD]
[TD]D
P&D

[/TD]
[TD]F
Background; use Oro Valley

[/TD]
[/TR]
</TBODY>[/TABLE]
The problem is that the original data are:

ET P 4 Casino Developments in Process in AZ P&D Tribe: 928-475-2361

That's an active hyperlink to a file or URL in Column C. Hyperlinks are always in Column C, and occasionally in Column F.

What is the formula to use so that the data comes from Articles workbook into the Roundup workbook with the hyperlink intact? (If formatting could transfer as well, it would be a bonus).

 

[jbeaucaire]

Formulas can be used to display information from a remote cell, either calculated information or textual.

Formulas typically cannot display any formatting characteristics from the source cells, not color, font, hyperlinks, none of it. The formula must do all the work in a cell to create its own result and the formatting/hyperlinks in that cell are controlled locally, in that cell.

So for formatting, no suggestions.

But there is hope for the Hyperlinks, and that is to NOT use the hardcoded hyperlinking (CTRL-K) to insert hyperlink into a original cells. If instead you resort to the HYPERLINK() formula to create the hyperlink from text in some logical manner, then your remote workbook may be able to read the values from the original workbook and also construct that same working HYPERLINK() formula.

 

[EricJToll]

Thanks for the answer.

The Hyperlink() command looked like it could be the answer, but it won't carry the data from Workbook B to Workbook A as a hyperlink. The hyperlink displayed in the cell, but it's not a link to the URL. I even tried with the friendly name to see if that would carry the active link, but it doesn't.

Darn. Guess, I'm just stuck with copy-and-paste.

I appreciate your rapid response.