martes, 3 de abril de 2012

Obtener el nombre de una hoja

La función CELDA devuelve información acerca del formato, la ubicación o el contenido de una celda. Su sintaxis es:

CELDA("tipo_de_info";[referencia])
  • "tipo_de_info" es una palabra elegida entre una lista de posibles argumentos: "DIRECCIÓN", "COLUMNA", "COLOR", "CONTENIDO"...
  • referencia (opcional) es el nombre de una celda.

CELDA con el argumento "NOMBREARCHIVO" se usa para obtener la ruta completa donde se guarda el libro así como el nombre de la hoja.

Nota: En la ayuda de Excel (al menos en las versiones 2003, 2007 y 2010) hay un error; en vez de "NOMBREARCHIVO" aparece "ARCHIVO".

Si usamos CELDA con el argumento "NOMBREARCHIVO" pero no usamos el segundo argumento, se puede producir un resultado inesperado. Veámoslo.

Abrimos Excel, sustituimos el nombre de la "Hoja1" por el de "Valores", ponemos los rótulos de la imagen siguiente y finalizamos guardando el libro en el escritorio con el nombre SE19-Celda_nombrearchivo.

Ya tenemos la hoja preparada. Vamos a intentar poner en C8 el nombre de la hoja.

En C2:
=CELDA("nombrearchivo")

En mi ordenador el resultado ha sido: C:\Users\Javi\Desktop\[SE19-Celda_nombrearchivo.xlsx]Valores

El nombre de la hoja ha quedado al final de la expresión y a continuación del corchete "]". Necesitamos conocer la longitud de la frase y en qué posición se encuentra el corchete para extraer los caracteres que hay a su derecha.

En C3:
=LARGO(C2)      [Resultado: 60]

En C4:
=ENCONTRAR("]";C2;1)      [Resultado: 53]

En C5:
=EXTRAE(C2;C4+1;C3-C4)      [Resultado: Valores]

En vez de EXTRAE, podemos usar la función DERECHA. Lo haremos en C6.

En C6:
=DERECHA(C2;C3-C4)      [Resultado: Valores]

Para prescindir de todas las celdas anteriores, creamos una fórmula compuesta en C8.

En C8:
=EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo");1)+1;LARGO(CELDA("nombrearchivo"))-ENCONTRAR("]";CELDA("nombrearchivo")))   [Resultado: Valores]

Hasta aquí todo parece correcto, pero hay un problema. Si cambiamos el nombre de otra hoja, la celda C8 ya no mostrará la etiqueta "Valores" sino la de la última hoja cambiada. Para comprobarlo, sustituimos "Hoja2" por "Compras" y volvemos a la hoja "Valores". Éste será el resultado:

Si ahora cambiamos "Hoja3" por "Balance anual" obtendremos:

El problema surge al prescindir del segundo argumento de la función CELDA. Si ponemos como segundo argumento cualquier celda de la hoja "Valores" el valor de C8 no cambiará, salvo que modifiquemos la etiqueta de la hoja "Valores". Así pues, debemos modificar las fórmulas de C2 y C8.

En C2:
=CELDA("nombrearchivo";Valores!E9)        [Vale cualquier celda]

En C8:
=EXTRAE(CELDA("nombrearchivo";Valores!E9);ENCONTRAR("]"; CELDA("nombrearchivo";Valores!E9);1)+1;LARGO(CELDA("nombrearchivo";Valores!E9))-ENCONTRAR("]";CELDA("nombrearchivo";Valores!E9);1))

Ya podemos cambiar tranquilamente el nombre de cualquier hoja sin temor de que se modifique el valor devuelto por la fórmulas de la celda C8 (excepto en el caso de que modifiquemos la etiqueta de la hoja "Valores").




