Can an excel cell contain a formula that updates when an integer is entered directly into the cell containing the formula

Question

So specifically I'm writing a test tracking document, when a particular test case fails I want to be able to enter it's bug number into a field and then have that field produce a URL to link to the bug in our database.

Currently I am having to use 2 columns to do this

so J contains the value entered and K produces the URL link.

=HYPERLINK("http://www.blah.com/edititem.aspx?id="&J1&"&type=defects&full_edit=true&project_id=0&step_id=0", J1)

This formula is in cell K1 and produces the result I want however in terms of just wanting it to be perfect I'd like to know if it's possible to have the formula in the cell so that when you type '10' for bug 10 it then creates the url in the cell you just typed 10 into, rather than the cell next to it?

This would obviously need to be copied down across many rows for each test case in the spreadsheet.

Answer 1

It's not possible to have a single cell formula change based on what you input to that same cell - without VBA. However, if your ID's are successive, you could use this formula in K1 and drag down.

=HYPERLINK("http://www.blah.com/edititem.aspx?id="&ROW(K1)+9&"&type=defects&full_edit=true&project_id=0&step_id=0",ROW(K1)+9 )

With VBA, you could use the following code to change a number to the hyperlinked value over the range A1:A10.

In the code editor, you need to add this code to the sheet you wish to use it on, rather than a module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:A10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Target.Formula = "=HYPERLINK(""http://www.blah.com/edititem.aspx?id=" & Target.Value & _
    "&type=defects&full_edit=true&project_id=0&step_id=0"",""" & Target.Value & """)"
Application.EnableEvents = True
End Sub

Source: http://msdn.microsoft.com/en-us/library/office/ff839775(v=office.15).aspx