Universal formula for generating Google Spreadsheet hyperlinks from cell contents

Question

I have a column of cells in a Google Spreadsheet with values such as:

A
-----
37683
36583
38637
32391

I'm generating hyperlinks in column B for each cell in column A:

http://example.com?id=xxxxx

Where xxxxx are values of the cells in column A:

=HYPERLINK(CONCATENATE("http://example.com?id=",A1);"link text")

The problem is that the formula requires to be filled down the rest of the column by the user.

I'd like to be able to paste the formula in any random cell in column B so that it takes the "link text" (xxxxx values from column A) from a parallel row in column A.

Scenario 1

When the value in cell A455 is 37683 then pasting a formula into cell B455 will display 37683 as a link text and will take us to http://example.com?id=37683

Scenario 2

When the value in cell A27 is 39873 then pasting a formula into cell B27 will display 37683 as a link text and will take us to http://mywebsite.com?id=39873

The formula needs to be universal so that the result is the same regardless of where in column B it is pasted.

How can this be done? Any workarounds welcome.

Answer 1

In Google Sheets, when you copy a formula that uses relative references to another cell, that references are automatically updated.

A basic example

If in cell B2 the formula is =A2 and you copy it and paste to cell B43, the resulting formula will be =A43.

An example for the specific case of this question

If in cell B2 the formula is
=HYPERLINK(CONCATENATE("http://example.com?id=",A2),A2)
and you copy it and paste to cell B455, the resulting formula will be
=HYPERLINK(CONCATENATE("http://example.com?id=",A455),A455).

References
Add formulas to a spreadsheet - Docs editors help

Answer 2

You could actually exclude the concatenate part if you want by doing

=hyperlink("http://example.com?id="&A1,A1)

or

=hyperlink("http://example.com?id="&indirect("$A"&ROW()),INDIRECT("A"&row()))