My attached example does already demonstrate this approach in column A. But: It only can return the name of the current sheet.Ī list of sheet names can only be produced setting at least a reference into every sheet that shall occur. The only function call I know returning a sheet name without basing on a reference is CELL(“filename”) without a second parameter. But: To get the name of a sheet that way we must provide a reference into the sheet as second parameter. There is one function returning a sheet name not already passed as a parameter: CELL() used with the controlling (first) parameter “address”. IF the address shall explicitly point into a sheet, the NAME must be explicitly passed via a parameter.
No name!ĪDDRESS() will return an address (notation modifyable) of a single cell on the base of row number and column number. SHEET() will return the sequential number (starting with 1) of the sheet the referenced range or cell is placed in. If you think so, please tell how that might work. SheetNameByNumber = ".Parameter.Missing."Īlas! There are fundamental disadventages of working with custom functions.Įxample attached: (regarding the question published as an answer): REM This can be done by appending "+NOW()*0" to the parameter expression, e.g. REM You will have to FORCE RECALCULATION "on any event". REM Note: A cell will NOT RECALCULATE DUE TO A CHANGE OF THE SheetName! REM There seems not to exist a StandardFunction for this purpose! One possibility: Function SheetNameByNumber(Optional pNo As Long) There were threads in other forums about this, too, and even the wise man (f/m) there didn’t know a way.Ī user defined function, on the other hand, can solve the problem in different ways and is really simple. On the other hand, there are no means to reference a sheet without knowing its name.
That is very strange in my opinion as there as well exist means to get the names (addresses) of cells by there column/row numbers as also to relatively reference them by OFFSET(). I don’t know a way to do this by standard functions. You will need to get the names by the numbers of the sheets.
0 kommentar(er)
