Google-sheets – Need to be able to refer to a sheet within a count function

google sheets

I have a Google Spreadsheet I'm working on. Each tab has new data that's pasted in on a weekly basis, for example, I just created a new sheet called 5/5/2015 and included the data within.

What I'd like to do is grab the sheet name (5/5/2015) and put in say cell A1. In A2, I'm doing a count of the total number of items in sheet 5/5/2015 in column A.

Two questions:

  1. How do I refer to the sheet name in cell A1?
  2. How do I do a COUNT function where the sheet name is based off of the value in A1? IE, =COUNTIF('4/28/15'!E:E, "Professional") where it is more like =COUNTIF(A1!E:E, "Premium")

Best Answer

The second question is easier to answer: use INDIRECT as below

=COUNTIF(INDIRECT(CONCAT(A1,"!E:E")), "Professional")

Be sure that A1 is formatted as plain text, because sheet names are text strings. This is particularly important because you use sheet names such as 5/5/2015, which get automatically interpreted as dates.

The first question is tricky: as far as I know, there is no way to get the last created sheet of a spreadsheet, even with a script. The best I can do is get the name of the sheet that appears last among your spreadsheet tabs, assuming you keep them in chronological order. Here is the script: 

function grabLastSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var lastSheetName = sheets[sheets.length-1].getSheetName(); 
  var mainSheet = ss.getActiveSheet();
  var range = mainSheet.getDataRange();
  range.getCell(1,1).setValue(lastSheetName);
}

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("New Data", [{name: "Get Last Sheet",  functionName: "grabLastSheet"}]);
}

After saving this script (via Tools->Script Editor), close and reopen the spreadsheet. You should see a new menu item, "New Data", with one command "Get Last Sheet". Using this menu command will put the name of the spreadsheet listed last in the cell A1 of the currently active sheet.

Alternatively: if you want the name to be in A1 of the first sheet, replace

var mainSheet = ss.getActiveSheet();

with 

var mainSheet = sheets[0];
Related Topic