32 comentarios:

  1. Tengo un problema. Con la fórmula "=SI(INDIRECTO(("'EQUIPOS'!G"&(EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo"))+1;255))))="EA";'C:\Documents and Settings\MIGUELB\Escritorio\[Operaciones mto.xlsx]48'!C31;"NO")"
    Repetida en varias hojas numeradas, me debe dar resultados diferentes en cada hoja dependiendo de su numero, pero, cliqueo en una hoja y me pone su resultado, el mismo, en todas, cliqueo en otra, y me cambia todos los resultados al correcto de esa hoja en concreto. ¿Cómo lo soluciono?

    ResponderEliminar
  2. Tengo esta fórmula en una celda de varias hojas. El resultado depende del nº de hoja.
    "=SI(INDIRECTO("'EQUIPOS'! G"&(EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo"))+1;255))))="EA";'C:\Documents and Settings\MIGUELB\Escritorio\[Operaciones mto. Xlsx]48'! C31;"NO")"
    El problema es que me da el mismo resultado en todas. Si ciqueo en una hoja, se actualiza el resultado de esa hoja en todas, y no conserva cada una la suya. ¿Qué hago?


    ResponderEliminar
  3. Buenos días,
    Tengo la fórmula: EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo")))
    Esta fórmula me da el nombre de la hoja activa, pero necesito que me de el nombre de la hoja donde está la fórmula, independientemente de si es la activa o no. ¿Se puede hacer?. Gracias

    ResponderEliminar
  4. MUchas gracias por entregar tu conocimiento, ¿tengo una consulta?, ¿es posible ir generando nuevas hojas, en las cuales paresca en una celda determinada el nombre de esta?
    de antemano muchas gracias

    ResponderEliminar
  5. Muchas gracias por la información.

    ResponderEliminar
  6. Hola: Probé esto y resultó dando el nombre de la hoja, siempre y cuando no sea mayor a 99 caracteres (Lo que sería muy raro, por cierto) =EXTRAE(CELDA("filename";A100);HALLAR("]";CELDA("filename";A100))+1;99)
    Hay valores arbitrarios, como A100 y 99, funciona igual
    También usé "filename" en lugar de "NombreArchivo" y anduvo bien.
    Espero que sea útil.
    Saludos
    Doctorniyo

    ResponderEliminar
  7. Gracias y muy bien explicado todo

    ResponderEliminar
  8. Excelente, es lo exactamente lo que andaba buscando.,

    ResponderEliminar
  9. Excelente tutorial amigo me ayudo mucho

    ResponderEliminar
  10. Buenas Tardes

    La información me funcionó tal y como requiero, desconocía la existencia de la función CELDA.

    Aunque el post no es reciente hago las siguiente observación:

    1. Al usar el nombre de la hoja en el segundo parámetro de la función CELDA se pierde la idea original que es "Obtener el nombre de una hoja". Es decir, para que voy a digitar el nombre de la hoja si lo que busco es el nombre de la hoja independientemente del que tenga. Bajo este esquema si se requiere cambiar el nombre de la hoja se tendría que modificar también en todas los lugares donde se use la función CELDA.
    2. Realizando pruebas en Microsoft Excel 2010 comprobé que en el segundo parámetro de la función CELDA no es necesario incluir el nombre de la hoja sino que basta con poner la referencia de la celda.

    Dicho lo anterior la formula me funciono de la siguiente manera:
    =DERECHA(CELDA("nombrearchivo",A1),LARGO(CELDA("nombrearchivo",A1))-HALLAR("]",CELDA("nombrearchivo",A1)))

    Tengo un archivo con 12 hojas cada una con el nombre de un mes y en todas uso la función anterior para obtener el nombre del mes en varias celdas y aunque agregue nuevas hojas el nombre de las demás se mantiene.

    Gracias por la información.

    ResponderEliminar
  11. Se puede hacer más fácil

    TRIM(
    RIGHT(
    CELL("filename"),
    LEN(CELL("filename")) - FIND("]",CELL("filename"),1)
    )
    )

    ResponderEliminar
  12. Excelente, me funcionó totalmente. Lo unico que tuve que variarle fueron los puntos y comas por comas, despues todo perfecto. Muchas gracias por el aporte.

    ResponderEliminar
  13. Genial, me ha servido de mucho. Gracias

    ResponderEliminar
  14. muy buena solución!!!!

    felicitaciones

    ResponderEliminar
  15. =EXTRAE(CELDA("nombrearchivo";A1);ENCONTRAR("]";CELDA("nombrearchivo";A1))+1;32)

    ResponderEliminar
  16. Una opción más simple:

    =EXTRAE(CELDA("nombrearchivo");HALLAR("]";CELDA("nombrearchivo");1)+1;10)

    Aprovechamos la función CELDA para obtener la dirección del archivo excel y extraemos cuantos caracteres (en este caso 10) hay tras el cierre de corchetes (sumamos uno para no extraer el propio cierre de corchete).

    C:\Users\Carpeta\[nombredelarchivo.xlsx]NombreDeHoja

    ResponderEliminar
  17. Muchas gracias por la aportación.

    Hasta ahora venía utilizando la fórmula inicial y el problema de los cambios de nombre de hoja, lo solucionaba pulsando la tecla F9, que refresca el valor.

    Sin embargo, acabo de encontrar otro modo, por si pudiera servir a alguien:

    =DERECHA(CELDA("nombrearchivo";A1);LARGO(CELDA("nombrearchivo";A1))-ENCONTRAR("]";CELDA("nombrearchivo";A1)))

    He realizado varias comprobaciones y la celda se mantiene sin cambios al modificar cualquier otra hoja.

    Un saludo

    ResponderEliminar
  18. Muchas gracias, fue de enorme ayuda

    ResponderEliminar
  19. muchas gracias por tu explicación y aplicación.

    ResponderEliminar
  20. Gracias, excelente aporte, me sirvió muchísimo.

    ResponderEliminar
  21. HOLA,
    Tienes un video acerca de esta explicación?

    ResponderEliminar
  22. Hola, me pareció muy bueno tu trabajo.
    Están caídos los enlaces de descarga.
    Saludos.

    ResponderEliminar