Excel: Créer résumé de la feuille de calcul à l'aide de certaines cellules d'autres feuilles de calcul

J'ai tout un tas d'onglets qui partagent une structure standard. Dans les cellules A1, B1, C1, de chaque onglet, j'ai, respectivement, un numéro, un Nom et un prénom. Il y a tout un tas d'autres informations sur chaque onglet qui n'est pas pertinent pour la question.

Je veux créer un onglet résumé, avec un 3-colonne de la table de la réalisation que le numéro d'identification, le nom et prénom de chacun des autres onglets. Encore une fois, ce sont toujours dans les cellules A1, B1, C1. Je veux aussi que le numéro d'identification dans le tableau sommaire de lien hypertexte de l'onglet approprié.

Est-il possible de semi-automatiser cela en utilisant des références de cellule? Comme la façon dont, dans un seul tableau, si vous entrez un nombre dans une cellule et faites glisser vers le bas, le nombre est incrémenté à chaque consécutives cellule de la colonne. Est-il possible d'obtenir un effet similaire, mais avec la référence de la cellule reste constante (toujours la cellule A1, A2, A3) et l'ONGLET de référence étant incrémenté? En bref, est-il possible de lier une ligne particulière d'un onglet séparé (de manière à prendre soin de la création d'hyperliens, d'ailleurs)? Ou dois-je entrer la référence manuellement pour chaque table?

Pas que les onglets ne sont pas étiquetés la Feuille 1, Feuille 2, etc. Ils seront étiquetés avec le nom de la personne dont les informations qu'ils détiennent.

OriginalL'auteur Swiftslide | 2012-03-18

excel vba

4

[EDIT: Added ASAP option]

ASAP utilities prend le dur travail de cette
- Télécharger ASAP utilities , http://www.asap-utilities.com/
- Exécuter l'utilitaire que par la capture d'écran 1 ci-dessous
- Choisi si vous voulez un lien direct de résumé, ou codé en dur rapport
  (capture d'écran 2)
ASAP paramètres

Sélectionnez votre A1:C1 travers les feuilles

Sortie

[Initial Post]

Vous pouvez le faire sans un continu VBA sous

Étape 1

Définir un nom de plage pour contenir tous les noms de feuille de calcul, (de ce grand exemple de David Hager)

Dans cet exemple j'ai utilisé
AllSheets

avec une référence de

=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))

Étape 2

L'utilisation d'une formule de l'INDICE de sortir de chaque feuille sur votre fiche de synthèse

=IF(ROW()<=COUNTA(AllSheets),INDEX(AllSheets,ROW()),"")& LEFT(RAND(),0)

En supposant que votre fiche de synthèse est à l'extrême gauche (c'est à dire de la feuille 1) puis
ROW() sera de 2 en A2, ce qui permettra de tirer la deuxième feuille de AllSheets

ROW() sera de 3 à A3, ce qui permettra de tirer la troisième feuille de AllSheets etc

La LEFT(RAND(),0) s'assure que le nom de la feuille de la liste des mises à jour dès que le nom de la feuille est changé (et VBA solution, il faudrait surveiller les noms de feuille de calcul pour les changements)

Étape 3

L'utilisation INDIRECTE de sortir A1, B1 et C1 pour chaque feuille
=IF($A2<>"",INDIRECT("'"&$A2&"'!"&B$1),"")

Copier ce B2 la formule vers la droite et vers le bas aussi loin que vous attendez potental les noms de feuilles

Noter que cette formule gère le ' qui se produisent pour la feuille de noms avec des espaces etc

n'a pas connu une telle addin existait 🙂
thx. Ce complément est à proximité à l'essentiel, il a une remarquable diffusion de la fonctionnalité.
+1 aussi pour le complément!

OriginalL'auteur brettdj
3

Vous pourriez probablement utiliser le INDIRECTS function.
Par exemple, pour obtenir la valeur de la cellule A1 de la feuille de John, vous pouvez appeler le:
```
=INDIRECT("John!A1")
```
Si dans votre cas, vous pourriez mettre "John" dans la cellule A1 de votre onglet résumé, "A1" dans la cellule B1 de l'onglet résumé et dans la cellule C1, mettre la formule suivante:
```
=INDIRECT("'"A1&"'!"&B1)
```
INDIRECT doit être utilisé de la restauration pour les noms de feuille de calcul qui sont comme des 'xxx aaaa"!
Oui - modifié - merci pour votre commentaire.

OriginalL'auteur assylias
1

Il n'y a pas de fonction intégrées dans excel qui va vous donner tous les noms de feuille de calcul dans le classeur. Pour cela, vous devez avoir à se tourner vers VBA, le script intégré dans Excel. Si vous êtes sur Office 2007 ou au-dessus, ce serait vous obliger à stocker votre classeur dans un format spécial. Aussi, pour exécuter la macro, l'utilisateur doit autoriser l'exécution de la macro. La macro suivante vous permettra d'imprimer tous les noms de feuille de calcul dans la première colonne. Un point important à noter est que ce code devrait être exécuté dans la fenêtre de code de la feuille sommaire.
```
Sub SheetNames() 
    For i = 1 To Sheets.Count 
        Cells(i, 1) = Sheets(i).Name 
    Next i 
End Sub 
```
Une fois que vous avez le nom de la feuille, vous pouvez ajouter un lien dynamique vers les cellules dans les feuilles à l'aide de la formule suivante:
```
=INDIRECT(ADDRESS(1, 1, 1, 1, A1))
=INDIRECT(ADDRESS(1, 2, 1, 1, A1))
=INDIRECT(ADDRESS(1, 3, 1, 1, A1))
```
INDIRECTS formule prend une adresse. L'ADRESSE de la formule fonctionne comme suit ADRESSE(les arguments no_lig, Column_num, Abs_num, A1, Sheet_text). L'aide d'Excel sera en mesure de répondre à toutes les questions.

Vous pouvez configurer la formule et de l'exécution de la macro que lorsqu'il y a beaucoup de nouvelles feuilles. Le classeur peut alors être stocké sans la macro. Il suffit de garder la macro dans un fichier texte quelque part dans le cas où vous avez à nouveau besoin.

OriginalL'auteur len

Voici une solution à l'aide de VBA UDF pour obtenir les noms de feuille de calcul. Il comprend un lien Hypertexte comme demandé.

L'appel à l'UDF est placé dans un hors de la voie de la colonne. J'ai utilisé H pour cet exemple

Place de ces fonctions dans les cellules A1, B1, C1, S1

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

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

H1: =SheetByIndex()

Mettre cela dans un module 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

Copier des formules A, B, C, H, vers le bas aussi loin que nécessaire

OriginalL'auteur chris neilsen

Mes légèrement différent de l'approche fondée sur les code publié ici (il y a un exemple de feuille de là, aussi, pour les tests):

Option Explicit

Sub CreateHyperlinkedSheetList()
'Author:    Jerry Beaucaire
'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

OriginalL'auteur Jerry Beaucaire

Vous devez vous connecter pour publier un commentaire.

Excel: Créer résumé de la feuille de calcul à l'aide de certaines cellules d'autres feuilles de calcul

`[EDIT: Added ASAP option]`

`[Initial Post]`