Avoiding auxiliary cells
 

Happy New Year to all readers.

I use Excel 2003.
I have a file containing a list of London station names.
I want to have it contain a list of hyperlinks to Wikipedia entries.
I have Column B filled with a title and a list of station names.
e.g. B2 = "Abbey Road"
I have Column C filled with a title and a list of formulae to calculate
Wikipedia entries. e.g.
C2 = ="http://en.wikipedia.org/wiki/"&SUBSTITUTE(B2," ","_")&IF(Y2<"","_DLR",IF(AD2&AF2<"","_railway", "_tube"))&"_station"
A few cells in Column C have exceptional values. e.g.
C28 = "http://en.wikipedia.org/wiki/Belmont_railway_station_(Sutton)"
I have Column D filled with a title and a list of entries. e.g.
D2 = ="HYPERLINK(""" & C2 & """,""" & B2 & """)"
I have Column A filled with a title and a Paste Special of values from
Column D. e.g.
A2 = HYPERLINK("http://en.wikipedia.org/wiki/Abbey_Road_DLR_station","Abbey Road")
I then search and replace HYPERLINK with =HYPERLINK in Column A.
e.g. I end with
A2 = =HYPERLINK("http://en.wikipedia.org/wiki/Abbey_Road_DLR_station","Abbey Road")
This has the value Abbey Road and clicking it opens the URL.

I think some simplification can be done.
I don't know how to have a cell formula evaluate to a formula.

To reiterate, I have
A2.formula = "=HYPERLINK("http://en.wikipedia.org/wiki/Abbey_Road_DLR_station","Abbey Road")"
[I have not bothered doubling quotes in this]
A2 = "Abbey Road"
B2 = "Abbey Road"
C2.formula = "="http://en.wikipedia.org/wiki/"&SUBSTITUTE(B2," ","_")&IF(Y2<"","_DLR",IF(AD2&AF2<"","_railway", "_tube"))&"_station""
C2.value = "http://en.wikipedia.org/wiki/Abbey_Road_DLR_station"
D2.formula = "="HYPERLINK(""" & C2 & """,""" & B2 & """)""
D2.value = "HYPERLINK("http://en.wikipedia.org/wiki/Abbey_Road_DLR_station","Abbey Road")"

--
Walter Briscoe

Avoiding auxiliary cells
 

On Friday, 3 January 2014 02:28:08 UTC+11, Walter Briscoe wrote:
Happy New Year to all readers.



I use Excel 2003.

I have a file containing a list of London station names.

I want to have it contain a list of hyperlinks to Wikipedia entries.

I have Column B filled with a title and a list of station names.

e.g. B2 = "Abbey Road"

I have Column C filled with a title and a list of formulae to calculate

Wikipedia entries. e.g.

C2 = ="http://en.wikipedia.org/wiki/"&SUBSTITUTE(B2," ","_")&IF(Y2<"","_DLR",IF(AD2&AF2<"","_railway", "_tube"))&"_station"

A few cells in Column C have exceptional values. e.g.

C28 = "http://en.wikipedia.org/wiki/Belmont_railway_station_(Sutton)"

I have Column D filled with a title and a list of entries. e.g.

D2 = ="HYPERLINK(""" & C2 & """,""" & B2 & """)"

I have Column A filled with a title and a Paste Special of values from

Column D. e.g.

A2 = HYPERLINK("http://en.wikipedia.org/wiki/Abbey_Road_DLR_station","Abbey Road")

I then search and replace HYPERLINK with =HYPERLINK in Column A.

e.g. I end with

A2 = =HYPERLINK("http://en.wikipedia.org/wiki/Abbey_Road_DLR_station","Abbey Road")

This has the value Abbey Road and clicking it opens the URL.



I think some simplification can be done.

I don't know how to have a cell formula evaluate to a formula.



To reiterate, I have

A2.formula = "=HYPERLINK("http://en.wikipedia.org/wiki/Abbey_Road_DLR_station","Abbey Road")"

[I have not bothered doubling quotes in this]

A2 = "Abbey Road"

B2 = "Abbey Road"

C2.formula = "="http://en.wikipedia.org/wiki/"&SUBSTITUTE(B2," ","_")&IF(Y2<"","_DLR",IF(AD2&AF2<"","_railway", "_tube"))&"_station""

C2.value = "http://en.wikipedia.org/wiki/Abbey_Road_DLR_station"

D2.formula = "="HYPERLINK(""" & C2 & """,""" & B2 & """)""

D2.value = "HYPERLINK("http://en.wikipedia.org/wiki/Abbey_Road_DLR_station","Abbey Road")"



--

Walter Briscoe

It looks to me as if you could simplify by just putting =HYPERLINK(C2, B2) in A2. (No need for any quotes.)

Howard