Generating hyperlink from formula outcome

Question

I'm having a problem with hyperlinks in Excel

=K1&TEXT(B5,"YYYY-MM-DD")&K2&MONTH(B5)&K3&B3&K4&TEXT(B4,"YYYY-MM-DD")&K5&MONTH(B4)&K6&1&K7&20&K8&YEAR(B4)&K9&B2&K10&YEAR(B1)

The idea is to create an automatically generated link for report for the week we are in. The formula works, the generated address works, but I'm having a problem with converting it to a clickable link with no need to copy and paste it to a browser.

The resulting link is 351 characters so i tried to reference just the cell which contains the formula but in both cases it throws me #VALUE!

When tried to make the formula with CONCATENATE but the result is the same

Any idea how to overcome the character limit? As I suppose that's the problem.

The address is different from the text that displays. There's probably a solution within VBA where you can take those inputs and convert them to a link but have a simple Display text that works around this limitation. — Mark S.
That's the generated address it work fine. you will not be able to access it its an internal only site. — Killroy

Mark S. Mark S. · Accepted Answer · 2021-07-29T12:38:35

Try:

=Hyperlink(K1&TEXT(B5,"YYYY-MM-DD")&K2&MONTH(B5)&K3&B3&K4&TEXT(B4,"YYYY-MM-DD")&K5&MONTH(B4)&K6&1&K7&20&K8&YEAR(B4)&K9&B2&K10&YEAR(B1),"Click for Report")

=Hyperlink(https://eu-icqa-repo.corp.amazon.com/metric/metric_daily_view/location?assist=threshold&date=2021-07-31&date_range=daily&fc=KTW1&fc_filter=EU&metric=Safety%2FAssociate+Satisfaction%09&month=07&quarter=3&report_id=161&search_type=location&start_date=2021-07-27&start_month=07&start_quarter=1&start_week=20&start_year=2021&view=op_area&week=30&year=2021,"Click for Report")

The VBA syntax would be something like:

Sub AddHyperlinkToCell()
Dim Link As String
Link = "https://eu-icqa-repo.corp.amazon.com/metric/metric_daily_view/location?assist=threshold&date=2021-07-31&date_range=daily&fc=KTW1&fc_filter=EU&metric=Safety%2FAssociate+Satisfaction%09&month=07&quarter=3&report_id=161&search_type=location&start_date=2021-07-27&start_month=07&start_quarter=1&start_week=20&start_year=2021&view=op_area&week=30&year=2021"

ActiveSheet.Hyperlinks.Add Range("A1"), _
Address:=Link, _
TextToDisplay:="Click Here for Report"

End Sub

Generating hyperlink from formula outcome

1 Answers