Google-sheets – How to link to source cell from filter output

google sheetsgoogle-workspace

I have a simple query that outputs columns A and B of a project dataset, when the value in column A is not empty:

=QUERY(Projects!A2:B, "SELECT A,B WHERE A<>''")

This creates an easy to scan highlight of the important project data (in my case, project name and next action item). However, the project details are in another tab, so if I want to examine the project, I have to copy the name, go to the tab, and search for the project.

It would be super nice if the output of the QUERY could automatically hyperlink to the source data. Or maybe there is a hotkey I can press to jump to the source. Does this feature exist?

Best Answer

You have some project transaction data that you query. For each project, there is additional static data in another sheet. When you query your transaction data you want to create a hyperlink to the relevant project on the project sheet.

You have a query that you already use: =QUERY(Projects!A2:B, "SELECT A,B WHERE A<>''"). Insert this in your output sheet in cell A1

From your project details sheet, copy the sheet url up to and including the gid. This will look like this. "https://docs.google.com/spreadsheets/d/1tvshJ...../edit#gid=1448462430"
Paste the url into the following formula replacing the section labelled "<>"
Insert this formula in cell C1, beside the query: =array_constrain(ARRAYFORMULA({HYPERLINK("https://docs.google.com/spreadsheets/d/<<insert sheetID and gid here>>&range=A"&match(A1:A,Project_details!A2:A5,0)+1,"Link to "&A1:A)}),COUNTA(A1:A),1)

Explanation:

The formula creates a hyperlink for the project name in Column A.
The hyperlink url is built in three parts:
- the main body is the url of the project details sheet. You have copied/pasted this into the formula.
- the column of the linked range is provided by &range=A
- the row of the linked range is provided by the match function. Note that the actual function is match +1; the "1" allows for the header row on the product details sheet.
the formula is wrapped in an arrayformula so that it automatically calculates results for every row.
array_contrain is used to limit the output. If not used, the arrayformula would calculate for every row of the sheet - even though it might display output in the rows where there is a value in Column A

Say you have sheet called "Projects" that contains project transaction data. The sheet might look like this:

Project Transaction data

You also have a sheet containing further static details of each project. Say, this sheet is called "Project_details" and it might look like this:

Project details

Your output sheet will contain two formula; one in Cell A1 and another in Cell C1. The output should look like this:

Query output and hyperlink

Related Solutions

Google Sheets – Using Filter() to Reference Source Array in Criteria

I've been working on your file, even before the bounty period, and it as been a struggle. I wanted to give you the automation te ARRAYFORMULA can give in combination with a bit of Google Apps Script. In doing so, I wanted to understand the calculations a bit and thus I started with optimizing the first formula for column E:

=ARRAYFORMULA(IF(A4:A<>"";ARRAYFORMULA(A4:A*D4:D);""))

For column H, I created to following formula:

=ARRAYFORMULA(IFERROR(VLOOKUP(G4:G30;Suits!A:AC;{MATCH(H2, suits!A1:AC1 ,0)}*SIGN(ROW(G4:G30));0),""))

For column I, I created the following script:

function skills(array1, array2, array3) {
  var array = new Array();

  for(i=0, len2=array2.length; i<len2; i++) {
    var prod = 1;
    for(j=0, len1=array1.length; j<len1; j++) {
      if("% " + array2[i][0] == array1[j][0]) {        
        prod *= array1[j][2] + 1;        
      }      
    }
    var value;
    if(array3[i][0] == "") {
      value = "";
    } else {
      value = prod * array3[i][0];
    }
    array.push([value]);
  } 
  return array;
}

For column J, I created te following script:

function mods(array1, ModAttrb, ModHeader, ModStats, array5, array6) {
  var array = new Array(), aSecond = new Array(), aThird = new Array();

  // second calculation  
  for(i=0, len1=array1.length; i<len1; i++) {    
    var secondSum=0;    
    for(j=0, len2=ModAttrb.length; j<len2; j++) {      
      if(array1[i][0] == ModAttrb[j][0]) {        
        for(l=1, len4=ModStats[0].length; l<len4; l++) {
          if(ModStats[j][l] == 0 || ModStats[j][l] == "") {
            secondSum += 0;
          } else {
           secondSum += ModStats[j][l];
          }
        }
      }
    } 
    aSecond.push(secondSum);
  } 

  // third calculation
  for(i=0, len1=array1.length; i<len1; i++) {
    var thirdSum=0;
    for(m=0, len5=array5.length; m<len5; m++) {
      if(array1[i][0] == array5[m][0]) {        
        thirdSum += parseInt(array5[m][3]);        
      }      
    }
    if(thirdSum == 0) {
      thirdSum = 0;
    } 
    aThird.push(thirdSum);
  }  

  for (var i=0; i < aSecond.length; i++) {
    var result = 0;
    result += (array6[i] * 1 + parseInt(aSecond[i]) + parseInt(aThird[i]));
    array.push([result])
  }  
  return array;
}

Here, for the sake of time, I set the first calculation to be 1.

After that, time was running short. The Mult column is/was difficult to comprehense. We can work on it afterwards if you want?

See te example file I copied in te beginning: Fitting Tool

Google Sheets – Create Link in Cell Using URL from Another Cell

You were so close !!!!!!!!

Use the following formula.

Formula

=ARRAYFORMULA(
   HYPERLINK(
     FILTER(Data!D2:D40,Data!B2:B40=D2),
     FILTER(Data!A2:A40,Data!B2:B40=D2)
   )
 )

Explained

The ARRAYFORMULA will take on the ranges, set in the filter, and return the matches.