Como no has adjuntado una hoja ejemplo, tengo que imaginarme lo que quieres hacer. Entiendo que quieres que en la celda C5 de tu hoja 1 aparezca el último valor introducido en la columna A de la hoja 2.

Si es un valor numérico e incremental, el último valor será el mayor de todos. de esta manera, la fórmula que te da el valor será:

=MAX(Hoja2!A:A)

Si no es un valor numérico o no es incremental, la cosa se complica un poco. Lo que haremos será contar el número de objetos que hay en la columna A de la hoja 2 y devolver el valor de la celda que ocupa esa posición.

=INDIRECTO(DIRECCION(CONTARA(Hoja2!A:A);2;1;1;"Hoja2"))

Esto funciona si no tienes ninguna fila vacía. Si tienes alguna fila libre por encima, debes sumar ese número (si son tres celdas vacías, +3) al resultado de CONTARA.

Alternativamente, puedes escribir cualquier cosa en las celdas vacías y ponerle el color del texto en blanco. (por ejemplo, "NO BORRAR ESTA CELDA"). Esto debes hacerlo también si hay celdas intermedias vacías.

El resultado que te da es el último valor que hay en la columna A de la Hoja2. Si la Hoja2 se llama de otra manera, sustituye "Hoja2" por el nombre que tenga.

Espero que te sirva. Un saludo