Excel - Contar celda de varias hojas de forma dinàmica

Llevo días buscando información y no encuentro como hacerlo. A ver si me pueden ayudar.

Tengo un excel para maestros en el cual hay hojas con las actividades que se han hecho durante el año con las notas de cada alumno, y un cuadro resumen de todas las notas. En el cuadro resumen, tengo varias celdas (300) que cuentan cuantas celdas estan rellenadas en las hojas de actividades (cada celda se fija en una celda diferente de las hojas). La formula seria esta:

=CONTAR(HOJA1:HOJA7!A1)
=CONTAR(HOJA1:HOJA7!A2)
etc...

Esto funciona siempre y cuando tenga el rango de hojas claro. Pero he automatizado el excel para ir añadiendo hojas de activades y entonces el rango deberia aumentar cada vez que se añade una nueva hoja. Quisiera que la formula se me actualizara.

He intentado con nombres definidos que me definan el rango y he visto que puedo hacer esto:

Nombredefinido = Hoja1:HOJA7!$A$1
=CONTAR(Nombredefinido)

Si actualizo el nombre a través de macros se me actualiza automaticamente la formula. Pero tengo que poner en el nombre definido la celda que quiero contar. El problema es que esta formula la necesito para contar 300 parametros de diferentes celdas.

He intentado que el nombre definido solo defina el rango de hojas y luego completar la formula, pero o no se puede o no se como se hace.

Nombre definido =HOJA1:HOJA7
=CONTAR(Nombredefinido!A1)

Necesitaria saber si hay alguna manera que lo único dinàmico sea el rango, ya que quiero poner la formula en las 300 celdas que cada una cuenta una celda diferente pero del mismo rango de hojas.

A lo mejor existe otra formula, o otra manera de hacerlo. Estoy buscando como hacerlo directamente con la formula, para no hacer una macro que me canvie cada vez que se añada una hoja todas las formulas.

Gracias de antemano

Hola Meritxel:
¿Y no sería todo muchísimo más sencillo si usaras una sola hoja, añadiendo una columna por el tipo de actividad, otra por la fecha e informes de Tabla dinámica en otra hoja?
Dos hojas sin fórmulas complejas ni macros. Compara.
https://wordexperto.com/

La cosa es que hay más información. El excel tiene lo siguiente:
- Una hoja donde hay el curso e alumnos de la classe.
- Otra hoja donde hay un resumen de las actividades y un cuadro con todas las competencias que se pueden trabajar. en esta hoja el profesor crea las nuevas actividades y marca en la tabla de las competencias cuales va a trabajar.
- Una hoja para cada actividad donde hay una tabla que muestra las competencias que trabaja y los alumnos, para rellenar con las notas.
- Y la hoja final que resume toda la informacion. De cada competencias y de cada alumno, que nota ha sacado.

Las actividades no tienen una sola nota, si no una para cada competencia. Y en la hoja final se marca si de entre todas las actividades el alumno a aprobado cada una de las competencia de forma individual. Por eso contamos de cada hoja la misma celda que corresponde al alumno y a la competencia. Y hay que contar todas las competencias para cada alumno.

Pero al margen de que se pueda hacer mas sencillo o no, es possible lo que yo comentava?? Se pueden hacer formulas con una referencia a rango de hojas dinàmico ? O solo se puede a través de macros?

Una base de datos contiene un registro por actividad (Nota) con todas las columnas que necesites: Curso académico, grupo, fecha, alumno, Profesor, Asignatura, ACTIVIDAD Y todas las demás que necesites.
Ya está. No hay que hacer nada más. El resto se encargan las Tablas dinámicas para hacer los resúmenes que necesites por año, alumno, asignatura, actividad, curso, grupo, sexo o lo que quieras.
Esta sería una estructura eficiente para Excel con un libro de dos hojas, sin fórmulas ni macros para toda la vida..
Si en vez de formarte en el manejo de Tablas dinámicas para destinar todo el tiempo que te vas a ahorrar a lo que quieras, te empeñas por el otro camino, mira si esto te ayuda: https://jldexcelsp.blogspot.com.es/2006/02/ms-sobre-referencias-dinmicas-en-excel.html

https://wordexperto.com/

Gracias por responder,

La verdad es que el excel me lo han pasado ya con una estructura y yo lo he modificador para que hiciera cosas de forma automàtica, para hacerlo más fácil a los professores que tienen que usarlo y también de paso aprender más sobre la programación con macros. Ya se que las tablas dinàmicas te pueden facilitar mucho pero tienen que basarse en una sola tabla y en este caso havia más.

Y gràcias por la información de la función INDIRECTO, ya lo habia probado y no me salia. Al final he conseguido hacerlo con con un rango de hojas ocultas y que las hojas nuevas aparecieran entre las dos ocultas.

Gracias por toda la información.