Google Sheets – How to Refer to Sheet with Value in Cell

From inside the Google spreadsheet, go to Tools -> Script editor then try this:

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
   if( r.getColumn() == 1) { //checks the column
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var XSheet = ss.getSheetByName("Sheet2");
     ss.setActiveSheet(XSheet);
     SpreadsheetApp.getActiveSheet().getRange(r.getRow(), r.getColumn()).setValue("X");
     var MainSheet = ss.getSheetByName("Sheet1");
     ss.setActiveSheet(MainSheet);   };
 };
}

This assumes your sheets are named Sheet1 and Sheet2, as is the default, and it's set to only watch column E (5) on Sheet1 for edits and only add an "X" to the corresponding cell in Sheet2.

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];