4

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.

  • 1
    What should "link text" be, is it just the content of the column A? You can put A1 instead of "link text" then. If the issue is something else, please clarify. – user79865 Jun 3 '15 at 13:11
  • From the description 'take the "link text" from a parallel row in A column', I'd say that was the answer @HomegrownTomato. – Jeeped Jun 3 '15 at 23:05
2

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

0

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()))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.