Excel: crea una hoja de trabajo resumida usando ciertas celdas de otras hojas de trabajo

Tengo un montón de tabs que comparten una estructura estándar. En las celdas A1, B1, C1 de cada pestaña, tengo, respectivamente, un número de ID, un primer nombre y un apellido. Hay una gran cantidad de otra información en cada pestaña que no es relevante para la pregunta.

Quiero crear una pestaña de resumen, con una tabla de 3 columnas con solo el número de identificación, el nombre y el apellido de cada una de las otras tabs. Nuevamente, estos están siempre en las celdas A1, B1, C1. También quiero que el número de identificación en la tabla de resumen se hyperlink a la pestaña correspondiente.

¿Hay alguna forma de semiautomatizar esto usando references de celda? Por ejemplo, en una sola tabla, si ingresa un número en una celda y arrastra hacia abajo, el número se incrementará en cada celda consecutiva en la columna. ¿Hay alguna manera de lograr un efecto similar, pero con la reference de celda restante constante (siempre celda A1, A2, A3) y la reference TAB se incrementa? En resumen, ¿hay alguna manera de vincular una fila en particular a una pestaña separada (de una manera que también se ocupe de la hipervinculación)? ¿O debo ingresar la reference manualmente para cada tabla?

No es que las tabs no estén labeldas como Hoja 1, Hoja 2, etc. Se labelrán con el nombre de la persona cuya información poseen.

[EDIT: Added ASAP option]

Las utilidades ASAP le quitan el trabajo a esto

  • Descargue las utilidades ASAP, http://www.asap-utilities.com/
  • Ejecute la utilidad como se muestra en la captura de pantalla 1 a continuación
  • Elija si desea un resumen de enlace en vivo o un informe codificado (captura de pantalla 2)

Configuración ASAP enter image description here

Seleccione su A1: C1 entre hojas enter image description here

Salida
enter image description here

[Initial Post]

Puede hacer esto sin un sub continuo de VBA

Paso 1

Defina un nombre de range para contener todos los nombres de las hojas (de este gran ejemplo de David Hager )

En este ejemplo, utilicé AllSheets
con una reference de
=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))

enter image description here

Paso 2
Use una fórmula INDEX para sacar cada hoja única en su hoja de resumen
=IF(ROW()<=COUNTA(AllSheets),INDEX(AllSheets,ROW()),"")& LEFT(RAND(),0)

Suponiendo que su hoja de resumen está en el extremo izquierdo (es decir, en la hoja 1), ROW () será 2 en A2, por lo que AllSheets la segunda hoja de AllSheets
ROW () será 3 en A3, por lo que AllSheets la tercera hoja de AllSheets etc.

LEFT(RAND(),0) asegura que la list de nombres de las hojas se actualice tan pronto como se cambie el nombre de una hoja (y la solución VBA necesitaría monitorear los cambios de los nombres de las hojas) enter image description here

Paso 3
Use INDIRECT para sacar A1, B1 y C1 para cada hoja =IF($A2<>"",INDIRECT("'"&$A2&"'!"&B$1),"")

Copie esta fórmula B2 a la derecha y hacia abajo hasta donde lo espere.

Tenga en count que esta fórmula maneja el ' que ocurre para los nombres de las hojas con espacios, etc. enter image description here

Probablemente INDIRECT usar la function INDIRECT . Por ejemplo, para get el valor de la celda A1 en la hoja John, puede llamar a:

 =INDIRECT("John!A1") 

Entonces, en su caso podría poner "Juan" en la celda A1 de su pestaña de resumen, "A1" en la celda B1 de la pestaña de resumen y en la celda C1, poner la siguiente fórmula:

 =INDIRECT("'"A1&"'!"&B1) 

No hay funciones integradas en Excel que le proporcionen todos los nombres de las hojas en el libro de trabajo. Para esto, tendrías que recurrir a VBA, la function de scripting integrada en Excel. Si está en Office 2007 o superior, esto requerirá que almacene su libro de trabajo en un formatting especial. Además, para ejecutar la macro, el usuario debe permitir específicamente la macro ejecución. La siguiente macro le permitirá imprimir todos los nombres de las hojas en la primera columna. Un punto importante a tener en count es que este código debería ejecutarse en la window de código de la hoja de resumen.

 Sub SheetNames() For i = 1 To Sheets.Count Cells(i, 1) = Sheets(i).Name Next i End Sub 

Una vez que tenga los nombres de la hoja, puede vincular dinámicamente a las celdas en las hojas usando la siguiente fórmula:

 =INDIRECT(ADDRESS(1, 1, 1, 1, A1)) =INDIRECT(ADDRESS(1, 2, 1, 1, A1)) =INDIRECT(ADDRESS(1, 3, 1, 1, A1)) 

La fórmula INDIRECTA toma una dirección. La fórmula ADDRESS funciona de la siguiente manera ADDRESS (Row_num, Column_num, Abs_num, A1, Sheet_text). La ayuda de Excel podrá responder cualquier pregunta.

Puede configurar la fórmula y ejecutar la macro solo cuando haya muchas hojas nuevas. El libro de trabajo puede almacenarse sin la macro. Simplemente mantenga la macro en un file de text en algún lugar en caso de que la necesite nuevamente.

Aquí hay una solución que usa una UDF de VBA para get los nombres de las hojas. Incluye un hyperlink según lo solicitado.

La llamada al UDF se coloca en una columna fuera del path. He usado H para este ejemplo

Coloque estas funciones en las celdas A1, B1, C1, H1

A1: =IF(H1<>"",HYPERLINK(SUBSTITUTE(H1,"]","]'")&"'!A1",INDIRECT("'"&$H1&"'!R1C[0]",FALSE)),"")

B1 y C1: =IF($H1<>"",INDIRECT("'"&$H1&"'!R1C"&COLUMN(),FALSE),"")

H1: =SheetByIndex()

Pon esto en un module de VBA

 Function SheetByIndex() As Variant Application.Volatile Dim r As Range Dim shIdx As Long Dim ThisShIndex As Long Dim sh As Worksheet Dim wb As Workbook Set r = Application.Caller ThisShIndex = r.Worksheet.Index shIdx = r.Row If shIdx >= ThisShIndex Then shIdx = shIdx + 1 End If Set wb = r.Worksheet.Parent On Error Resume Next Set sh = wb.Worksheets(shIdx) If Err.Number <> 0 Then SheetByIndex = "" Else SheetByIndex = "[" & wb.Name & "]" & sh.Name End If End Function 

Copie las fórmulas en A, B, C, H hacia abajo tanto como sea necesario

Mi enfoque ligeramente diferente, basado en el código publicado aquí (también hay una hoja de muestra para probar):

 Option Explicit Sub CreateHyperlinkedSheetList() 'Author: Jerry Beaucair 'Date: 1/3/2011 Dim ws As Worksheet, NR As Long Application.ScreenUpdating = False With ActiveSheet NR = .Range("A" & Rows.Count).End(xlUp).Row + 1 For Each ws In ActiveWorkbook.Worksheets If ws.Name <> .Name Then .Range("A" & NR).Value = ws.Range("A1").Value .Hyperlinks.Add Anchor:=.Range("A" & NR), Address:="", SubAddress:= _ "'" & ws.Name & "'!A1", TextToDisplay:=ws.Name .Range("B" & NR).Value = ws.Range("B1").Value .Range("C" & NR).Value = ws.Range("C1").Value NR = NR + 1 End If Next ws End With Application.ScreenUpdating = True End Sub