Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting Hyperlinked data
I have an application that pulls data into cells from other cells based upon
selection from a drop down box. The original data has some information that is hyperlinked (URL). This works fine except that when the data is copied, the hyperlink is lost. Is there some way to ensure that the hyperlink is retained? Thanks for any assistance .. greatly appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting Hyperlinked data
Here is a little trick:
Say your list is in H1 thru H40 and in D1 you have your typical Data Validation pull-down. In another cell (say D2) enter: =IF(1=2,HYPERLINK("",""),D1) Obviously 1 does not equal 2 so the dummy hyperlink is never returned. D1 is always returned. Excel will put a real, click-able, link in D2 cell -- Gary''s Student gsnu200701 "RBeau" wrote: I have an application that pulls data into cells from other cells based upon selection from a drop down box. The original data has some information that is hyperlinked (URL). This works fine except that when the data is copied, the hyperlink is lost. Is there some way to ensure that the hyperlink is retained? Thanks for any assistance .. greatly appreciated |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting Hyperlinked data
Why not just:
=hyperlink(D1) Gary''s Student wrote: Here is a little trick: Say your list is in H1 thru H40 and in D1 you have your typical Data Validation pull-down. In another cell (say D2) enter: =IF(1=2,HYPERLINK("",""),D1) Obviously 1 does not equal 2 so the dummy hyperlink is never returned. D1 is always returned. Excel will put a real, click-able, link in D2 cell -- Gary''s Student gsnu200701 "RBeau" wrote: I have an application that pulls data into cells from other cells based upon selection from a drop down box. The original data has some information that is hyperlinked (URL). This works fine except that when the data is copied, the hyperlink is lost. Is there some way to ensure that the hyperlink is retained? Thanks for any assistance .. greatly appreciated -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting Hyperlinked data
You are absolutely correct. I must have had a Benedryl moment.
-- Gary's Student gsnu200701 "Dave Peterson" wrote: Why not just: =hyperlink(D1) Gary''s Student wrote: Here is a little trick: Say your list is in H1 thru H40 and in D1 you have your typical Data Validation pull-down. In another cell (say D2) enter: =IF(1=2,HYPERLINK("",""),D1) Obviously 1 does not equal 2 so the dummy hyperlink is never returned. D1 is always returned. Excel will put a real, click-able, link in D2 cell -- Gary''s Student gsnu200701 "RBeau" wrote: I have an application that pulls data into cells from other cells based upon selection from a drop down box. The original data has some information that is hyperlinked (URL). This works fine except that when the data is copied, the hyperlink is lost. Is there some way to ensure that the hyperlink is retained? Thanks for any assistance .. greatly appreciated -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting Hyperlinked data
Thanks to both of you for the quick response! ... now took your advice ...
here is the formula: =IF('Investment Summary'!$H$4=1,HYPERLINK(AN3),IF('Investment Summary'!$H$4=2,hyperlink (BM3),IF('Investment Summary'!$H$4=3,HYPERLINK(CL3),IF('Investment Summary'!$H$4=4,HYPERLINK(DK3),HYPERLINK(EJ3))))) The hyperlink is now there and it wasn't before .. so thanks. However, when I click the link in the cell that the original was copied to, it says "cannot open the specified file". .... so the link works in the original cell, but it doesn't in the cell it was copied into. Any idea why that would happen? Thanks again! "Gary''s Student" wrote: Here is a little trick: Say your list is in H1 thru H40 and in D1 you have your typical Data Validation pull-down. In another cell (say D2) enter: =IF(1=2,HYPERLINK("",""),D1) Obviously 1 does not equal 2 so the dummy hyperlink is never returned. D1 is always returned. Excel will put a real, click-able, link in D2 cell -- Gary''s Student gsnu200701 "RBeau" wrote: I have an application that pulls data into cells from other cells based upon selection from a drop down box. The original data has some information that is hyperlinked (URL). This works fine except that when the data is copied, the hyperlink is lost. Is there some way to ensure that the hyperlink is retained? Thanks for any assistance .. greatly appreciated |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting Hyperlinked data
What do you have in those cells?
Do they look like: http://www.google.com When I had stuff like that in my cells, it worked fine. Ps so did this: =HYPERLINK(IF('Investment Summary'!$H$44,EJ3, CHOOSE('Investment Summary'!$H$4,AN3,BM3,CL3,DK3))) If the only options for H4 were counting numbers (1, 2, 3, ...). ====== Ps. If http:// is missing from those cells, you can add it or do it in the formula: =HYPERLINK("http://" & IF('Investment Summary'!$H$44,EJ3, CHOOSE('Investment Summary'!$H$4,AN3,BM3,CL3,DK3))) RBeau wrote: Thanks to both of you for the quick response! ... now took your advice ... here is the formula: =IF('Investment Summary'!$H$4=1,HYPERLINK(AN3),IF('Investment Summary'!$H$4=2,hyperlink (BM3),IF('Investment Summary'!$H$4=3,HYPERLINK(CL3),IF('Investment Summary'!$H$4=4,HYPERLINK(DK3),HYPERLINK(EJ3))))) The hyperlink is now there and it wasn't before .. so thanks. However, when I click the link in the cell that the original was copied to, it says "cannot open the specified file". .... so the link works in the original cell, but it doesn't in the cell it was copied into. Any idea why that would happen? Thanks again! "Gary''s Student" wrote: Here is a little trick: Say your list is in H1 thru H40 and in D1 you have your typical Data Validation pull-down. In another cell (say D2) enter: =IF(1=2,HYPERLINK("",""),D1) Obviously 1 does not equal 2 so the dummy hyperlink is never returned. D1 is always returned. Excel will put a real, click-able, link in D2 cell -- Gary''s Student gsnu200701 "RBeau" wrote: I have an application that pulls data into cells from other cells based upon selection from a drop down box. The original data has some information that is hyperlinked (URL). This works fine except that when the data is copied, the hyperlink is lost. Is there some way to ensure that the hyperlink is retained? Thanks for any assistance .. greatly appreciated -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting Hyperlinked data
Dave,
Thanks again ... got it solved ..to net it out .. .have a drop down that selects say a "catelogue".. when that is selected it populates the "solutions" in the next drop down. It is there that i am looking to get the hyperlink. Your first suggestion works for one level, but didn't for the next. However, by using the fully syntax - hyperlink (URL, cell) ... it will work .. and does. Thanks for your suggestions .. .got me where i needed to be! "Dave Peterson" wrote: What do you have in those cells? Do they look like: http://www.google.com When I had stuff like that in my cells, it worked fine. Ps so did this: =HYPERLINK(IF('Investment Summary'!$H$44,EJ3, CHOOSE('Investment Summary'!$H$4,AN3,BM3,CL3,DK3))) If the only options for H4 were counting numbers (1, 2, 3, ...). ====== Ps. If http:// is missing from those cells, you can add it or do it in the formula: =HYPERLINK("http://" & IF('Investment Summary'!$H$44,EJ3, CHOOSE('Investment Summary'!$H$4,AN3,BM3,CL3,DK3))) RBeau wrote: Thanks to both of you for the quick response! ... now took your advice ... here is the formula: =IF('Investment Summary'!$H$4=1,HYPERLINK(AN3),IF('Investment Summary'!$H$4=2,hyperlink (BM3),IF('Investment Summary'!$H$4=3,HYPERLINK(CL3),IF('Investment Summary'!$H$4=4,HYPERLINK(DK3),HYPERLINK(EJ3))))) The hyperlink is now there and it wasn't before .. so thanks. However, when I click the link in the cell that the original was copied to, it says "cannot open the specified file". .... so the link works in the original cell, but it doesn't in the cell it was copied into. Any idea why that would happen? Thanks again! "Gary''s Student" wrote: Here is a little trick: Say your list is in H1 thru H40 and in D1 you have your typical Data Validation pull-down. In another cell (say D2) enter: =IF(1=2,HYPERLINK("",""),D1) Obviously 1 does not equal 2 so the dummy hyperlink is never returned. D1 is always returned. Excel will put a real, click-able, link in D2 cell -- Gary''s Student gsnu200701 "RBeau" wrote: I have an application that pulls data into cells from other cells based upon selection from a drop down box. The original data has some information that is hyperlinked (URL). This works fine except that when the data is copied, the hyperlink is lost. Is there some way to ensure that the hyperlink is retained? Thanks for any assistance .. greatly appreciated -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with selecting multiple ranges of data | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions |