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];
If you want to sort only the active sheet, it's simpler:
function onEdit() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(3, 1, sheet.getLastRow() - 2, sheet.getLastColumn());
range.sort({column: 1, ascending: false});
}
You had sheet.getLastRow() - 1
for the number of rows in the range, which doesn't seem correct if you start the range from the 3rd row. I put -2.
Best Answer
Contrary as occurs with built-in functions like
=NOW()
that return an new value every time that the spreadsheet is recalculated, custom functions are recalculated only when their arguments change. So if we have a sheet which ID is503917557
, then the resulting formula isif the name of the sheet with ID =
503917557
is changed, the argument of the above formula is still the same, so it will not be recalculated.It's worthy to note that if we overwrite a cell with exactly the same formula, the formula will not be recalculated either, because the optimization algorithm of Google Sheets see this action as "nothing happened".
On previous versions of Google Sheets Google Apps Script allowed the use of functions like NOW(), TODAY() and other deterministic built-in functions as custom function arguments, but nowadays they aren't allowed.
The current workarounds are
Use the web browser refresh feature as custom functions are recalculated on opening the spreadsheet
Add a dummy argument, like adding a number that is incremente every time that we want the formula to be recalculated, example:
Update the cell value by using a script able to be called from an installable trigger, custom menu, button, etc.
Below is my original answer.
From my answer to Custom Functions and Recalculation