Problemas en las fórmulas al cambiar el nombre de la hoja

En ocasiones, una fórmula deja de funcionar si se cambia el nombre de la hoja. Esto puede ocurrir cuando el nombre de la hoja está incluido en la fórmula como un literal. Pongamos un ejemplo.

Escribimos algo en la celda  D1 de la Hoja1: mesa
En D3 ponemos el número de la fila donde hemos escrito la palabra anterior: 1
En D4 ponemos el número de la columna: 4
En D5 creamos la dirección: =DIRECCION(D3;D4)       [Resultado: $D$1]
En D7 extraemos el valor de la celda señalada en D5: =INDIRECTO("Hoja1!"&D5)       [Resultado: mesa]

Nota: A nadie se le escapa que la fórmula más lógica, si necesitáramos utilizar INDIRECTO, hubiera sido: =INDIRECTO(Hoja1!D5). He introducido el literal "Hoja1!", seguido del signo de concatenación (&) y el nombre de la celda, únicamente para ilustrar el problema que puede surgir al cambiar el nombre de una hoja.

¿Qué ocurre si sustituimos el nombre de la "Hoja1" por otro? Que se produce un error en D7. La razón es que la expresión "Hoja1!" es un literal que no se modifica al cambiar el nombre de la hoja; por tanto, INDIRECTO sigue apuntando a la celda D5 de una hoja que ya no existe.

¿Cómo resolver el problema? Lo haremos con el siguiente ejemplo:

Tenemos un texto en la celda C2 que debemos poner en C14 utilizando INDIRECTO y el nombre de la hoja (Monumentos) tratado como un literal.

En C4 ponemos la fila de la celda C2: 2

En C5 ponemos la columna de la celda C2: 3

En C6 obtenemos la dirección de C2: =DIRECCION(C4;C5)

En C8 obtenemos la ruta del fichero y el nombre de la hoja: =CELDA("nombrearchivo";Monumentos!A1)

En C9 calculamos los caracteres totales del texto obtenido en C8: =LARGO(C8)

En C10 calculamos en qué posición se encuentra el carácter "]": =HALLAR("]";C8;1)

En C11 extraemos el nombre de la hoja: =DERECHA(C8;C9-C10)

En C12 creamos una macrofórmula para evitar el uso de las celdas C8 a C11: =DERECHA(CELDA("nombrearchivo";Monumentos!A1);LARGO(CELDA("nombrearchivo";Monumentos!A1))-HALLAR("]";CELDA("nombrearchivo";Monumentos!A1);1))

En C14 ponemos el mismo valor que en C2: =INDIRECTO("'"&C12&"'!"&C6)

Para comprobar si funciona correctamente, sustituimos el nombre de la hoja por "Monumentos históricos":

El resultado de C14 no ha cambiado.

El empleo de la función CELDA, usando los dos argumentos que tiene, puede ser de utilidad si queremos mostrar una lista con el nombre de todas las hojas del libro. Lo correcto sería que la lista se actualizara al cambiar el nombre de cualquier hoja. Lo haremos en la hoja "Museos".

Usaremos las celdas D3 a D6 para poner los nombres de las hojas. Debemos conseguir que se actualicen al cambiar el nombre de la hoja.

En D3:

=DERECHA(CELDA("nombrearchivo";'Monumentos históricos'!A1);LARGO(CELDA("nombrearchivo";'Monumentos históricos'!A1))-HALLAR("]";CELDA("nombrearchivo";'Monumentos históricos'!A1);1))

En D4:

=DERECHA(CELDA("nombrearchivo";Museos!A1); LARGO(CELDA("nombrearchivo";Museos!A1))-HALLAR("]";CELDA("nombrearchivo";Museos!A1);1))

En D5:

=DERECHA(CELDA("nombrearchivo";'Instalaciones deportivas'!A1);LARGO(CELDA("nombrearchivo";'Instalaciones deportivas'!A1))-HALLAR("]";CELDA("nombrearchivo";'Instalaciones deportivas'!A1);1))

En D6:

=DERECHA(CELDA("nombrearchivo";Auditorios!A1); LARGO(CELDA("nombrearchivo";Auditorios!A1))-HALLAR("]";CELDA("nombrearchivo";Auditorios!A1);1))

Hacemos clic en B3, accedemos a Datos + Validación de datos y ponemos los valores siguientes:

La lista está creada. Podemos cambiar el nombre de cualquier hoja y comprobar que la lista queda actualizada.

Descargar archivo (SE20-Monumentos.xls)

Descargar archivo (SE20-Monumentos.xlsx)