Is it possible to bulk hyperlink automation in Google Sheets using a formula that links to a specific cell on...
I am having trouble getting a formula to work in Google Sheets that functions in Excel. This formula allows me to nest an index/match within a hyperlink formula and generate bulk hyperlinks to corresponding cells on another tab.
This is how the formula looks in excel:
=HYPERLINK("#"&CELL("address",INDEX('Tab2'!A:A,MATCH('Tab1'!J2,'Tab2'!A:A,0))),'Tab1'!J2)
Tab 1 Contains:
An index of hyperlinks as well as a hidden column (column J) that contains the 'match' reference text
Tab 2 Contains:
A dataset with variable/question names down column A in random row intervals down the column that match the reference text in column J on Tab 1.
In order to easily share this data with interested parties we are looking for a Google Sheets solution. Uploading to Google Sheets causes the hyperlinks to become inactive, so we need a way to build this in sheets.
Upon pasting in the data set and reapplying the formula above, I cannot get it to function as it does in Excel.
I imagine this has something to do with having to pull in the URL reference or needing to reference a range ID but am having trouble finding a solution.
Any suggestions would be much appreciated!
worksheet-function
edited Dec 19 '18 at 22:27
Worthwelle
2,68731125
asked Dec 19 '18 at 21:33
Cole
11
add a comment |
I am having trouble getting a formula to work in Google Sheets that functions in Excel. This formula allows me to nest an index/match within a hyperlink formula and generate bulk hyperlinks to corresponding cells on another tab.
This is how the formula looks in excel:
=HYPERLINK("#"&CELL("address",INDEX('Tab2'!A:A,MATCH('Tab1'!J2,'Tab2'!A:A,0))),'Tab1'!J2)
Tab 1 Contains:
An index of hyperlinks as well as a hidden column (column J) that contains the 'match' reference text
Tab 2 Contains:
A dataset with variable/question names down column A in random row intervals down the column that match the reference text in column J on Tab 1.
In order to easily share this data with interested parties we are looking for a Google Sheets solution. Uploading to Google Sheets causes the hyperlinks to become inactive, so we need a way to build this in sheets.
Upon pasting in the data set and reapplying the formula above, I cannot get it to function as it does in Excel.
I imagine this has something to do with having to pull in the URL reference or needing to reference a range ID but am having trouble finding a solution.
Any suggestions would be much appreciated!
worksheet-function
edited Dec 19 '18 at 22:27
Worthwelle
2,68731125
asked Dec 19 '18 at 21:33
Cole
11
add a comment |
I am having trouble getting a formula to work in Google Sheets that functions in Excel. This formula allows me to nest an index/match within a hyperlink formula and generate bulk hyperlinks to corresponding cells on another tab.
This is how the formula looks in excel:
=HYPERLINK("#"&CELL("address",INDEX('Tab2'!A:A,MATCH('Tab1'!J2,'Tab2'!A:A,0))),'Tab1'!J2)
Tab 1 Contains:
An index of hyperlinks as well as a hidden column (column J) that contains the 'match' reference text
Tab 2 Contains:
A dataset with variable/question names down column A in random row intervals down the column that match the reference text in column J on Tab 1.
In order to easily share this data with interested parties we are looking for a Google Sheets solution. Uploading to Google Sheets causes the hyperlinks to become inactive, so we need a way to build this in sheets.
Upon pasting in the data set and reapplying the formula above, I cannot get it to function as it does in Excel.
I imagine this has something to do with having to pull in the URL reference or needing to reference a range ID but am having trouble finding a solution.
Any suggestions would be much appreciated!
worksheet-function
edited Dec 19 '18 at 22:27
Worthwelle
2,68731125
asked Dec 19 '18 at 21:33
Cole
11
I am having trouble getting a formula to work in Google Sheets that functions in Excel. This formula allows me to nest an index/match within a hyperlink formula and generate bulk hyperlinks to corresponding cells on another tab.
This is how the formula looks in excel:
=HYPERLINK("#"&CELL("address",INDEX('Tab2'!A:A,MATCH('Tab1'!J2,'Tab2'!A:A,0))),'Tab1'!J2)
Tab 1 Contains:
An index of hyperlinks as well as a hidden column (column J) that contains the 'match' reference text
Tab 2 Contains:
A dataset with variable/question names down column A in random row intervals down the column that match the reference text in column J on Tab 1.
In order to easily share this data with interested parties we are looking for a Google Sheets solution. Uploading to Google Sheets causes the hyperlinks to become inactive, so we need a way to build this in sheets.
Upon pasting in the data set and reapplying the formula above, I cannot get it to function as it does in Excel.
I imagine this has something to do with having to pull in the URL reference or needing to reference a range ID but am having trouble finding a solution.
Any suggestions would be much appreciated!
worksheet-function
worksheet-function
edited Dec 19 '18 at 22:27
Worthwelle
2,68731125
asked Dec 19 '18 at 21:33
Cole
11
edited Dec 19 '18 at 22:27
Worthwelle
2,68731125
asked Dec 19 '18 at 21:33
Cole
11
edited Dec 19 '18 at 22:27
Worthwelle
2,68731125
edited Dec 19 '18 at 22:27
Worthwelle
2,68731125
edited Dec 19 '18 at 22:27
Worthwelle
2,68731125
2,68731125
asked Dec 19 '18 at 21:33
Cole
11
asked Dec 19 '18 at 21:33
Cole
11
asked Dec 19 '18 at 21:33
Cole
11
11
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1386054%2fis-it-possible-to-bulk-hyperlink-automation-in-google-sheets-using-a-formula-tha%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1386054%2fis-it-possible-to-bulk-hyperlink-automation-in-google-sheets-using-a-formula-tha%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
