Excel: Generar mismas referencias de una hoja a otra hoja en una misma tabla


Tengo datos de una ficha en nombre_hoja_1 y estos están referenciados en una tabla, quiero replicar esta hoja y replicar a su vez las referencias a esta hoja en un tabla que se extiende descendentemente. Estas son las referencias en mi tabla:

='nombre_hoja_1'!C14   ='nombre_hoja_1'!C46

Lo que pretendo conseguir es generar las mismas referencias a celdas pero de otra hoja, mi tabla tiene la siguiente estructura:

 ='nombre_hoja_1'!C14    ='nombre_hoja_1'!C46
 ='nombre_hoja_2'!C14    ='nombre_hoja_2'!C46
 ='nombre_hoja_3'!C14    ='nombre_hoja_3'!C46
 ='nombre_hoja_4'!C14    ='nombre_hoja_4'!C46

Lo único que he conseguido hasta ahora arrastrando es lo siguiente:

 ='nombre_hoja_1'!C14    ='nombre_hoja_1'!C46
 ='nombre_hoja_1'!C15    ='nombre_hoja_1'!C47
 ='nombre_hoja_1'!C16    ='nombre_hoja_1'!C48
 ='nombre_hoja_1'!C17    ='nombre_hoja_1'!C49

Cómo puedo conseguir el resultado esperado? Una macros? un listado, alguna función específica y cómo lo hago?

Muchísimas gracias!