CODEWITHSUNDEEP
google-sheets
friedman
friedman

Reputation: 675

Copy formula replacing the references in their arguments by values

Is there an easy way to copy range referenced formulas with values. In particular example I have column A with a list of URLs, column B with a list of names, I combine them in column C formulas: =HYPERLINK(A1;B1)

And then I would like to copy all column C cells but with already modified formulas, where references are replaced with actual values, like: =HYPERLINK("http://w3c.org";"W3C")

I need to have this to let other users easily copy formulas to other sheets. Is it possible?

Upvotes: 1

Views: 57

Answers (1)

user6655984
user6655984

Reputation:

Yes, although not exactly by copying the formulas you already have. You need a "meta-formula" that makes that formula:

="=HYPERLINK(""" & A1 & """;""" & B1 & """)"

If A1 is "google.com" and B1 is "Google", the above evaluates to text

=HYPERLINK("google.com";"Google")

which is the formula you need (the doubling of " marks is necessary to escape them within a string). So far this is still text, though. Next steps:

  1. Copy the column of meta-formulas
  2. Paste them to a new column as values (Ctrl-Shift-V or its analog)
  3. Format the new column as "automatic" even if it already is formatted that way: select it, go to Format -> Number -> Automatic.

Now your new column has the formulas you want.

Upvotes: 2

Related Questions