Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoiding cells being included in a chart | Excel Programming | |||
Avoiding cells being included in a chart | Charts and Charting in Excel | |||
Avoiding Calculating Cells on the Status bar | Excel Discussion (Misc queries) | |||
Avoiding empty cells showing as zero in line chart without na() | Charts and Charting in Excel | |||
AVOIDING DUPLICATES IN A RANGE OF CELLS | Excel Discussion (Misc queries) |