Suponga que tiene un libro de trabajo con tres hojas de trabajo, Sheet1, Sheet2 y Sheet3. En la columna A1 de la hoja de trabajo Hoja2, tiene la fórmula = Hoja1! A1. Cuando copia esa fórmula de Sheet2 a la celda A1 de Sheet3, la fórmula aún hace referencia a Sheet1. ¿Pero cómo puede ser eso? ¿Por qué Excel no ajusta la referencia de la hoja, como lo hace con las referencias de celda?

Al igual que los rangos con nombre, Excel trata los nombres de las hojas de trabajo como absolutos. Cada objeto de la hoja de trabajo es independiente de todas las demás hojas de trabajo del libro.

Cuando pega una fórmula que incluye una referencia de hoja, esa referencia de hoja se deja sin cambios en lo que se pega.

Hay un par de cosas que puedes hacer. Una es simplemente modificar la referencia de la fórmula después de pegarla para que haga referencia a la hoja correcta. Si tiene muchos de ellos para cambiar, puede seleccionar todas las fórmulas en la hoja de trabajo de destino (F5 | Especial | Fórmulas) y luego usar Buscar y reemplazar para reemplazar el nombre de la hoja de trabajo original (Hoja1)

con el nombre correcto de la hoja de trabajo (Hoja2).

Si sus necesidades de referencia no son complejas, puede utilizar un enfoque macro. Por ejemplo, si desea que una fórmula en una celda en particular se refiera a una celda en la hoja anterior a la hoja actual, entonces puede hacerlo mediante macro con bastante facilidad. Considere la siguiente macro:

Function PrevSheet(rCell As Range)

Application.Volatile     Dim i As Integer     i = rCell.Cells(1).Parent.Index     PrevSheet = Sheets(i - 1).Range(rCell.Address)

End Function

La macro mira la hoja de trabajo actual y luego determina qué hoja de trabajo está antes. Luego se hace la referencia para esa hoja de trabajo.

Una vez que haya creado la macro PrevSheet, esta es una forma en que se puede usar la función en una celda:

=PrevSheet(A1)

Esto devuelve el valor de la celda A1 de la hoja de trabajo anterior. Si tiene Sheet1, Sheet2 y Sheet3, y usa esta fórmula en Sheet3, devuelve el valor de Sheet2! A1. Si la hoja anterior es la primera hoja del libro de trabajo o no es una hoja de trabajo, la función devuelve un error #Value.

Si luego copia esta fórmula en una hoja diferente (por ejemplo, en la Hoja 5), ​​entonces saca el valor relativo a su nueva ubicación, lo que significa que saca el valor de Sheet4! A1.

También puede incluir un nombre de hoja y la función funcionará bien:

=PrevSheet(Sheet3!A5)

Esta versión siempre devolverá Sheet2! A5 ya que sheet2 es la hoja anterior de Sheet3.

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (12141) se aplica a Microsoft Excel 2007, 2010, 2013 y 2016.

Puede encontrar una versión de este consejo para la interfaz de menú anterior de Excel aquí:

link: / excel-Relative_Worksheet_References [Referencias relativas de la hoja de trabajo].