Fórmula genérica
=GET.WORKBOOK(1)&T(NOW())
Resumen
Para enumerar hojas de trabajo en un libro de Excel, puede usar un enfoque de 2 pasos: (1) defina un rango con nombre llamado "nombres de hoja" con un comando de macro antiguo y (2) use la función INDICE para recuperar nombres de hojas usando el rango con nombre. En el ejemplo que se muestra, la fórmula en B5 es:
=INDEX(MID(sheetnames,FIND(")",sheetnames)+1,255),ROWS($B$5:B5))
Nota: encontré esta fórmula en el tablero de mensajes de MrExcel en una publicación de T. Valko.
Explicación
El rango con nombre "nombres de hoja" se crea con este código:
=GET.WORKBOOK(1)&T(NOW())
GET.WORKBOOK es un comando de macro que recupera una matriz de nombres de hojas en el libro actual. La matriz resultante se ve así:
("(workbook.xlsm)Sheet1","(workbook.xlsm)Sheet2","(workbook.xlsm)Sheet3","(workbook.xlsm)Sheet4","(workbook.xlsm)Sheet5")
Una expresión críptica se concatena al resultado:
&T(NOW())
El propósito de este código es forzar el recálculo para recoger cambios en los nombres de las hojas. Como NOW es una función volátil, se recalcula con cada cambio de hoja de trabajo. La función AHORA devuelve un valor numérico que representa la fecha y la hora. La función T devuelve una cadena vacía ("") para valores numéricos, por lo que la concatenación no tiene ningún efecto sobre los valores.
De vuelta en la hoja de trabajo, la celda B6 contiene esta fórmula copiada:
=INDEX(MID(sheetnames,FIND(")",sheetnames)+1,255),ROWS($B$5:B5))
Trabajando de adentro hacia afuera, la función MID se usa para eliminar los nombres de las hojas de trabajo. La matriz resultante se ve así:
("Sheet1","Sheet2","Sheet3","Sheet4","Sheet5")
Esto entra en la función INDICE como "matriz". La función FILA usa rangos en expansión para generar un número de fila creciente. En cada nueva fila, INDICE devuelve el siguiente valor de matriz. Cuando no haya más nombres de hojas para generar, la fórmula devolverá un error #REF.
Nota: debido a que esta fórmula se basa en un comando de macro, deberá guardar como un libro de trabajo habilitado para macros si desea que la fórmula continúe actualizando los nombres de las hojas después de que el archivo se cierre y se vuelva a abrir. Si guarda como una hoja de trabajo normal, el código del nombre de la hoja se eliminará.
