Help with lengthy hyperlink

caj1980

Board Regular
Joined
Oct 23, 2013
Messages
107
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,327
Members
414,053
Latest member
Dual Showman

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