Google-sheets – How to reference a string from a cell in a Google Spreadsheet formula

google sheets

I have a spreadsheet that I use to track progress completion based on the percentage of checkboxes that are TRUE (checked).

The formula I use for this has 3 separate references to another sheet. I do this for every project and I am looking into reducing the amount of copy and pasting I am doing when adding a new tracker. Currently I am using the below formula:

=COUNTIF('Automation: PHP Reference Parser'!A:A,TRUE)/(COUNTIF('Automation: 
PHP Reference Parser'!A:A, FALSE)+COUNTIF('Automation: PHP Reference 
Parser'!A:A, TRUE))

I am trying to replace the three instances of 'Automation: PHP Reference Parser' with a reference to a cell that already contains this string as the project name. I have attempted to do this with INDIRECT but I get a parse error in the following attempt:

=COUNTIF(INDIRECT(A2)!A:A,TRUE)/(COUNTIF(INDIRECT(A2)!A:A, FALSE)+COUNTIF(INDIRECT(A2)!A:A, TRUE))

Best Answer

Change your

INDIRECT(A2)!A:A

to the following format.

INDIRECT(A2&"!A:A")

Related Solutions

Google-sheets – How to reference a cell string to use as a note in another cell

I can't manage to get by the permission error, this might work for you.

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() == "Project"){ //Change to your sheet name
    var sourceRange = e.source.getActiveRange();
    var sourceRow = sourceRange.getRow();
    var sourceColumn = sourceRange.getColumn();
    var sourceValue = sourceRange.getValue();

    if(sourceColumn == 1){ //Change to the column you want to get the value from
      var noteCell = sheet.getRange(sourceRow, 2); //The cell where the Note should be, B-column on the current row
      noteCell.setNote(sourceValue);
    }
  }
}

EDIT
I do believe this is what you want. The function above sets a note on the cell in the B-column on the current row, if the cell in the A-column is edited.

If you don't want the note to update with A1 you should be able to use the function you wrote, with a slight modification to get the value from A1.

function noteSetter() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var cell = sheet.getRange(2, 2);
 cell.setNote(sheet.getRange(1,1).getValue());
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
  name : "Set cell note",
    functionName : "noteSetter"
  }];
  sheet.addMenu("Scripts", entries);
};

I added a function to add the script to a Custom menu, to make it easier to access, if you don't want that you can just remove the onOpen-function.

Google-sheets – Using cell reference with COUNTIFS formula

Apply the following adjustment to your formula.

Formula

=COUNTIFS(J6:J365, "=David & Victor", K6:K365, "="&G3)

Screenshot

enter image description here