Google Sheets – Get Cell or Range Reference Dynamically

Assuming you meant 'selecting from a certain cell from all sheets' (in one spreadsheet), custom function written in GAS that does that could look like this:

function avgCellAllSheets(cell) {
var arr = [];
SpreadsheetApp.getActive()
    .getSheets()
    .forEach(function (s) {
        arr.push(s.getRange(cell)
            .getValue());
    });
return arr.reduce(function (cur, prev, i) {
    return cur + (prev - cur) / (i + 1);
},0);
}

Fomula to be entered in the sheet is

=avgCellAllSheets(cell) 

example:

=avgCellAllSheets("A1")

However, as custom functions like that seem to suffer from memoization, it will probably not auto-update after adding a sheet. There are workarounds, e.g. adding a dummy parameter, let's say the content of a certain cell that when incremented 'forces' a recalculation of the formula)

Spreadsheet functions cannot access the content of other spreadsheets, other than by invoking importrange.

But you can have importrange inside other functions. For example,

=join(",", importrange("...", "Sheet1!A1:A5"))

returns the content of cells A1:A5 in the other spreadsheet, comma-separated within one cell. Another command often nested is query, which allows you to do a lot of processing of another spreadsheet's data without getting intermediate results in. For example:

=query(importrange("...",  "Sheet1!A1:B9"), "select Col1, sum(Col2) group by Col1 order by Col1") 

returns the totals of the second column split by the values of the first column, and sorts by the first column.