Help with lengthy hyperlink

caj1980

Board Regular
Joined
Oct 23, 2013
Messages
108
I have an excel formula that outputs a text string that is a url (web address). I need the functionality of having this url clickable like a standard hyperlink. Normally I would just paste the results of the formula into the hyperlink target but the link is dynamic and changes every week and I don't want to recreate it every week. Kind of defeats the whole purpose. I need it to update dynamically with the formula.

When I use the HYPERLINK function it returns an error: "#VALUE!"
I assume this is because the text of the url is too long (over 200 characters).

Here is the formula:
=TEXT("https://tt.amazon.com/search?category=&assigned_group=",)&A11&"+Data+Tech&status=Resolved%3BClosed&impact=&assigned_individual=&requester_login=&login_name=&cc_email=&phrase_search_text=&keyword_bq=&exact_bq=&or_bq1=&or_bq2=&or_bq3=&exclude_bq=&create_date=&modified_date=&tags=&case_type=&building_id=&resolved_date="&TEXT(B11,"mm\%\2\Fdd\%\2\Fyyyy")&"%2C"&TEXT(C11,"mm\%\2\Fdd\%\2\Fyyyy")&"&search=Search!"

And the formula result:
https://tt.amazon.com/search?category=&assigned_group=IAD88+Data+Tech&status=Resolved%3BClosed&impact=&assigned_individual=&requester_login=&login_name=&cc_email=&phrase_search_text=&keyword_bq=&exact_bq=&or_bq1=&or_bq2=&or_bq3=&exclude_bq=&create_date=&modified_date=&tags=&case_type=&building_id=&resolved_date=12%2F04%2F2016%2C12%2F11%2F2016&search=Search!

<colgroup><col width="461"></colgroup><tbody>
</tbody>

I either need the formula result to function as a clickable hyperlink
OR
an adjacent cell with a clickable hyperlink that references the text string formula result as the target of the hyperlink
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,424
Messages
5,837,155
Members
430,479
Latest member
mamush200

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top